SQL Syntax problem

[kosmokramer]

Hey. I know I am probably missing something stupid, but I can't get this to work for some reason. When I run the query with the hard coded values in an online database query analyzer, the query works, but it will not work on my asp page. With all that said, here is the query:

strSQL6="Update PMD_Members Set membName='" & Request("membName")& "', address='" & Request("address") & "', phoneNumber='" & Request("phoneNumber") & "', position='" & Request("position") & "', email='" & Request("email") & "' where membNumb=" & Request("membNumb")

As it is right now, I do not have any line breaks in this string. I tried breaking it up earlier, but it kept giving me errors. Anyways, when I try to run this query, it gives me a syntax error.

P.S. All the data types are text except for membNumb which is a numeric value

 

[BG12424]

There are a couple of things that could be wrong here.

1. First, check to make sure that you don't have any early string terminations. If you have a single quote in one of your string fields then you need to escape it using something like:

replace(stringField,"'","''&quot

2. It's important to understand how you are executing this SQL. You are best off using something like:

dbConn.Execute strSQL6

_______________________________
regards,
Brian

AOL IM: FreelanceGaines

 

[JamesLean]

The best way to debug problems like this is to output the resultant SQL string to the screen once you've built it:

strSQL6 = "....."

Response.Write strSQL6

Have a look at what you get here - it may be, as Brian said, you have some dodgy characters in your variables or even that some of them aren't being populated at all. --James

 

[kosmokramer]

Hey. I tried the Response.Write method a while back, but when I put this string into the query analyzer, it worked. I did however have to change the spacing within the string because when I put it into the input box for the analyzer, it automatically put in some line breaks because the string was too long. For example, it did stuff like membName='John (line break) Doe',. I just changed it so that the strings were on one line ( ex. membName='John Doe'. When I ran it without making the corrections to the spacing it gave me the same syntax error.

Just for reference here is the whole thing:

Dim strSQL6, strConn6, objConn6
		strConn6 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
		"Data Source=" & Server.MapPath("\xxxxxxxxx\db\someDatabase.mdb") & ";" & _ 
		"Persist Security Info=False"
		Set objConn6 = Server.CreateObject("ADODB.Connection") 
		objConn6.ConnectionString=strConn6
		objConn6.Open
		Set objRS6=Server.CreateObject("ADODB.Recordset")
		strSQL6="Update PMD_Members Set membName='" & Request("membName")& "', address='" & Request("address") & "', phoneNumber='" & Request("phoneNumber") & "', position='" & Request("position") & "', email='" & Request("email") & "' where membNumb=" & Request("membNumb")
		'Response.Write strSQL6
		objConn6.Execute strSQL6
		objConn6.Close
		Set objConn6 = Nothing
		Response.Redirect "Exec Page.asp?membName=" & Request("membName") & "&password_Field=" & Request("password_Field")

P.S. bgaines, how do you use the thing you were talking about (replace(stringField,"'","''&quot)