How to make an INSERT QUERY String using an array of variables

Question

My code is:

For index = 0 To dupColIndx - 1
    expression(index) = dgvINSRT.Rows.Item(i).Cells.Item(index).Value.ToString
Next

Dim strInsrt As String = "INSERT INTO " & _ 
                         dbTbl & _
                         colStr & _
                         Strings.Replace(expression(0), "'", "''", 1, -1, CompareMethod.Binary) & "','" & _
                         Strings.Replace(expression(1), "'", "''", 1, -1, CompareMethod.Binary) & "','" & _
                         Strings.Replace(expression(2), "'", "''", 1, -1, CompareMethod.Binary) & "')"

In the code above instead of entering manually expression(0), expression(1), etc. I want the string to be concatenated automatically once I enter the value of 'n' for expression(n).

Thanks.

How are you utilizing the insert query string? Are you actually sending the command to the database? If so, I would highly recommend using a parameterized (through an SqlCommand, OracleCommand, etc) to avoid issue with SQL injection (or to catch bad data). Would be easy enough to loop through your array to add each item as a parameter to the command object to send to the database. Need to know what type of command object you are using if this is the case, to write an appropriate code sample. — RianBattle
– RianBattle, Commented Mar 31, 2015 at 17:56

Guffa · Accepted Answer · 2015-03-31 18:05:17Z

1

You can use String.Join:

For index = 0 To dupColIndx - 1
    expression(index) = dgvINSRT.Rows.Item(i).Cells.Item(index).Value.ToString.Replace("'", "''")
Next

Dim strInsrt As String = "INSERT INTO " & _ 
                     dbTbl & _
                     colStr & _
                     String.Join("','", expression) & "')"

Note: If you escape values yourself, it's crucial that you use the correct replacement for the database that you are using, or the query is wide open for SQL injection attacks. The method used here works for SQL Server and Access, but might be wrong for other databases. It's not appropriate for MySQL for example, then you also need to escape backslashes.

answered Mar 31, 2015 at 18:05

Guffa

703k111 gold badges760 silver badges1k bronze badges

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

3 Comments

TheDProgrammer Over a year ago

The string is returned like this:

INSERT INTO ['Part Commodity Class$']([Dispatch Commodity],[Dispatch Commodity Temp],[Dispatch Commodity 2]) VALUES ('DispatchCommodity3','DispatchCommodityTemp3','DISPATCHCOMMODITY3','')

Everything is fine but I am not able to INSERT INTO the dataTable because of the ,'' at the end. Please help in removing this.

Guffa Over a year ago

@TheDProgrammer: That means that the array expression has one item too much. It should be created using ReDim expression(dupColIndx - 1).

TheDProgrammer Over a year ago

You Rock!...It worked with Dim expression(dupColIndx - 1) As String...Thanks a lot :)

Bruno · Accepted Answer · 2015-04-01 15:10:28Z

1

If expression.Count >= 1
    Dim strInsrt As String = "INSERT INTO " & dbTbl & colStr

    For i= 0 To expression.Count - 2
        strInsrt &= "'" & Strings.Replace(expression(i), "'", "''", 1, -1, CompareMethod.Binary) & "',"
    Next i

    strInsrt &= "'" & Strings.Replace(expression(expression.Count - 1), "'", "''", 1, -1, CompareMethod.Binary) & "')"
End If

edited Apr 1, 2015 at 15:10

answered Mar 31, 2015 at 17:44

Bruno

4,6757 gold badges65 silver badges117 bronze badges

2 Comments

TheDProgrammer Over a year ago

There is an extra comma and double quote (viz. ,'') at the end which is rendering the INSERT STATEMENT unusable. Please help me correct this:

INSERT INTO ['Part Commodity Class$']([Dispatch Commodity],[Dispatch Commodity Temp],[Dispatch Commodity 2]) VALUES ('DispatchCommodity2','DispatchCommodityTemp2','DISPATCHCOMMODITY2','')

Bruno Over a year ago

updated my answer, you might have to modify a bit the colStr value since I don't know what's in it.

Collectives™ on Stack Overflow

How to make an INSERT QUERY String using an array of variables

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related