Dragon Drop - A Visual Basic Software Consultancy

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.