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.

PrivateSub 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 AsString
Dim TrioleOpenDate, OrderDate, NextActionDate, DeliveryDate AsDate
Dim Description, SWGateKeeper, User, Status, NextAction, Chase, Strike, Notes, sqlInsert AsString
Dim result AsInteger = -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")
EndIf

sqlInsert = "insert into tracker (TrioleNo,TrioleOpenDate,Description,Qty,Price,SWC ostCentre,SWGateKeeper,User,EmployeeID,OrderDate,S tatus,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")
EndIf
Catch ex AsException
MsgBox(ex.ToString)
EndTry

result = -1

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

EndIf

EndSub

Generated query....

insert into tracker (TrioleNo,TrioleOpenDate,Description,Qty,Price,SWC ostCentre,SWGateKeeper,User,EmployeeID,OrderDate,S tatus,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 your help inadvance

Recommended Answers

All 8 Replies

I think You should use Textbox name instead column name in your insert statement in values section you are entering your column name you should use textbox name follow this example

sqlInsert = "INSERT INTO tracker(TrioleNo,TrioleOpenDate,Description,Qty,Price) VALUES ('" & Me.Textbox1.Text & "','" & Me.Textbox2.Text & "','" & Me.Textbox3.Text & "','" & Me.Textbox4.Text & "','" & Me.Textbox5.Text & "')"

I hope this would help you.

Many thanks, I'll give it a go and report back :)

AquaNut did you use debug.print to get the "Generated query" or are you typing to us what it should generate?
What is the syntax error you are getting ? Is it from Access about your query or from VB about your syntax (I don't think you should be using + to concatenate)?

I have re-wrote my code but still getting an syntax error on the insert from VB.

This is the new code..

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

and the generated SQL query is..

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)

Not sure if I'm going the right way with this???

I have re-wrote my code but still getting an syntax error on the insert from VB.

This is the new code..

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

and the generated SQL query is..

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)

Not sure if I'm going the right way with this???

"User" is a reserved word in access.
You should use [User] instead.
About "Status" and "Description" im not sure if they are reserved.

Changed the names int he DB and all is working now...many thanks for your help..

Please Mark the thread as reolved

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.