VB.Net 2010 insert to access syntax error

Access , VB , VB.Net Edinburgh, United Kingdom
  • 7 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)
     
    

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Theory is when you know something, but it doesn't work. Practice is when something works, but you don't know why. Programmers combine theory and practice: Nothing works and they don't know why.”