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
EndSubGenerated 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
