CODEWITHSUNDEEP
sqlsql-serverexcelvba
res74
res74

Reputation: 37

ADODB Insert with Single Quote in String

I am using an Excel sheet to grab information from an SQL Server then someone adds some information and it gets inserted back into a different SQL database and table. The problem I am having is that the string has the ' in it and so the INSERT INTO fails

Private Sub SaveData_Click()
Dim conn As New ADODB.Connection
Dim iRowNo, answer, secondOne As Integer
Dim sJobNum, sWorkOrderNum, sItemDesc, sType, sCatName As String

answer = MsgBox("Are you sure you want to save the sheet?", vbYesNo + vbQuestion, "Save Categories")

If answer = vbYes Then
    With Sheets("JobInformation")

        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Data Source=10.0.2.2;Initial Catalog=ReportingAddOn;User ID=SOMEID;Password=SOMEPASSWORD"

        'Skip the header row
        iRowNo = 2

        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            If .Cells(iRowNo, 5) <> "" Then
               sJobNum = .Cells(iRowNo, 1)
               sWorkOrderNum = .Cells(iRowNo, 2)
               sItemDesc = .Cells(iRowNo, 3)
               sType = .Cells(iRowNo, 4)
               sCatName = .Cells(iRowNo, 5)

               'Generate and execute sql statement to import the excel rows to SQL Server table
               conn.Execute "INSERT INTO dbo.TryOutCategoryComplete (JobNumber, WorkOrderNumber, ItemDescription, Type, CategoryName) VALUES ('" & sJobNum & "', '" & sWorkOrderNum & "', '" & sItemDesc & "', '" & sType & "', '" & sCatName & "')"
            End If
            iRowNo = iRowNo + 1
        Loop

        secondOne = MsgBox("Categories Saved!", vbOKOnly, "Successfully Saved!")

        conn.Close
        Set conn = Nothing

        Sheets("CategoryName").Select
    End With
End If
End Sub

The problem becomes when the sItemDesc has something like this:

It is a value that hasn't happened

So the "hasn't" would error as the single quote mark in it causes an end of string in the INSERT INTO

So how would I go about still being able to use the INSERT the way it is but have it watch out for the single quote block?

Upvotes: 0

Views: 1269

Answers (1)

Parfait
Parfait

Reputation: 107767

Classic example to why one should use parameterization (an industry best practice) when inserting values into backend databases at the application layer as no quote enclosures are needed and therefore does not affect actual values.

Excel VBA can run parameterized queries with the ADO Command object where you bind parameters to a prepared statement divorcing SQL from VBA values. Below you will see the prepared SQL statement assigned once outside the loop:

Dim conn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
...

With Sheets("JobInformation")

    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=10.0.2.2;Initial Catalog=ReportingAddOn;User ID=SOMEID;Password=SOMEPASSWORD"

    'Skip the header row
    iRowNo = 2

    ' PREPARED STATEMENT WITH QMARK PLACEHOLDERS
    strSQL = "INSERT INTO dbo.TryOutCategoryComplete (JobNumber, WorkOrderNumber, ItemDescription, Type, CategoryName) VALUES (?, ?, ?, ?, ?)"

    'Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = ""
        If .Cells(iRowNo, 5) <> "" Then
           sJobNum = .Cells(iRowNo, 1)
           sWorkOrderNum = .Cells(iRowNo, 2)
           sItemDesc = .Cells(iRowNo, 3)
           sType = .Cells(iRowNo, 4)
           sCatName = .Cells(iRowNo, 5)

          ' COMMAND OBJECT 
           Set cmd = New ADODB.Command

           With cmd
               .ActiveConnection = cn    ' CONNECTION OBJECT
               .CommandText = strSQL     ' SQL STRING     
               .CommandType = adCmdText

               ' BINDING PARAMETERS
               .Parameters.Append .CreateParameter("sJobNumParam", adVarChar, adParamInput, , sJobNum)
               .Parameters.Append .CreateParameter("sWorkOrderNumParam", adVarChar, adParamInput, , sWorkOrderNum)
               .Parameters.Append .CreateParameter("sItemDescParam", adVarChar, adParamInput, , sItemDesc)
               .Parameters.Append .CreateParameter("sTypeParam", adVarChar, adParamInput, , sType)
               .Parameters.Append .CreateParameter("sCatNameParam", adVarChar, adParamInput, , sCatName)

               .Execute                 ' RUN ACTION
           End With

           Set cmd = Nothing

        End If
        iRowNo = iRowNo + 1
    Loop

    secondOne = MsgBox("Categories Saved!", vbOKOnly, "Successfully Saved!")

    conn.Close
    Set conn = Nothing

    Sheets("CategoryName").Select
End With

Upvotes: 1

Related Questions