VBA- how to use' ' correctly

[Jana.Luo]

Hi there,

Part of the script like this below. The values in some columns are varchar. But this script works fine until it meets a name like this KK K'mm.

How can I modify the script to avoid this problem?

====================================

Sub Test1()
Dim SQL As String
Dim X As Integer

-------------------------
C.Execute "Truncate Table Agent_list"

For X = 2 To 142


SQL = "Insert Into Agent_list Values ( '" & Cells(X, 1) & "', "


If Cells(X, 2) = "" Then
SQL = SQL & "Null, "
Else
SQL = SQL & "'" & Cells(X, 2) & "', "
End If
---------------------------


C.Execute SQL

Next X

============================

many thanks,
Jana

 

[Norie]

Jana

What database are you trying to use this with?

Most have some way of 'escaping' characters like.

It really depends on the the database involved how to deal with this sort of thing.

 

[Jana.Luo]

what I want to do is I want to creat a temp table into the microsoft sql server by importing a spreadsheet. and I want to make a vba script to link the spreadsheet to the temp table in able to update the table value easily when I update the spreadsheet.

Does this explanation help?

 

[jreedich]

You are having a problem because of the single quote in the last name. Try the following code and see if this solves your problem.

Public Sub Test1()
Dim SQL As String
Dim X As Integer

'-------------------------
'C.Execute "Truncate Table Agent_list"

For X = 2 To 10
SQL = "Insert Into Agent_list Values ( '" & Cells(X, 1) & "', "
If Cells(X, 2) = "" Then
SQL = SQL & "Null, "
Else
If (InStr(1, Cells(X, 2), "'") > 0) Then
SQL = """" & Cells(X, 2) & """"
Else
SQL = SQL & "'" & Cells(X, 2) & "', "
End If
End If
'---------------------------
'C.Execute SQL

Next X
End Sub

 

[xenou]

In a pinch, this will probably work:

SQL = SQL & "'" & Replace(Cells(X, 2),"'","''") & "', "

 

[xenou]

More fully, I think:

For X = 2 To 10

    SQL = SQL & " Insert Into Agent_list Values ( "
    SQL = SQL &  "'" & Cells(X, 1).Value & "', "
    If Cells(X, 2) = "" Then
        SQL = SQL & "Null"
    Else
        SQL = SQL & Replace(Cells(X, 2),"'","''") & "' "
    End If
    SQL = SQL & ");"

    C.Execute SQL

Next x

We are using '' to indicate a single quote that is part of the quoted string itself.

Note: your SQL will be more robust if you use the field names (that way if the table structure changes, such as adding a field, the SQL still works:

INSERT INTO Agent_list ([COLOR="Blue"]AgentID, AgentName[/COLOR]) Values (1, 'O''Bama');

 

[Jana.Luo]

Hi Xenou,

If the situation only happens in cell (x,1), should I only use the first part of your script which related to cell (x,1)?

thanks,
Jana

 

[xenou]

Correct. I think I got my 1's and 2's mixed up. If both are text, then may as well cover both values:

For X = 2 To 10

    SQL = SQL & " Insert Into Agent_list Values ( "
    SQL = SQL = SQL & "'" & Replace(Cells(X, 1),"'","''") & "' , "
    If Cells(X, 2) = "" Then
        SQL = SQL & "Null"
    Else
        SQL = SQL & "'" & Replace(Cells(X, 2),"'","''") & "' "
    End If
    SQL = SQL & ");"

    C.Execute SQL

Next x

In the above, (X,1) cannot be null, otherwise both are treated the same. There is a remote possibility of failure if you actually have '' in a cell (two single quotes next to each other), since this would become '''' (four single quotes). But I think that very unlikely.

 

[Jana.Luo]

I have got one error message: missing the left "("

SQL="Flase'AKL'",'XX')"

 

[xenou]

SQL="Flase'AKL'",'XX')"

This doesn't look like a valid construction for any kind of sql that I recognize - back up a step. What's the outcome supposed to look like?

Note: was it supposed to be:

SQL = [COLOR="Blue"]SQL &[/COLOR] "Flase'AKL'",'XX')"