Hi Everyone, I know that I have read a post on this but I can't find it anywhere..
Can someone explain or direct me to a post?

How do I get a statement in a form being sent to MS Access db when an apostrophe, etc. is involved?

EXAMPLE
input field: Table 4' x 5'

How do I get this to work..??

Thanks for any help.

function SQLComply (Term)
 
     if Term <> "" then
          Term = Replace (Term, chr (39), chr (39) & chr (39))
     end if
     SQLComply = Term
 
end function

You use it like this:

SQL_Query = "Update Your_Table set Your_Field = '" & SQLComply (Your_Field) & "'...

SQLComply will replace single apostrophes with two apostrophes (not to be confused with double quotes) and as a result, your query will work and the data will be entered as one apostrophe.

I'm not sure on this, but I think it also helps with SQL Injection (someone told me that once a long time ago, after I wrote it, although it wasn't the intention when I did).

Thanks ADAM,

Is this what I should be doing..??

<%

function SQLComply (Term)
 
     if Term <> "" then
          Term = Replace (Term, chr (39), chr (39) & chr (39))
     end if
     SQLComply = Term
 
end function
%>

<%


'SQL to add Item to catalog 
strQ2 = "INSERT INTO usedequip001 (" & SQLComply (Description) &  ", StockNo, Price, Notes, Thumb, Photo) VALUES ('"
strQ2 = strQ2 & strDescription
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strStockNo
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strPrice
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strNotes
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strThumb
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strPhoto
strQ2 = strQ2 & "')"
objConn2.Execute strQ2
%>

Almost. You need to put single quotes around the item itself in your SQL query.

add Item to catalog
strQ2 = "INSERT INTO usedequip001 ('" & SQLComply (Description) & "',...

Still having a problem with this..

Here is my full code:

'assign form values to variables
strDescription = Trim(Request.Form("Description"))
strStockNo = Trim(Request.Form("StockNo"))
strPrice = Trim(Request.Form("Price"))
strNotes = Trim(Request.Form("Notes"))
strThumb = Trim(Request.Form("Thumb"))
strPhoto = Trim(Request.Form("Photo"))

'this script adds item to catlog
Dim objConn2
Dim strQ2
Dim strConn2
Set objConn2 = Server.CreateObject("ADODB.Connection")
strConn2 = "Data Source=catalog1;"
objConn2.Open strConn2


function SQLComply (Term)
 
     if Term <> "" then
          Term = Replace (Term, chr (39), chr (39) & chr (39))
     end if
     SQLComply = Term
 
end function
%>

<%
'SQL to add Item to catalog 
strQ2 = "INSERT INTO usedequip001 (Description, StockNo, Price, Notes, Thumb, Photo) VALUES ('"
strQ2 = strQ2 & strDescription
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strStockNo
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strPrice
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strNotes
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strThumb
strQ2 = strQ2 & "', '"
strQ2 = strQ2 & strPhoto
strQ2 = strQ2 & "')"
objConn2.Execute strQ2
%>

and here is the error message I am getting:


Technical Information (for support personnel)

• Error Type:
  Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
  [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''Another Item 4' long', '9998', '20', 'This is a second test', '0', '0')'.
  /admin/usedequip/additem2.asp, line 85
• Browser Type:
  Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.12) Gecko/20050915 Firefox/1.0.7
• Page:
  POST 100 bytes to /admin/usedequip/additem2.asp
• POST Data:
  Description=Another+Item+4%27+long&StockNo=9998&Pr ice=20&Notes=This+is+a+second+test&Photo=0&Thumb=0

Can anyone see what I am doing / not doing to make this crash?

Thanks...

You're not calling the function.

I'm going to take a small portion of your code and bold the important part (I guess I'm not very good at explaining this function):

strQ2 = "INSERT INTO usedequip001 (Description, StockNo, Price, Notes, Thumb, Photo) VALUES ('"
strQ2 = strQ2 & SQLComply (strDescription)
strQ2 = strQ2 & "', '"

Repeat as necessary for all text-based fields (not anything else, though).

You have to use the SQLComply function for each text-based item you wish to insert/modify in your table as you concatenate your SQL query itself, or it won't work.

I see. I wasn't putting it in the right spot. I'll try this. Thanks for being patient with me.

No problem. For some reason, that function is incredibly difficult to understand for a lot of people. I'm not totally sure why that is, but then again, I wrote it so I'm not the best source of unbiased information.

Anyway, let me know if you're still stuck, bro.

Adam, Great script. It works like a charm. I don't completely understand the
mechanics of it, but I see on a basic level what it is doing. I have only been using
asp for a couple of months now and don't quite have a grasp on funtions. Thanks
for your eforts to give me a hand and share your knowledge..!

I'm sure I'll talk with you later.

No probs.

Remember, if you're not sure how something works, Response.Write at various stages is your best friend for debugging. That's not what it's meant for, but **** it's a great way to figure things out. It has saved my *** I dunno how many times.

And here's to ya!