OleDbException:Syntax error (missing operator) in...

[franc_xav]

Hello,

Can anyone tell me what is wrong with the following code:

    Private Sub BT_Save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BT_Save.Click


        'Retrieve data from database
        Dim Connec_String As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Visual Studio 2010\Projects\ddbb_tec_V3\ddbb_tec.mdb"
        Dim cnn As New OleDb.OleDbConnection(Connec_String)
        cnn.Open()


        ' Queries to select all rows in the tables
        Dim query_Tec_Data As String = "SELECT * FROM " & "Tec_Data"


        'Create Comand and Adaptar for all tables
        Dim cm_query_Tec_Data As New OleDb.OleDbCommand(query_Tec_Data, cnn)
        Dim Adapter_Tec_Data As New OleDb.OleDbDataAdapter(cm_query_Tec_Data)




        'Load all tables in the dataset
        Dim DS_View_Tec As New DataSet
  
        Adapter_Tec_Data.Fill(DS_View_Tec, "Tec_Data")
        cnn.Close()


        Dim DBuilber_Tec_Data As New OleDb.OleDbCommandBuilder(Adapter_Tec_Data)
        Adapter_Tec_Data.UpdateCommand = DBuilber_Tec_Data.GetUpdateCommand()
        Adapter_Tec_Data.InsertCommand = DBuilber_Tec_Data.GetInsertCommand()
        Adapter_Tec_Data.DeleteCommand = DBuilber_Tec_Data.GetDeleteCommand()


        'Save data in the datarow
        Dim Modified_Tec_DataRow() As DataRow
        Modified_Tec_DataRow = DS_View_Tec.Tables("Tec_Data").Select(Filter_Tec)
        Modified_Tec_DataRow(0).Item("Name") = TB_Name.Text
        Modified_Tec_DataRow(0).Item("Surname") = TB_Surname.Text
        Modified_Tec_DataRow(0).Item("Card_Id") = TB_CardId.Text
        Modified_Tec_DataRow(0).Item("Cell") = TB_Tel.Text
        Modified_Tec_DataRow(0).Item("Cell_Short") = TB_Short.Text
        Modified_Tec_DataRow(0).Item("email") = TB_mail.Text
        Modified_Tec_DataRow(0).Item("Work") = TB_job.Text
   
        Try
[B]            Adapter_Tec_Data.Update(DS_View_Tec, "Tec_Data")[/B]
        Catch ex As Exception
            MsgBox("ERROR:" & ex.Source & " " & ex.Message, MsgBoxStyle.OkOnly)
        End Try


        Me.Close()


    End Sub

I tried many things, but I always get an error with the update method.

Where is my mistake?

Thanks

 

[jmcilhinney]

First of all, these lines are pointless:

        Adapter_Tec_Data.UpdateCommand = DBuilber_Tec_Data.GetUpdateCommand()
        Adapter_Tec_Data.InsertCommand = DBuilber_Tec_Data.GetInsertCommand()
        Adapter_Tec_Data.DeleteCommand = DBuilber_Tec_Data.GetDeleteCommand()

Secondly, why are you concatenating two literal Strings here:

Dim query_Tec_Data As String = "SELECT * FROM " & "Tec_Data"

It's a small thing and doesn;t actually do any harm but indicates things being done by rote and no actual thought about what the code is doing.

As for the issue, this sort of thing often happens when using a command builder when the query uses a wildcard for the column list. If any column contains spaces or other special characters or is a reserved word then a syntax error results. You need to escape the offending column name(s) to avoid this, which means setting the QuotePrefix and QuoteSuffix properties of the command builder. Many databases support "`" for both but, for Access, you use "[" and "]" respectively.

 

[franc_xav]

Thanks a lot. After adding the lines below, the error is gone.

DBuilber_Tec_Data.QuotePrefix = "["
DBuilber_Tec_Data.QuoteSuffix = "]"

 

[sammy1986]

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If txtAuthor.Text = "" Then
MsgBox("Empty Field Detected")
ElseIf txtBID.Text = "" Then
MsgBox("Empty Field Detected")
ElseIf txtTransactionNo.Text = "" Then
MsgBox("Empty Field Detected")
ElseIf txtCourse.Text = "" Then
MsgBox("Empty Field Detected")
ElseIf txtTitle.Text = "" Then
MsgBox("Empty Field Detected")
ElseIf txtStudName.Text = "" Then
MsgBox("Empty Field Detected")
Else
Dim OleDBC1 As New OleDbCommand
Dim oledbdr As OleDbDataReader
With OleDBC1


.Connection = conn
.CommandText = "SELECT * FROM tblTransactionDetails WHERE StudentID='" & txtID.Text & "' Or WHERE BookID='" & txtBID.Text & "'"
.ExecuteNonQuery()
End With
oledbdr = OleDBC1.ExecuteReader
oledbdr.Read()


If oledbdr.HasRows Then
MsgBox("The Student Haven't Returned the Book Borrowed Or Book Not Available")
Exit Sub
Else
Call functions()
End If
End If
End Sub

I want it to validate two (2) fields in same table in the database... but its not working!!!
Please help me..