SQL update not working

[JustAsking]

Following is a sql update statement I am using,:


UpdateQuery = "UPDATE NF_Results SET school_name = '" & school_name & "' WHERE username = '" & username & "'"


but this is the error I am receiving which points to this line.

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the character string '''.

/bq/insertrecord.asp, line 92

-----------
I cannot see what is wrong, probably just been looking at this too long and missing something easy, can anyone help?

[allida77]

Right after your code do this:


UpdateQuery = "UPDATE NF_Results SET school_name = '" & school_name & "' WHERE username = '" & username & "'"
Response.Write("UpdateQuery=" & UpdateQuery)
Response.End


From there you should see where your "Unclosed quotation mark before the character string" is.

[oracleguy]

Is there a single quote in the school name? or the username? If so that is what will cause that error. To correct this, refer to the sticky at the top of the ASP forum about single quotes, there is a function in there to help you.

[JustAsking]

Variable 'username' did not have a value when getting the error. Once that error was fixed the update worked without a hitch.

Thanks peeps..

[JustAsking]

When using the following code to populate a dropdown select list if the name of the sachool in that database is, Brisbane State High School, the value when in it is in the select list is Brisbane. This occurs for all fields in the database. I cannot work out where the values are being trimmed (cut-off)?


response.write("<option value=" & rs.Fields("school_name") & ">" & server.HTMLEncode(rs.Fields("school_name")))

[Roy Sinclair]

Originally posted by JustAsking
When using the following code to populate a dropdown select list if the name of the sachool in that database is, Brisbane State High School, the value when in it is in the select list is Brisbane. This occurs for all fields in the database. I cannot work out where the values are being trimmed (cut-off)?


response.write("<option value=" & rs.Fields("school_name") & ">" & server.HTMLEncode(rs.Fields("school_name")))


It's getting cut off at the first space because you've failed to use quotation marks. You can insert inline quote marks by simply "doubling" them up:


response.write("<option value=""" & rs.Fields("school_name") & """>" & server.HTMLEncode(rs.Fields("school_name"))) & "</option>"



Attribute properties should always be quoted and those quote marks should be " not '. That's not enforced by the browsers but it is in the current standard (xHTML 1.0). Likewise, there are no "optional" closing tags in the current standards so it's a good practice to not leave them out any more.