Inserting names from Excel to SQL Server table (VBA)

Question

I have written a simple code to loop through rows and insert values in column A (that is column containing full name) into an SQL Table. Something like this:

    For i = 1 to LastRow

    Command.CommandText = "INSERT INTO [TABLE] [Col1] VALUES ('" & Sheets("Sheet1").Cells(i, 1).Value & "')"

    Next i

Issue arises when we have names like [O'Connell], which obviously creates a Bobby Table issue.

Are there any clever workarounds to avoid this?

Thanks

N.B. The full name is inserted from another SQL Table. In other words, perhaps a little difficult to edit.

Possible duplicate of [VBA, ADO.Connection and query parameters](https://stackoverflow.com/questions/10352211/vba-ado-connection-and-query-parameters) — Victor K, Jun 10 '18 at 02:09

score 1 · Accepted Answer · answered Jun 10 '18 at 02:09

1

You could use Replace to remove the problem character:

Command.CommandText = "INSERT INTO [TABLE] [Col1] VALUES ('" & _
    Replace(Sheets("Sheet1").Cells(i, 1).Value,"'","") & "')"

answered Jun 10 '18 at 02:09

ashleedawg

20,365
9
72
105

Brilliant... So in theory I can use this to santize all my strings from (') and (;)... Correct? – Oday Salim Jun 10 '18 at 02:19
1

@OdaySalim in theory yes. And you can nest them `replace(replace(replace(yourstring,"'",""),"*",""),".","")` etc – ashleedawg Jun 10 '18 at 02:36
How would you nest them? – Oday Salim Jun 10 '18 at 02:36
if there are any `"` make sure you get those too replace `Chr(34)` with whatever – ashleedawg Jun 10 '18 at 02:37
One more question dude, why are you sanitizing the "."? I do not see anything malicious with it? – Oday Salim Jun 10 '18 at 14:37

Inserting names from Excel to SQL Server table (VBA)

1 Answers1