Dragon Drop - A Visual Basic Software Consultancy

Software Clinic - ASP

Can someone help me with an SQL error?


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") & "'" 

set UpdateConn = nothing


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") & "'" 	


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