0

I am trying to insert multiple record into my Access Database. Currently I can insert a single transaction record with the code below

Try
    sql = "INSERT INTO  tblINV_SalesRecord " &_ 
          "(transID, itemcode, itemname, itemunits ) " &_ 
          "VALUES ('TGR011111','Cheese','INV0234','5')"

    Using con1 As New OleDbConnection(dbProvider & dbSource)
       Dim command As New OleDbCommand(Sql, con1)
       con1.Open()
       command.ExecuteScalar()
        MsgBox("Record Inserted")
    End Using
Catch ex As Exception
      MsgBox("An Error Occured")
      MessageBox.Show(ex.Message & " - " & ex.Source)
End Try

Now what I am trying to do is to insert multiple items purchased by customer in a single SQL statement. My code is shown below.

Try
    sql = "INSERT INTO  tblINV_SalesRecord " &_ 
          "(transID, itemcode, itemname, itemunits ) " &_ 
          "VALUES ('TGR011111','Cheese','INV0234','5')," &_ 
                 "('TGR011111','Cake','INV0114','2')," &_ 
                 "('TGR011111','Burger','INV0217','3')" &_ 

    Using con1 As New OleDbConnection(dbProvider & dbSource)
       Dim command As New OleDbCommand(Sql, con1)
       con1.Open()
       command.ExecuteScalar()
        MsgBox("Record Inserted")
    End Using
Catch ex As Exception
      MsgBox("An Error Occured")
      MessageBox.Show(ex.Message & " - " & ex.Source)
End Try

But it returns Error. Please how do I get this resolved?

Smarton
  • 94
  • 1
  • 10
  • Did you read the error? – Ňɏssa Pøngjǣrdenlarp Jul 09 '16 at 15:31
  • @Plutonix The Error is " Missing Semicolon (;) at end of SQL statement - Microsoft Jet Engine Database" – Smarton Jul 09 '16 at 15:54
  • @Steve Please check carefully:(1) I asked a specific VB.net Question not a VBA question (2) Inserting records in Msaccess is quite simple and straight forward , but the specific problem here is doing it in VB.Net – Smarton Jul 09 '16 at 15:59
  • 1
    As you can see in the duplicate Access cannot understand that syntax. So it is not a problem of VBA or VB.NET. The receiving part cannot understand what are you writing. In the same duplicate there is a possible workaround. INSERT INTO ---- SELECT ----- UNION SELECT ---. If you tell me that this also doesn't work then there is no problem to reopen the question – Steve Jul 09 '16 at 16:02
  • @Steve I tried the solution but if gives the following error "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. - Microsoft Jet Engine Database" . Any help would be appreciated – Smarton Jul 09 '16 at 21:17
  • My new SQL statement based on your suggestion is now : sql = "INSERT INTO tblINV_SalesRecord (transgroupID, itemcode, itemname, itemunits ) " & _ " SELECT * FROM (select top 1 'TGR011112' AS transgroupID, 'Burger' AS itemname, 'INV0234' AS itemcode,'5' AS itemunits from tblINV_SalesRecord union select top 1 'TGR011112' AS transgroupID, 'Cheese' AS Meat Pie, 'INV0234' AS itemcode,'5' AS itemunits from tblINV_SalesRecord )" – Smarton Jul 09 '16 at 21:19
  • _'Cheese' AS Meat Pie_ should be _'Cheese' AS Itemname_ – Steve Jul 09 '16 at 21:42
  • Yes Correct. Sorry I skipped that. "Records Inserted" Thanks a Lot. – Smarton Jul 10 '16 at 07:55

0 Answers0