Excel Vba - Using an apostrophe in an sql string
22,113
You can correct this either by using parameters (recommended) or by using Replace.
& Replace(txtDescription,"'","''") &
Parameters
Dim cmd As New ADODB.command
cn.Open ServerConnect
cmd.ActiveConnection = cn
stSQL = "INSERT INTO Products (ProductName, " _
& "ProductDescription, ProductUnit, SupplierID) " _
& "Values (param1,param2,param3,param4)"
cmd.CommandText = stSQL
cmd.CommandType = adCmdText
With cmd
.Parameters.Append .CreateParameter( _
"param1", adInteger, adParamInput, , cboxItemNum)
.Parameters.Append .CreateParameter( _
"param2", adVarChar, adParamInput, 50, txtDescription )
.Parameters.Append .CreateParameter( _
"param3", adInteger, adParamInput, , txtUnit )
.Parameters.Append .CreateParameter( _
"param4", adInteger, adParamInput, , linkPID )
End with
cmd.Execute recs
Note that while I have named these parameters param1 to param4, that is for my convenience, all that matters is the order, which must match the order in which the parameters are to be used.
Related videos on Youtube
Author by
Reznor
Updated on July 05, 2022Comments
-
Reznor almost 2 years
I'm using excel to input data into an Access database and some of my data strings contain an apostrophe for measurements.
This is my SQL input string
stSQL = "INSERT INTO Products (ProductName, ProductDescription, ProductUnit, SupplierID) " & _ "Values ('" & cboxItemNum & "', '" & txtDescription & "', '" & txtUnit & "', " & linkPID & ")" cn.Execute (stSQL)
My string is as follows:
Aliplast 4E White. 30" X 80' X 1/4" Soft.
In this string the ' after the 80 is causing the error and I'm not sure how to get around this. I can't just tell the user not to enter an apostrophe. How can I get around this?
Thanks
-
Eric almost 12 yearsPlease use parameters, or google around about SQL injections
-
Reznor almost 12 yearsThanks for the help Remou! I'll switch everything to parameters today to avoid any future problems.