Insert syntax error

[Nessie]

Hi All,

Been reluctant to drop this on anyone as I wanted to resolve it myself but alas it has got the better of me. I am trying to do an insert into an Access 2007 DB but keep getting a syntax error even thougt I can get the generated query and run it in Access and successfully add to the table. I would greatly appreciate any help on this so I can continue with the development of 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)
End Try

result = -1

SqlCommand = Nothing
ButCommit.Enabled = False
ButNew.Enabled = True
ButUpdate.Enabled = True
ButDelete.Enabled = True
con.Close()

End If

End Sub

Generated query....

insert into tracker (TrioleNo,TrioleOpenDate,Description,Qty,Price,SWCostCentre,SWGateKeeper,User,EmployeeID,OrderDate,Status,NextAction,NextActionDate,Chase,DeliveryDate,Strike,Notes) values (99999999,'14/10/2011','Mobile Accessory - Nokia 2330 Cradle',2,132.08,12345,'Sweeny Todd','Joe Bloggs',9876543,'14/10/2011','Open','Chase User','15/10/2011','BT Mobile','20/10/2011','NA','test')

many many thanks for you help inadvance

 

[jmcilhinney]

Don't ever use string concatenation to insert values into SQL code. Always use parameters. Follow the Blog link in my signature and check out my post on ADO.NET Parameters. Once you fix your code in that regard your issue will almost certainly go away.

 

[Nessie]

I must admit after reading your blog pertaining to ADO it made a lot of sence and after rewriting the code as advised found it much easier to read but unfortunetly it is still throwing up that darn SQL Insert syntax error.

New 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
        'TxtPrice.Text = Integer.Parse(TxtPriceCat.Text) * Integer.Parse(TxtQty.Text)

        If inc <> -1 Then

            Dim result As Integer = -1
            Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand
            Dim myConnection = New OleDb.OleDbConnection()

            If DateOrder.Text > DateDelivery.Text Then
                MsgBox("Delivery Date cannot be before Order Date")
            End If

            Dim sql As String = "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)"

            Dim myCommand As New OleDb.OleDbCommand(sql)

            With myCommand.Parameters
                .AddWithValue("@TrioleNo", CInt(Me.TxtTriole.Text))                       'Integer value
                .AddWithValue("@TrioleOpenDate", Me.DateOpen.Value.Date)            'Short date value
                .AddWithValue("@Description", Me.ComboBoxDesc.Text)                   'String taken from linke to Cat table
                .AddWithValue("@Qty", CInt(Me.TxtQty.Value))                              'Integer value
                .AddWithValue("@Price", CInt(Me.TxtPrice.Text))                             'Integer value
                .AddWithValue("@SWCostCentre", CInt(Me.TxtCostCenter.Text))        'Integer value
                .AddWithValue("@SWGateKeeper", Me.TxtGatekeeper.Text)               'String
                .AddWithValue("@User", Me.TxtUser.Text)                                      'String
                .AddWithValue("@EmployeeID", CInt(Me.TxtID.Text))                        'Integer value
                .AddWithValue("@OrderDate", Me.DateOrder.Value.Date)                   'Short date value
                .AddWithValue("@Status", Me.CBStatus.Text)                                 'String
                .AddWithValue("@NextAction", Me.CBNext.Text)                              'String
                .AddWithValue("@NextActionDate", Me.DateNextAction.Value.Date)     'Short date value
                .AddWithValue("@Chase", Me.CBChase.Text)                                   'String
                .AddWithValue("@DeliveryDate", Me.DateDelivery.Value.Date)             'Short date value
                .AddWithValue("@Strike", Me.CBStrike.Text)                                    'String
                .AddWithValue("@Notes", Me.TxtNotes.Text)                                   'String
            End With

            Try
                SqlCommand.Connection = con
                SqlCommand.CommandText = sql
                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)

            End Try

            result = -1

            SqlCommand = Nothing

            ButCommit.Enabled = False
            ButNew.Enabled = True
            ButUpdate.Enabled = True
            ButDelete.Enabled = True

            con.Close()

        End If
    End Sub

Resulting SQL query....

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)

 

[jmcilhinney]

It's not obvious to me why you'd get a syntax error in that SQL. The only thing I can think of is that one of your column names is a reserved word, but it doesn't look like it. Try wrapping each single-word column name in brackets [] and see if that helps.

 

[Nessie]

Get another error - No value given for one or more required parameters.


Dim sql = "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)"

Dim myCommand As New OleDb.OleDbCommand(sql)

With myCommand.Parameters
.AddWithValue("@TrioleNo", CInt(Me.TxtTriole.Text)) 'Integer value
.AddWithValue("@TrioleOpenDate", Me.DateOpen.Value.Date) 'Short date value
.AddWithValue("@Description", Me.ComboBoxDesc.Text) 'String taken from linke to Cat table
.AddWithValue("@Qty", CInt(Me.TxtQty.Value)) 'Integer value
.AddWithValue("@Price", CInt(Me.TxtPrice.Text)) 'Integer value
.AddWithValue("@SWCostCentre", CInt(Me.TxtCostCenter.Text)) 'Integer value
.AddWithValue("@SWGateKeeper", Me.TxtGatekeeper.Text) 'String
.AddWithValue("@User", Me.TxtUser.Text) 'String
.AddWithValue("@EmployeeID", CInt(Me.TxtID.Text)) 'Integer value
.AddWithValue("@OrderDate", Me.DateOrder.Value.Date) 'Short date value
.AddWithValue("@Status", Me.CBStatus.Text) 'String
.AddWithValue("@NextAction", Me.CBNext.Text) 'String
.AddWithValue("@NextActionDate", Me.DateNextAction.Value.Date) 'Short date value
.AddWithValue("@Chase", Me.CBChase.Text) 'String
.AddWithValue("@DeliveryDate", Me.DateDelivery.Value.Date) 'Short date value
.AddWithValue("@Strike", Me.CBStrike.Text) 'String
.AddWithValue("@Notes", Me.TxtNotes.Text) 'String
End With

There is a primary key in access called PKID which is set to auto number.

Not sure if it's the way I am multiplying these values..

TxtPrice.Text = Val(TxtPriceCat.Text) * Val(TxtQty.Text) 'gets the total price of goods

where TxtPriceCat.Text is taken from another table and TxtQty.Text is a numericupdown then the results being part of the insert query.

 

[jmcilhinney]

Ah, now that I look again I see that the issue was probably your User column, which is likely to be a reserved word.

As for the current issue, it's hard to say because your code is a bit all over the place. My guess is that you are adding the parameters to one command and then exedcuting another. Looking back atg post #3 youj have too connection objects and two command objects. Why?

 

[Nessie]

Thanks for pointing that out, all working now