0

I am using VB .Net to loop through a regex Match and generate a sql statement. I'm creating the sql like this

sql = "Insert Into Agencies (Address) Values"
While MatchObj.Success
    sql = sql & "(""" & MatchObj.Groups(1).Value & """), "
    MatchObj = MatchObj.NextMatch()
End While
sql = sql.Substring(0, Len(sql) - 2) & ";"

so when I print sql to the immediate window after creating it, I get this:

Insert Into Agencies (Address) Values(" 1330 W Indian School Rd, "), (" 3323 E Baseline Rd, "), (" 207 N Gilbert Rd, "), (" 3160 S. Gilbert Rd., Ste. 4, ");

I then create a OleDbCommand using the sql statement. I can connect up to the DB but when I run the .ExecuteNonQuery() I get this error: "Missing semicolon (;) at end of SQL statement"

My goal is to use a single INSERT to put all these values into the DB column "Address".

Any help is much appreciated.

Edit: Here is the sub I'm using to open and execute

Public Sub executeSQL(ByVal sql As String)
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Local Projects\AgenciesAZ.mdb'"
        Dim conn As OleDbConnection = New OleDbConnection(connString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)


        conn.Open()
        cmd.ExecuteNonQuery() 'error hits here
        conn.Close()
End Sub
TLS
  • 3,090
  • 2
  • 25
  • 33
user1457296
  • 239
  • 1
  • 3
  • 8
  • 2
    Which database? Could you show the connectionstring used and the code used to send the command to the database? – Steve Jun 14 '12 at 21:29
  • You are using double quotes instead of single quotes around the values within the parentheses. What happens when you use single quotes instead: `... Values (' 123 ABC, '), (' 456 XYX, ');`? – TLS Jun 14 '12 at 21:36
  • single quotes returns the same error – user1457296 Jun 14 '12 at 21:43
  • 1
    Well, do you know if your flavor of database *even supports* multi-value `insert` statements? Some versions of SQL Server support it, but you have not mentioned which database version you are using despite multiple requests for such information. "OleDbCommand" does not tell us what database flavor you are using. – TLS Jun 14 '12 at 21:46
  • 1
    I suspect that you are using MS-Access via OleDb. If this is the case then you can't do multiple inserts in that way. – Steve Jun 14 '12 at 21:47
  • 1
    There, I retagged the question with "ms-access," now that we know. – TLS Jun 14 '12 at 21:49
  • 2
    As supposed it is Microsoft Access. [Look at this question](http://stackoverflow.com/questions/62504/is-there-any-way-to-create-multiple-insert-statements-in-a-ms-access-query) – Steve Jun 14 '12 at 21:52

1 Answers1

2

You need to split your insert statement ,you have to use the "insert into..." every time that you want to insert something in the Address but also if you want to to execute it just once in VB you need to separate each statement with the semicolon (;), it should work

You need to do something like this. 

Insert Into Agencies (Address) Values
 (" 1330 W Indian School Rd, ");
 Insert Into Agencies (Address) Values
 (" 3323 E Baseline Rd, ");
 Insert Into Agencies (Address) Values
 (" 207 N Gilbert Rd, ");
 Insert Into Agencies (Address) Values
 (" 3160 S. Gilbert Rd., Ste. 4, ");

Edit

your while should be 

While MatchObj.Success
sql = " Insert Into Agencies (Address) Values"

    sql = sql & "(""" & MatchObj.Groups(1).Value & """); "
    MatchObj = MatchObj.NextMatch()
End While

Although I strongly recommend to insert each one in separate statements and in a transaction, but it depends to you.

Edit 2

You can't insert multiple records at the same time in MS- Access you must do it insert per insert like...

Public Sub executeSQL(ByVal sql As String)
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Local Projects\AgenciesAZ.mdb'"
        Dim conn As OleDbConnection = New OleDbConnection(connString)
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)


        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
End Sub

 While MatchObj.Success
    sql = " Insert Into Agencies (Address) Values"

        sql = sql & "(""" & MatchObj.Groups(1).Value & """); "
        executeSQL(sql )
        MatchObj = MatchObj.NextMatch()
    End While
Hector Sanchez
  • 2,297
  • 4
  • 26
  • 39
  • I still end up with the same error. I'm not sure I understand exactly how this should look as a string variable. Would I need to add the line feeds? Perhaps you could put this in the format: sql = "" thank you for your quick response – user1457296 Jun 14 '12 at 21:36
  • OP hasn't mentioned the database is SQL Server, so `GO` isn't necessarily an option. `GO` wouldn't work in code anyway, it's not a keyword. It's just an indicator for SSMS to split up scripts (in fact you can change `GO` to something else from SSMS options if you wish). – Bridge Jun 14 '12 at 21:37
  • Correct, this is not sql server so I'm using OleDbCommand...not sqlCommand – user1457296 Jun 14 '12 at 21:39
  • so which one? retag your question – Hector Sanchez Jun 14 '12 at 21:40
  • I think the SQL tag is correct cause the descriptions says "This tag is for general SQL programming questions; it is not for Microsoft SQL Server, nor does it refer to specific dialects of SQL on its own." I am new to the site though so I could be wrong. – user1457296 Jun 14 '12 at 21:49
  • that tag is correct, but also you should say what RDBMS you are using, to your specific case like SQL Server, MySql, Oracle, Access, we need that information to answer your specific question, you can specify up to 5 tags – Hector Sanchez Jun 14 '12 at 21:51
  • ah, ok...someone added the Access tag for me. Thanks for the clarification. – user1457296 Jun 14 '12 at 21:53
  • Edit 2 is very helpful. I think I will use that. Thanks a million. – user1457296 Jun 14 '12 at 22:08