VB.Net 2010 insert to access syntax error

• • Reply

  9 years ago

  by Nessie

  Ken Nessworthy

  , United Kingdom

  Joined 9 years ago

  Hi Everyone, been trying to resolve this myself before resorting to forum for help :(

  I am getting a syntax error while trying to do an insert into access 2007 DB, the query looks ok and I can even run it on access and insert it into the table. Any help would be greatly appreciated and would enable me to continue developing this project.

  CODE.....

  Private Sub ButCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButCommit.Click
  
      con.Open()
  
      TxtPrice.Text = Val(TxtPriceCat.Text) * Val(TxtQty.Text) 'gets the total price of goods
  
      If inc <> -1 Then
  
          Dim TrioleNo, Qty, Price, SWCostCentre, EmployeeID As String
          Dim TrioleOpenDate, OrderDate, NextActionDate, DeliveryDate As Date
          Dim Description, SWGateKeeper, User, Status, NextAction, Chase, Strike, Notes, sqlInsert As String
          Dim result As Integer = -1
          Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand
          Dim myConnection = New OleDb.OleDbConnection()
  
          TrioleNo = Integer.Parse(TxtTriole.Text)                  '6 or 7 digit integer
          TrioleOpenDate = DateOpen.Value.Date                'date taken from datetimepicker
          Description = ComboBoxDesc.Text.Trim                'string taken from dropdown list linked to ICT Catalogue table
          Qty = TxtQty.Value.ToString                                         'Numeric up and down
          Price = TxtPrice.Text                                                     'price taken from ICT Catalogue table multiplied with the number required
          SWCostCentre = Integer.Parse(TxtCostCenter.Text)    '5 digit integer
          SWGateKeeper = TxtGatekeeper.Text.Trim              'string
          User = TxtUser.Text.Trim                                              'string
          EmployeeID = Integer.Parse(TxtID.Text)                   '9 digit integer
          OrderDate = DateOrder.Value.Date                           'date taken from datetimepicker
          Status = CBStatus.Text.Trim                                        'string taken from dropdown list
          NextAction = CBNext.Text.Trim                                   'string taken from dropdown list
          NextActionDate = DateNextAction.Value.Date          'date taken from datetimepicker
          Chase = CBChase.Text.Trim                                      'string taken from dropdown list
          DeliveryDate = DateDelivery.Value.Date                   'date taken from datetimepicker
          Strike = CBStrike.Text.Trim                                         'string taken from dropdown list
          Notes = TxtNotes.Text.Trim                                        'string
  
          If DateOrder.Text > DateDelivery.Text Then
              MsgBox("Delivery Date cannot be before Order Date")
          End If
  
          sqlInsert = "insert into tracker (TrioleNo,TrioleOpenDate,Description,Qty,Price,SWCostCentre,SWGateKeeper,User,EmployeeID,OrderDate,Status,NextAction,NextActionDate,Chase,DeliveryDate,Strike,Notes) values (" + TrioleNo + ",'" & TrioleOpenDate & "','" + Description + "'," + Qty + "," + Price + "," + SWCostCentre + ",'" + SWGateKeeper + "','" + User + "'," + EmployeeID + ",'" & OrderDate & "','" + Status + "','" + NextAction + "','" & NextActionDate & "','" + Chase + "','" & DeliveryDate & "','" + Strike + "','" + Notes + "')"
  
          Try
              SqlCommand.Connection = con
              SqlCommand.CommandText = sqlInsert
              result = SqlCommand.ExecuteNonQuery()
              If result = 0 Then
                  MsgBox("Record NOT added to database")
              Else
                  MsgBox("New Record added to database")
              End If
          Catch ex As Exception
              MsgBox(ex.ToString)
   
  

  Report abuse