0

I am using update query statment but is not working for this code

Dim myConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../lms/DBLMS.mdb")
        Dim item As Integer
        myConn.Open()
        myComm = New OleDbCommand("UPDATE tblVehicles SET Vehicle_Type = " & cboVehicle_Type.Text & ", Vehicle_Model = " & txtVehicle_Model.Text & ", Plate_No = " & txtPlate_No.Text & ", Date_Acquired = " & dtpDate.Text & ", WHERE Vehicle_Id = " & txtVehicle_Id.Text & ")", myConn)
        item = myComm.ExecuteNonQuery()
        myConn.Close()

ERROR - Syntax error (missing operator) in query expression '02/09/2011 01:21 PM'.

Edited by TIP.Synergy: i forgot to insert [CODE]

4
Contributors
6
Replies
10
Views
6 Years
Discussion Span
Last Post by DONPAWPAW
Featured Replies
  • 1
    __avd 1,826   6 Years Ago

    Use Parameters. [code] Dim myConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../lms/DBLMS.mdb") Dim item As Integer myComm = New OleDbCommand("UPDATE tblVehicles SET Vehicle_Type =@p1, Vehicle_Model =@p2,Plate_No =@p3,Date_Acquired =@p4 WHERE Vehicle_Id = @p5", myConn) myComm.Parameters.AddWithValue("@p1",cboVehicle_Type.Text) myComm.Parameters.AddWithValue("@p2",txtVehicle_Model.Text) myComm.Parameters.AddWithValue("@p3",txtPlate_No.Text) myComm.Parameters.AddWithValue("@p4",dtpDate.Value) myComm.Parameters.AddWithValue("@p5",txtVehicle_Id.Text) myConn.Open() item=myComm.ExecuteNonQuery() myConn.Close() [/code] Read More

0

TIP.Synergy
remove the integer (item ) decleared
ie item = myComm.ExecuteNonQuery()
make it as
myComm.ExecuteNonQuery()
and try

Edited by DONPAWPAW: correct

1

Use Parameters.

Dim myConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../lms/DBLMS.mdb")

Dim item As Integer
myComm = New OleDbCommand("UPDATE tblVehicles SET Vehicle_Type =@p1, Vehicle_Model =@p2,Plate_No =@p3,Date_Acquired =@p4 WHERE Vehicle_Id = @p5", myConn)


myComm.Parameters.AddWithValue("@p1",cboVehicle_Type.Text)
myComm.Parameters.AddWithValue("@p2",txtVehicle_Model.Text)
myComm.Parameters.AddWithValue("@p3",txtPlate_No.Text)
myComm.Parameters.AddWithValue("@p4",dtpDate.Value)
myComm.Parameters.AddWithValue("@p5",txtVehicle_Id.Text)

myConn.Open()
item=myComm.ExecuteNonQuery()
myConn.Close()
Comments
showing the way it should be done.
0

Use Parameters.

Dim myConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../lms/DBLMS.mdb")

Dim item As Integer
myComm = New OleDbCommand("UPDATE tblVehicles SET Vehicle_Type =@p1, Vehicle_Model =@p2,Plate_No =@p3,Date_Acquired =@p4 WHERE Vehicle_Id = @p5", myConn)


myComm.Parameters.AddWithValue("@p1",cboVehicle_Type.Text)
myComm.Parameters.AddWithValue("@p2",txtVehicle_Model.Text)
myComm.Parameters.AddWithValue("@p3",txtPlate_No.Text)
myComm.Parameters.AddWithValue("@p4",dtpDate.Value)
myComm.Parameters.AddWithValue("@p5",txtVehicle_Id.Text)

myConn.Open()
item=myComm.ExecuteNonQuery()
myConn.Close()

Thx to you Mr.adatapost, It works, but i don't know which is better parameter or non parameter?
but finally, i decided to use parameters

0
'copy and past and it will work perfectly
'you eliminated the single quote in yor query
'remove the variable decleared (Dim item As Integer)

Dim myConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= Provider=Microsoft.Jet.OLEDB.4.0;Data Source=../lms/DBLMS.mdb")   
   
     myConn.Open()     

   myComm = New OleDbCommand("UPDATE tblVehicles SET Vehicle_Type = '" & cboVehicle_Type.Text & "', Vehicle_Model = '" & txtVehicle_Model.Text & "', Plate_No = '" & txtPlate_No.Text & "', Date_Acquired = '" & dtpDate.Text & "', WHERE Vehicle_Id = '" & txtVehicle_Id.Text & "')", myConn) 

     
myComm.ExecuteNonQuery()   

myConn.Close()
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.