Dragon Drop
Dragon Drop - A Software Consultancy
Home      Our Products      Consultancy      Web Page Development      Services      Coding      Windows      External Resources
Links      Newsletter      News And Issues      Books etc.      About Us

Software Clinic - ASP

Can someone help me with an SQL error?

Problem

I get the following error:
Microsoft VBScript compilation error '800a0400'

set UpdateConn = Server.CreateObject("ADODB.Connection") 



MdbFilePath = Server.MapPath("NWIS.mdb") 

UpdateConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";" 



sqlstr = "update NWISUpdate set RequestStatus = 'Complete', Comments = '" &

  request.form("Comments") & "' where ID='" & request.form("id") & "'" 



UpdateConn.Execute(sqlstr) 

UpdateConn.Close 

set UpdateConn = nothing

Solution

This is one of the common problems when entering free text into a database from a form. If the user enters a text string which contains an apostrophe then the SQL statement will choke. Therefore, the answer is to handle the the string by replacing any single apostrophes with two.

  Dim sComment



  sComment = Replace(Request.form("Comments"),"'","''")

  sqlstr = "update NWISUpdate set RequestStatus = 'Complete', Comments = '" &

    sComment & "' where ID='" & request.form("id") & "'" 	

	

Updates

If there are any suggestions for updates then please drop me a mail at malcolm.smith@dragondrop.com.