Problem updating Sql Server database from asp page

Hi,
I am building an asp application with sql server as backend.
I am building the sql string in the main processing page. With a
response.write
statement I get the sql statement. I am testing in Query Analyzer whether
this SQL statement is working. Sql Server does not update the table and sends
me an error message. I would appreciate some help on this. Thanks. Regards.

CODE:

strSQL1 = "UPDATE tblApplication SET tblApplication.ORI= '" & l_p_ORI
& "' " & _
", tblApplication.Agency= '" & l_p_Agency & "' " & _
", tblApplication.MaleSworn= '" & l_p_MaleSworn & "' " & _
", tblApplication.FemaleSworn= '" & l_p_FemaleSworn & "' " & _
", tblApplication.MaleCivilian= '" & l_p_MaleCivilian & "' " & _
", tblApplication.FemaleCivilian= '" & l_p_FemaleCivilian & "' " & _
", tblApplication.Detention= '" & l_p_Detention & "' " & _
", tblApplication.SchoolResource= '" & l_p_SchoolResource & "' " & _
", tblApplication.HaveReserve= '" & l_p_HaveReserve & "' " & _
", tblApplication.Reserve= '" & l_p_Reserve & "' " & _
", tblApplication.HavePTSworn= '" & l_p_HavePTSworn & "' " & _
", tblApplication.PTSworn= '" & l_p_PTSworn& "' " & _
", tblApplication.FTCourtSecurityOfficer= '" &
l_p_FTCourtSecurityOfficer & "' " & _
", tblApplication.OfficerStartingSalary= '" &
l_p_OfficerStartingSalary & "' " & _
", tblApplication.HasDispatchCenter= '" & l_p_HasDispatchCenter &
"' " & _
", tblApplication.WhatKindDispatchCenter= '" &
l_p_WhatKindOfDispatchCenter & "' " & _
" where " & _
" tblApplication.ApplicantIntID= " & ApplicantIntID & ";"


Response.Write "
"
Response.Write strSQL1
Response.Write "
"

SQL STATEMENT GENERATED:


UPDATE tblApplication SET tblApplication.ORI= '00000000007' ,
tblApplication.Agency= 'TestingAgency' , tblApplication.MaleSworn= '21' ,
tblApplication.FemaleSworn= '18' , tblApplication.MaleCivilian= '55' ,
tblApplication.FemaleCivilian= '29' , tblApplication.Detention= '79' ,
tblApplication.SchoolResource= '24' , tblApplication.HaveReserve= 'Y' ,
tblApplication.Reserve= '14' , tblApplication.HavePTSworn= 'Y' ,
tblApplication.PTSworn= '21' , tblApplication.FTCourtSecurityOfficer= '23' ,
tblApplication.OfficerStartingSalary= '$2,500.55' ,
tblApplication.HasDispatchCenter= 'Y' ,
tblApplication.WhatKindDispatchCenter= 'This is a test disp' where
tblApplication.ApplicantIntID= 70;


ERROR REPORT FROM SQL SERVER:

Disallowed implicit conversion from data type varchar to data type money,
table 'manpowerweb.dbo.tblApplication', column 'OfficerStartingSalary'. Use
the CONVERT function to run this query.

Re: Problem updating Sql Server database from asp page

You are including formatting in your Money datatype.
Instead of '$2,500.55' try 2500.55

"Jack" wrote in message
news:1EDE57EE-DD0B-4B1F-B933-AC483FDDBA45@microsoft.com...
> Hi,
> I am building an asp application with sql server as backend.
> I am building the sql string in the main processing page. With a
> response.write
> statement I get the sql statement. I am testing in Query Analyzer whether
> this SQL statement is working. Sql Server does not update the table and
> sends
> me an error message. I would appreciate some help on this. Thanks.
> Regards.
>
> CODE:
>
> strSQL1 = "UPDATE tblApplication SET tblApplication.ORI= '" & l_p_ORI
> & "' " & _
> ", tblApplication.Agency= '" & l_p_Agency & "' " & _
> ", tblApplication.MaleSworn= '" & l_p_MaleSworn & "' " & _
> ", tblApplication.FemaleSworn= '" & l_p_FemaleSworn & "' " & _
> ", tblApplication.MaleCivilian= '" & l_p_MaleCivilian & "' " & _
> ", tblApplication.FemaleCivilian= '" & l_p_FemaleCivilian & "' " &
> _
> ", tblApplication.Detention= '" & l_p_Detention & "' " & _
> ", tblApplication.SchoolResource= '" & l_p_SchoolResource & "' " & _
> ", tblApplication.HaveReserve= '" & l_p_HaveReserve & "' " & _
> ", tblApplication.Reserve= '" & l_p_Reserve & "' " & _
> ", tblApplication.HavePTSworn= '" & l_p_HavePTSworn & "' " & _
> ", tblApplication.PTSworn= '" & l_p_PTSworn& "' " & _
> ", tblApplication.FTCourtSecurityOfficer= '" &
> l_p_FTCourtSecurityOfficer & "' " & _
> ", tblApplication.OfficerStartingSalary= '" &
> l_p_OfficerStartingSalary & "' " & _
> ", tblApplication.HasDispatchCenter= '" & l_p_HasDispatchCenter &
> "' " & _
> ", tblApplication.WhatKindDispatchCenter= '" &
> l_p_WhatKindOfDispatchCenter & "' " & _
> " where " & _
> " tblApplication.ApplicantIntID= " & ApplicantIntID & ";"
>
>
> Response.Write "
"
> Response.Write strSQL1
> Response.Write "
"
>
> SQL STATEMENT GENERATED:
>
>
> UPDATE tblApplication SET tblApplication.ORI= '00000000007' ,
> tblApplication.Agency= 'TestingAgency' , tblApplication.MaleSworn= '21' ,
> tblApplication.FemaleSworn= '18' , tblApplication.MaleCivilian= '55' ,
> tblApplication.FemaleCivilian= '29' , tblApplication.Detention= '79' ,
> tblApplication.SchoolResource= '24' , tblApplication.HaveReserve= 'Y' ,
> tblApplication.Reserve= '14' , tblApplication.HavePTSworn= 'Y' ,
> tblApplication.PTSworn= '21' , tblApplication.FTCourtSecurityOfficer= '23'
> ,
> tblApplication.OfficerStartingSalary= '$2,500.55' ,
> tblApplication.HasDispatchCenter= 'Y' ,
> tblApplication.WhatKindDispatchCenter= 'This is a test disp' where
> tblApplication.ApplicantIntID= 70;
>
>
> ERROR REPORT FROM SQL SERVER:
>
> Disallowed implicit conversion from data type varchar to data type money,
> table 'manpowerweb.dbo.tblApplication', column 'OfficerStartingSalary'.
> Use
> the CONVERT function to run this query.

Re: Problem updating Sql Server database from asp page

"Jack" wrote in message
news:1EDE57EE-DD0B-4B1F-B933-AC483FDDBA45@microsoft.com...
> Hi,
> I am building an asp application with sql server as backend.
> I am building the sql string in the main processing page. With a
> response.write
> statement I get the sql statement. I am testing in Query Analyzer whether
> this SQL statement is working. Sql Server does not update the table and
> sends
> me an error message. I would appreciate some help on this. Thanks.
> Regards.
>
> CODE:
>
> strSQL1 = "UPDATE tblApplication SET tblApplication.ORI= '" & l_p_ORI
> & "' " & _
> ", tblApplication.Agency= '" & l_p_Agency & "' " & _
> ", tblApplication.MaleSworn= '" & l_p_MaleSworn & "' " & _
> ", tblApplication.FemaleSworn= '" & l_p_FemaleSworn & "' " & _
> ", tblApplication.MaleCivilian= '" & l_p_MaleCivilian & "' " & _
> ", tblApplication.FemaleCivilian= '" & l_p_FemaleCivilian & "' " &
> _
> ", tblApplication.Detention= '" & l_p_Detention & "' " & _
> ", tblApplication.SchoolResource= '" & l_p_SchoolResource & "' " & _
> ", tblApplication.HaveReserve= '" & l_p_HaveReserve & "' " & _
> ", tblApplication.Reserve= '" & l_p_Reserve & "' " & _
> ", tblApplication.HavePTSworn= '" & l_p_HavePTSworn & "' " & _
> ", tblApplication.PTSworn= '" & l_p_PTSworn& "' " & _
> ", tblApplication.FTCourtSecurityOfficer= '" &
> l_p_FTCourtSecurityOfficer & "' " & _
> ", tblApplication.OfficerStartingSalary= '" &
> l_p_OfficerStartingSalary & "' " & _
> ", tblApplication.HasDispatchCenter= '" & l_p_HasDispatchCenter &
> "' " & _
> ", tblApplication.WhatKindDispatchCenter= '" &
> l_p_WhatKindOfDispatchCenter & "' " & _
> " where " & _
> " tblApplication.ApplicantIntID= " & ApplicantIntID & ";"
>
>
> Response.Write "
"
> Response.Write strSQL1
> Response.Write "
"
>
> SQL STATEMENT GENERATED:
>
>
> UPDATE tblApplication SET tblApplication.ORI= '00000000007' ,
> tblApplication.Agency= 'TestingAgency' , tblApplication.MaleSworn= '21' ,
> tblApplication.FemaleSworn= '18' , tblApplication.MaleCivilian= '55' ,
> tblApplication.FemaleCivilian= '29' , tblApplication.Detention= '79' ,
> tblApplication.SchoolResource= '24' , tblApplication.HaveReserve= 'Y' ,
> tblApplication.Reserve= '14' , tblApplication.HavePTSworn= 'Y' ,
> tblApplication.PTSworn= '21' , tblApplication.FTCourtSecurityOfficer= '23'
> ,
> tblApplication.OfficerStartingSalary= '$2,500.55' ,
> tblApplication.HasDispatchCenter= 'Y' ,
> tblApplication.WhatKindDispatchCenter= 'This is a test disp' where
> tblApplication.ApplicantIntID= 70;
>
>
> ERROR REPORT FROM SQL SERVER:
>
> Disallowed implicit conversion from data type varchar to data type money,
> table 'manpowerweb.dbo.tblApplication', column 'OfficerStartingSalary'.
> Use
> the CONVERT function to run this query.


The error message says it all really. 'OfficerStartingSalary' is defined as
a numeric data type, yet you are supplying a string to it.

Change the appropriate part of your sql statement from

tblApplication.OfficerStartingSalary= '$2,500.55'

to:

tblApplication.OfficerStartingSalary= 2500.55

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software priced to suit all
businesses
http://www.room-booking-software.com - Schedule rooms & equipment bookings
for your meeting/class over the web.
http://www.lbetoolbox.com - Remove Duplicates from MS Outlook
http://www.outlook-find-replace.com - Find & Replace in Emails, Contacts,
Appointments, Tasks and Notes