Excel Vba - Using an apostrophe in an sql string

Question

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

Fionnuala · Accepted Answer · 2012-08-03 15:00:06Z

12

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.

edited Aug 3, 2012 at 15:00

answered Aug 3, 2012 at 13:47

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Over a year ago

Please use parameters, or google around about SQL injections

Reznor Over a year ago

Thanks for the help Remou! I'll switch everything to parameters today to avoid any future problems.

Jonathan · Accepted Answer · 2013-02-06 04:05:52Z

Replace the apostrophe in values that are encased in single quotes (e.g. the O'Brien in 'O'Brien') as follows: O' & "'" & 'Brien

Use the following code snippet: Replace(rs![field1], " ' ", " ' " & " & " & """" & " ' " & """" & " & ' ") & "'" ). NOTE I have added a space between the single and double quotes to make it easier to tell them apart. In the actual code you use, you should not have these spaces.

For example

Instead of

Docmd.RunSQL ("INSERT INTO Tablename (DestinationField) SELECT '" & rs![field1] & "'")

use

Docmd.RunSQL ("INSERT INTO Tablename (DestinationField) SELECT '" & Replace(rs![field1], "'", "'" & " & " & """" & "'" & """" & " & '") & "'" )

This worked for me and allowed me to use VBA to insert values containing apostrophes (single quote marks) into a table using SQL insert statements

Collectives™ on Stack Overflow

Excel Vba - Using an apostrophe in an sql string

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related