Update Help (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

mar0364 -> Update Help (3/20/2003 11:08:52)

This update querry keeps hangingup on me
This is the error:
Microsoft OLE DB Provider for ODBC Drivers error ' 80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ' ' American Risk Assurance' first_name = ' Tangela' last_name = ' Sapp' email = ' sappt@doi.state.fl.us' phone_number = ' 413-4424' fax_number = ' 922-0289' address = ' PO Box 110' address1 = ' s' address2 = ' s' city = ' Tallahassee' state = ' FL' zip_code = ' 32302-011' .

All fields are text except the two date fields. Any Ideas?


strSQL = " Update tracker Set " & _
" company_name = ' " & _
CStr(Request.Form(" txtcompany_name" )) & " ' " & _
" first_name = ' " & _
CStr(Request.Form(" txtfirst_name" )) & " ' " & _
" last_name = ' " & _
CStr(Request.Form(" txtlast_name" )) & " ' " & _
" email = ' " & _
CStr(Request.Form(" txtemail" )) & " ' " & _
" phone_number = ' " & _
CStr(Request.Form(" txtphone_number" )) & " ' " & _
" fax_number = ' " & _
CStr(Request.Form(" txtfax_number" )) & " ' " & _
" address = ' " & _
CStr(Request.Form(" txtaddress" )) & " ' " & _
" address1 = ' " & _
CStr(Request.Form(" txtaddress1" )) & " ' " & _
" address2 = ' " & _
CStr(Request.Form(" txtaddress2" )) & " ' " & _
" city = ' " & _
CStr(Request.Form(" txtcity" )) & " ' " & _
" state = ' " & _
CStr(Request.Form(" txtstate" )) & " ' " & _
" zip_code = ' " & _
CStr(Request.Form(" txtzip_code" )) & " ' " & _
" id_number = ' " & _
CStr(Request.Form(" txtid_number" )) & " ' " & _
" policy_number = ' " & _
CStr(Request.Form(" txtpolicy_number" )) & " ' " & _
" customer_type = ' " & _
CStr(Request.Form(" txtcustomer_type" )) & " ' " & _
" information_requested = ' " & _
CStr(Request.Form(" txtinformation_requested" )) & " ' " & _
" note = ' " & _
CStr(Request.Form(" txtnote" )) & " ' " & _
" date_sent = " & _
CStr(Request.Form(" txtdate_sent" )) & " , " & _
" isur = ' " & _
CStr(Request.Form(" txtisur" )) & " ' " & _
" assigned = ' " & _
CStr(Request.Form(" txtassigned" )) & " ' " & _
" resolution = ' " & _
CStr(Request.Form(" txtresolution" )) & " ' " & _
" completed = ' " & _
CStr(Request.Form(" txtcompleted" )) & " ' " & _
" completed_by = ' " & _
CStr(Request.Form(" txtcompleted_by" )) & " ' " & _
" completed_date = " & _
CStr(Request.Form(" txtcompleted_date" )) & " , " & _
" WHERE tracker = ' " & _
CStr(Request.Form(" txttracker" )) & " ' "

Thanks!
Rich



rdouglass -> RE: Update Help (3/20/2003 11:29:34)

Well if you' re using Access, your date field data need to be delimited by a #...

....
" completed_date = #" & _
CStr(Request.Form(" txtcompleted_date" )) & " #, " & _
....

Try that and see where you get...



Spooky -> RE: Update Help (3/20/2003 11:59:37)

Youll need a comma to seperate all of the fields :-)



mar0364 -> RE: Update Help (3/20/2003 12:03:08)

I tried that rdouglas no dice.

Spooky Do you mean like this:
example
strSQL = " Update tracker Set " & _,
" company_name = ' " & _
CStr(Request.Form(" txtcompany_name" )) & " ' " & _,
" first_name = ' " & _
CStr(Request.Form(" txtfirst_name" )) & " ' " & _,
" last_name = ' " & _
CStr(Request.Form(" txtlast_name" )) & " ' " & _,
" email = ' " & _
CStr(Request.Form(" txtemail" )) & " ' " & _,
" phone_number = ' " & _
CStr(Request.Form(" txtphone_number" )) & " ' " & _,


I' m having a slow day.

Thanks!



Spooky -> RE: Update Help (3/20/2003 12:10:37) 

strSQL = " Update tracker Set "  & _
	" company_name = ' "  & _
	CStr(Request.Form(" txtcompany_name" )) & " ' , "  & _
	" first_name = ' "  & _
	CStr(Request.Form(" txtfirst_name" )) & " ' , "  & _
	" last_name = ' "  & _
	CStr(Request.Form(" txtlast_name" )) & " ' , "  & _
	" email = ' "  & _
	CStr(Request.Form(" txtemail" )) & " ' , "  & _
	" phone_number = ' "  & _
	CStr(Request.Form(" txtphone_number" )) & " ' , "  & _
	" fax_number = ' "  & _
	CStr(Request.Form(" txtfax_number" )) & " ' , "  & _
	" address = ' "  & _
	CStr(Request.Form(" txtaddress" )) & " ' , "  & _
	" address1 = ' "  & _
	CStr(Request.Form(" txtaddress1" )) & " ' , "  & _
	" address2 = ' "  & _
	CStr(Request.Form(" txtaddress2" )) & " ' , "  & _
	" city = ' "  & _
	CStr(Request.Form(" txtcity" )) & " ' , "  & _
	" state = ' "  & _
	CStr(Request.Form(" txtstate" )) & " ' , "  & _
	" zip_code = ' "  & _
	CStr(Request.Form(" txtzip_code" )) & " ' , "  & _
	" id_number = ' "  & _
	CStr(Request.Form(" txtid_number" )) & " ' , "  & _
	" policy_number = ' "  & _
	CStr(Request.Form(" txtpolicy_number" )) & " ' , "  & _
	" customer_type = ' "  & _
	CStr(Request.Form(" txtcustomer_type" )) & " ' , "  & _
	" information_requested = ' "  & _
	CStr(Request.Form(" txtinformation_requested" )) & " ' , "  & _
	" note = ' "  & _
	CStr(Request.Form(" txtnote" )) & " ' , "  & _
	" date_sent = #"  & _
	CStr(Request.Form(" txtdate_sent" )) & " #, "  & _
	" isur = ' "  & _
	CStr(Request.Form(" txtisur" )) & " ' , "  & _
	" assigned = ' "  & _
	CStr(Request.Form(" txtassigned" )) & " ' , "  & _
	" resolution = ' "  & _
	CStr(Request.Form(" txtresolution" )) & " '  ,"  & _
	" completed = ' "  & _
	CStr(Request.Form(" txtcompleted" )) & " ' , "  & _
	" completed_by = ' "  & _
	CStr(Request.Form(" txtcompleted_by" )) & " ' , "  & _
	" completed_date = #"  & _
	CStr(Request.Form(" txtcompleted_date" )) & " # "  & _
	" WHERE tracker = ' "  & _
	CStr(Request.Form(" txttracker" ))





mar0364 -> RE: Update Help (3/20/2003 12:44:10)

I' m having a rotten time with this. Now I get the infamous:
Syntax error in UPDATE statement.





rdouglass -> RE: Update Help (3/20/2003 13:00:04)

Can you Response.Write(strSQL) to see what you end up with?



mar0364 -> RE: Update Help (3/20/2003 13:16:06)

This is the response.write I get. Does not make sense.
Its not even writing the whole thing.


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ' ' Agent' information_requested = ' Assessment Notice' note = ' TEST ' date_sent = #7/25/2002 8:19:12 AM#' .




Spooky -> RE: Update Help (3/20/2003 13:20:43)

Immediately after the posted code, use :

Response.Write(strSQL)
Response.end

I dont see any commas yet? 

" WHERE tracker = ' "  & _ 
cStr(Request.Form(" txttracker" )) &" ' "


Is tracker numeric?



mar0364 -> RE: Update Help (3/20/2003 13:28:20)

This is the update code:
' Build the update string
strSQL = " Update tracker Set " & _
" company_name = ' " & _
CStr(Request.Form(" txtcompany_name" )) & " ' , " & _
" first_name = ' " & _
CStr(Request.Form(" txtfirst_name" )) & " ' , " & _
" last_name = ' " & _
CStr(Request.Form(" txtlast_name" )) & " ' , " & _
" email = ' " & _
CStr(Request.Form(" txtemail" )) & " ' , " & _
" phone_number = ' " & _
CStr(Request.Form(" txtphone_number" )) & " ' , " & _
" fax_number = ' " & _
CStr(Request.Form(" txtfax_number" )) & " ' , " & _
" address = ' " & _
CStr(Request.Form(" txtaddress" )) & " ' , " & _
" address1 = ' " & _
CStr(Request.Form(" txtaddress1" )) & " ' , " & _
" address2 = ' " & _
CStr(Request.Form(" txtaddress2" )) & " ' , " & _
" city = ' " & _
CStr(Request.Form(" txtcity" )) & " ' , " & _
" state = ' " & _
CStr(Request.Form(" txtstate" )) & " ' , " & _
" zip_code = ' " & _
CStr(Request.Form(" txtzip_code" )) & " ' , " & _
" id_number = ' " & _
CStr(Request.Form(" txtid_number" )) & " ' , " & _
" policy_number = ' " & _
CStr(Request.Form(" txtpolicy_number" )) & " ' , " & _
" customer_type = ' " & _
CStr(Request.Form(" txtcustomer_type" )) & " ' , " & _
" information_requested = ' " & _
CStr(Request.Form(" txtinformation_requested" )) & " ' , " & _
" note = ' " & _
CStr(Request.Form(" txtnote" )) & " ' , " & _
" date_sent = #" & _
CStr(Request.Form(" txtdate_sent" )) & " #, " & _
" isur = ' " & _
CStr(Request.Form(" txtisur" )) & " ' , " & _
" assigned = ' " & _
CStr(Request.Form(" txtassigned" )) & " ' , " & _
" resolution = ' " & _
CStr(Request.Form(" txtresolution" )) & " ' , " & _
" completed = ' " & _
CStr(Request.Form(" txtcompleted" )) & " ' , " & _
" completed_by = ' " & _
CStr(Request.Form(" txtcompleted_by" )) & " ' , " & _
" completed_date = #" & _
CStr(Request.Form(" txtcompleted_date" )) & " #, " & _
" WHERE tracker = ' " & _
CStr(Request.Form(" txttracker" )) &" ' ,"


This is the response.write I get:

Microsoft OLE DB Provider for ODBC Drivers error ' 80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

/Receiver/_editmail01.asp, line 532



Spooky -> RE: Update Help (3/20/2003 13:31:35) 

strSQL = " Update tracker Set "  & _
	" company_name = ' "  & _
	CStr(Request.Form(" txtcompany_name" )) & " ' , "  & _
	" first_name = ' "  & _
	CStr(Request.Form(" txtfirst_name" )) & " ' , "  & _
	" last_name = ' "  & _
	CStr(Request.Form(" txtlast_name" )) & " ' , "  & _
	" email = ' "  & _
	CStr(Request.Form(" txtemail" )) & " ' , "  & _
	" phone_number = ' "  & _
	CStr(Request.Form(" txtphone_number" )) & " ' , "  & _
	" fax_number = ' "  & _
	CStr(Request.Form(" txtfax_number" )) & " ' , "  & _
	" address = ' "  & _
	CStr(Request.Form(" txtaddress" )) & " ' , "  & _
	" address1 = ' "  & _
	CStr(Request.Form(" txtaddress1" )) & " ' , "  & _
	" address2 = ' "  & _
	CStr(Request.Form(" txtaddress2" )) & " ' , "  & _
	" city = ' "  & _
	CStr(Request.Form(" txtcity" )) & " ' , "  & _
	" state = ' "  & _
	CStr(Request.Form(" txtstate" )) & " ' , "  & _
	" zip_code = ' "  & _
	CStr(Request.Form(" txtzip_code" )) & " ' , "  & _
	" id_number = ' "  & _
	CStr(Request.Form(" txtid_number" )) & " ' , "  & _
	" policy_number = ' "  & _
	CStr(Request.Form(" txtpolicy_number" )) & " ' , "  & _
	" customer_type = ' "  & _
	CStr(Request.Form(" txtcustomer_type" )) & " ' , "  & _
	" information_requested = ' "  & _
	CStr(Request.Form(" txtinformation_requested" )) & " ' , "  & _
	" note = ' "  & _
	CStr(Request.Form(" txtnote" )) & " ' , "  & _
	" date_sent = #"  & _
	CStr(Request.Form(" txtdate_sent" )) & " #, "  & _
	" isur = ' "  & _
	CStr(Request.Form(" txtisur" )) & " ' , "  & _
	" assigned = ' "  & _
	CStr(Request.Form(" txtassigned" )) & " ' , "  & _
	" resolution = ' "  & _
	CStr(Request.Form(" txtresolution" )) & " ' , "  & _
	" completed = ' "  & _
	CStr(Request.Form(" txtcompleted" )) & " ' , "  & _
	" completed_by = ' "  & _
	CStr(Request.Form(" txtcompleted_by" )) & " ' , "  & _
	" completed_date = #"  & _ 
	CStr(Request.Form(" txtcompleted_date" )) & " # "  & _ 
	" WHERE tracker = ' "  & _
	CStr(Request.Form(" txttracker" )) &" ' " 

Response.write strSQL 
response.end



mar0364 -> RE: Update Help (3/20/2003 13:35:32)

trying to figure this out I went down the code a line at a time adding the coma. Here is what I got I could add the coma up to (see red line) and get a reponse.write of something link:

Microsoft OLE DB Provider for ODBC Drivers error ' 80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ' ' Assessment Notice' note = ' TEST ' date_sent = #7/25/2002 8:19:12 AM# isur = ' d' assigned = ' Estate Management ' resolution = ' Prior to its liquidation in 1999, the Florida Workers Compensation Fund (FWCF) filed suit against Safety National Casualty Corporat' .

/Receiver/_editmail01.asp, line 536


What is odd there is it still does not write the top portion of the statement.


strSQL = " Update tracker Set " & _
" company_name = ' " & _
CStr(Request.Form(" txtcompany_name" )) & " ' , " & _
" first_name = ' " & _
CStr(Request.Form(" txtfirst_name" )) & " ' , " & _
" last_name = ' " & _
CStr(Request.Form(" txtlast_name" )) & " ' , " & _
" email = ' " & _
CStr(Request.Form(" txtemail" )) & " ' , " & _
" phone_number = ' " & _
CStr(Request.Form(" txtphone_number" )) & " ' , " & _
" fax_number = ' " & _
CStr(Request.Form(" txtfax_number" )) & " ' , " & _
" address = ' " & _
CStr(Request.Form(" txtaddress" )) & " ' , " & _
" address1 = ' " & _
CStr(Request.Form(" txtaddress1" )) & " ' , " & _
" address2 = ' " & _
CStr(Request.Form(" txtaddress2" )) & " ' , " & _
" city = ' " & _
CStr(Request.Form(" txtcity" )) & " ' , " & _
" state = ' " & _
CStr(Request.Form(" txtstate" )) & " ' , " & _
" zip_code = ' " & _
CStr(Request.Form(" txtzip_code" )) & " ' , " & _
" id_number = ' " & _
CStr(Request.Form(" txtid_number" )) & " ' , " & _
" policy_number = ' " & _
CStr(Request.Form(" txtpolicy_number" )) & " ' , " & _
" customer_type = ' " & _
CStr(Request.Form(" txtcustomer_type" )) & " ' , " & _ " information_requested = ' " & _
CStr(Request.Form(" txtinformation_requested" )) & " ' " & _
" note = ' " & _
CStr(Request.Form(" txtnote" )) & " ' " & _
" date_sent = #" & _
CStr(Request.Form(" txtdate_sent" )) & " # " & _
" isur = ' " & _
CStr(Request.Form(" txtisur" )) & " ' " & _
" assigned = ' " & _
CStr(Request.Form(" txtassigned" )) & " ' " & _
" resolution = ' " & _
CStr(Request.Form(" txtresolution" )) & " ' " & _
" completed = ' " & _
CStr(Request.Form(" txtcompleted" )) & " ' " & _
" completed_by = ' " & _
CStr(Request.Form(" txtcompleted_by" )) & " ' " & _
" completed_date = #" & _
CStr(Request.Form(" txtcompleted_date" )) & " #, " & _
" WHERE tracker = ' " & _
CStr(Request.Form(" txttracker" )) &" ' "



mar0364 -> RE: Update Help (3/20/2003 13:48:00)

This is the response.write I get based on what spooky sent to me.

Update tracker Set company_name = ' American Risk Assurance' , first_name = ' Tangela' , last_name = ' Sapp' , email = ' sappt@doi.state.fl.us' , phone_number = ' 413-4424' , fax_number = ' 922-0289' , address = ' PO Box 110' , address1 = ' a' , address2 = ' a' , city = ' Tallahassee' , state = ' FL' , zip_code = ' 32302-0110' , id_number = ' 4292840238' , policy_number = ' DLP02828710' , customer_type = ' Agent' , information_requested = ' Assessment Notice' , note = ' TEST ' , date_sent = #7/25/2002 8:19:12 AM#, isur = ' a' , assigned = ' Estate Management ' , resolution = ' Prior to its liquidation in 1999, the Florida Workers Compensation Fund (FWCF) filed suit against Safety National Casualty Corporation, a reinsurer, alleging breach of contract on the part of the reinsurer. The suit involved two different reinsurance treaties – one for the year 1987 and the other for the years 1988-91. The Receiver continued the litigation after liquidation. The law firm of Volpe, Bajalia, et.al., has represented the Receiver in this matter. Another consultant, Ray Neff, researched Special Disability Trust Fund (SDTF) issues for the Receiver.' , completed = ' Closed' , completed_by = ' Rich Mariner' , completed_date = #10/16/2002# WHERE tracker = ' 16'



rdouglass -> RE: Update Help (3/20/2003 14:13:45)

The only thing that jumps out at me is the possible numerical values in:

id_number = ' 4292840238'

and

tracker = ' 16'

If ' tracker' is your primary key, then it definitely should be numeric:

tracker = 16

...so if I' m correct, you' ll need to remove the apostrophes from the respective areas when you build strSQL.

Does that help at all?



mar0364 -> RE: Update Help (3/20/2003 14:15:38)

Thanks I caught that one also. Now the strSQL seems correct. I just sent an email to my server admin to make sure the data is not read only or something insane like that.



mar0364 -> RE: Update Help (3/20/2003 14:20:21)

here it is now:
Update tracker Set company_name = ' American Risk Assurance' , first_name = ' Tangela' , last_name = ' Sapp' , email = ' sappt@doi.state.fl.us' , phone_number = ' 413-4424' , fax_number = ' 922-0289' , address = ' PO Box 110' , address1 = ' a' , address2 = ' a' , city = ' Tallahassee' , state = ' FL' , zip_code = ' 32302-0110' , id_number = ' 4292840238' , policy_number = ' DLP02828710' , customer_type = ' Agent' , information_requested = ' Assessment Notice' , note = ' TEST ' , date_sent = #7/25/2002 8:19:12 AM#, isur = ' a' , assigned = ' Estate Management ' , resolution = ' Prior to its liquidation in 1999, the Florida Workers Compensation Fund (FWCF) filed suit against Safety National Casualty Corporation, a reinsurer, alleging breach of contract on the part of the reinsurer. The suit involved two different reinsurance treaties – one for the year 1987 and the other for the years 1988-91. The Receiver continued the litigation after liquidation. The law firm of Volpe, Bajalia, et.al., has represented the Receiver in this matter. Another consultant, Ray Neff, researched Special Disability Trust Fund (SDTF) issues for the Receiver.' , completed = ' Closed' , completed_by = ' Rich Mariner' , completed_date = #10/16/2002# WHERE tracker = 16



Spooky -> RE: Update Help (3/20/2003 14:38:56)

Now that the SQL is correct, is there an error?
You have checked the other numeric fields and removed the quotes from them?



mar0364 -> RE: Update Help (3/20/2003 15:21:00)

Yes I have. But I still get the :
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

I' m about to go nuts.



mar0364 -> RE: Update Help (3/20/2003 15:24:49)

This is the data design view


[image]http://www.frontpagewebmaster.com/upfiles/5085/Mk27332.gif[/image]



mar0364 -> RE: Update Help (3/20/2003 15:53:09)

Could it be that I should refer to the memo fields as something other then just text.?

Could that be it?



rdouglass -> RE: Update Help (3/20/2003 15:55:19)

quote:

strSQL = " Update tracker Set " & _


Have you tried this at all??

strSQL = " Update DISTINCTROW tracker Set " & _
....

I always use it...



mar0364 -> RE: Update Help (3/20/2003 16:00:21)

No didn' t help but dame everything looks correct.
Update distinctrow tracker Set company_name = ' American Risk Assurance' , first_name = ' Tangela' , last_name = ' Sapp' , email = ' sappt@doi.state.fl.us' , phone_number = ' 413-4424' , fax_number = ' 922-0289' , address = ' PO Box 110' , address1 = ' z' , address2 = ' z' , city = ' Tallahassee' , state = ' FL' , zip_code = ' 32302-0110' , id_number = ' 4292840238' , policy_number = ' DLP02828710' , customer_type = ' Agent' , information_requested = ' Assessment Notice' , note = ' TEST ' , date_sent = #7/25/2002 8:19:12 AM# isur = ' z' , assigned = ' Estate Management ' , resolution = ' Prior to its liquidation in 1999, the Florida Workers Compensation Fund (FWCF) filed suit against Safety National Casualty Corporation, a reinsurer, alleging breach of contract on the part of the reinsurer. The suit involved two different reinsurance treaties – one for the year 1987 and the other for the years 1988-91. The Receiver continued the litigation after liquidation. The law firm of Volpe, Bajalia, et.al., has represented the Receiver in this matter. Another consultant, Ray Neff, researched Special Disability Trust Fund (SDTF) issues for the Receiver.' , completed = ' Closed' , completed_by = ' Rich Mariner' , completed_date = #10/16/2002# WHERE tracker = 16



Spooky -> RE: Update Help (3/20/2003 21:40:01)

Move the 2 memo fields to be the last 2 fields in the update statement.



mar0364 -> RE: Update Help (3/21/2003 8:13:48)

I tried it spooky. No dice

with sql responce write off I get:
Syntax error in UPDATE statement.

With sql responce write on I get:
Update distinctrow tracker Set company_name = ' American Risk Assurance' , first_name = ' Tangela' , last_name = ' Sapp' , email = ' sappt@doi.state.fl.us' , phone_number = ' 413-4424' , fax_number = ' 922-0289' , address = ' PO Box 110' , address1 = ' 1' , address2 = ' s' , city = ' Tallahassee' , state = ' FL' , zip_code = ' 32302-0110' , id_number = ' 4292840238' , policy_number = ' DLP02828710' , customer_type = ' Agent' , information_requested = ' Assessment Notice' , date_sent = #7/25/2002 8:19:12 AM#, isur = ' s' , assigned = ' Estate Management ' , completed = ' Closed' , completed_by = ' Rich Mariner' , completed_date = #10/16/2002#, note = ' TEST ' , resolution = ' Prior to its liquidation in 1999, the Florida Workers Compensation Fund (FWCF) filed suit against Safety National Casualty Corporation, a reinsurer, alleging breach of contract on the part of the reinsurer. The suit involved two different reinsurance treaties – one for the year 1987 and the other for the years 1988-91. The Receiver continued the litigation after liquidation. The law firm of Volpe, Bajalia, et.al., has represented the Receiver in this matter. Another consultant, Ray Neff, researched Special Disability Trust Fund (SDTF) issues for the Receiver.' , WHERE tracker = 16,



Spooky -> RE: Update Help (3/21/2003 13:14:21)

There should be no commas here :
, WHERE tracker = 16,



mar0364 -> RE: Update Help (3/21/2003 13:50:58)

Once again I thank this great group of folks here. It works great now.

Now I need to go watch my daughters favorite show:


[image]http://www.frontpagewebmaster.com/upfiles/5085/Om33218.jpg[/image]



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.15625