Search and update data to database

[Satyam_1]

I am using vb.net with OleDb database. Following is my code, although i am to search database but not able to save after making changes. It displays following error at Command.ExecuteNonQuery level as OleDbException was unhandled, Data type mismatch in criteria expression.

Imports.Sysytem.Data.OleDb

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

End Sub

Private Sub Src_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Src.Click
    Dim Connection1 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Satyam\Documents\Database2.accdb;" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & "your pass" & ";")
    Dim cmd As OleDbCommand

    Dim dr As OleDbDataReader
    Try
        Connection1.Open()
        cmd = New OleDbCommand("SELECT *  from Table1 WHERE ID=" & IDtxt.Text & "", Connection1)

        dr = cmd.ExecuteReader
        If dr.Read Then
            Me.IDtxt.Text = dr("ID")
            Me.Hbtxt.Text = dr("Hb")
            Me.TCtxt.Text = dr("TC")
            Me.DCtxt.Text = dr("DC")

            dr.Close()
        Else
            MsgBox("No Record")
        End If
    Catch
    End Try
    Connection1.Close()
End Sub


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim Connection1 As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Satyam\Documents\Database2.accdb;")
    Connection1.Open()
    Dim ID : ID = Me.IDtxt.Text
    Dim Hb : Hb = Me.Hbtxt.Text
    Dim TC : TC = Me.TCtxt.Text
    Dim DC : DC = Me.DCtxt.Text


    Dim command As New OleDbCommand("UPDATE Table1 SET Hb = '" & Hb & "',TC = '" & TC & "',DC = '" & DC & "' WHERE ID = '" & ID & "'", Connection1)
    Command.ExecuteNonQuery()

    Connection1.Close()


End Sub

[Sulaiman_1]

Could you please confirm the Table1's fields are in the non numeric data types?

 Dim command As New OleDbCommand("UPDATE Table1 SET Hb = '" & Hb & "',TC = '" & TC & "',DC = '" & DC & "' WHERE ID = " & Cint(ID) & "", Connection1)

[Satyam_1]

Table1 contain some numeric and some non-muneric type
Here ID,Hb,TC,DC are numeric and one more column in this table a PS that i didn't mentioned here contains non-muneric type data

[jared.geli]

You can use OLEDB Command Builder. It will generate, Update, Delete, and Insert commands for DataAdapter. Note: You should have a Primary Key in your DB for this to work.

Private Sub Src_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Src.Click

    Dim MyConnection As New OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & path & "';)
    Dim cmd As OleDbCommand = New OleDbCommand("SELECT * from Table1 ", MyConnection)
    MyConnection.Open()
    adpt = New OleDbDataAdapter(cmd)
    Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
    Dim DtSet = New DataSet()
    adpt.Fill(DtSet)
    DataGridView1.DataSource = Nothing
    DataGridView1.DataSource = DtSet.Tables(0).DefaultView
    DataGridView1.Columns(0).Visible = False
    MyConnection.Close()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Try
        Me.Validate()
        Me.adpt.Update(Me.DtSet.Tables(0))
        Me.DtSet.AcceptChanges()
    Catch ex As Exception
        MsgBox("Primary Key cannot be null!")
    End Try

End Sub

[Sulaiman_1]

Could you please confirm the Table1's fields are in the non numeric data types?

Dim command As New OleDbCommand("UPDATE Table1 SET Hb = '" & Hb & "',TC = '" & TC & "',DC = '" & DC & "' WHERE ID = " & Cint(ID) & "", Connection1)

if Hb is numeric type means

SET Hb = " & Hb & "

if Hb is non numeric type means

SET Hb = '" & Hb & "'

the all columns values are to be like this ...
first try to Execute Query using --
Server Explorer--> your Database --> your Database's Table-->(right click) New Query--> type and check the Query is executing manually is right.
then make that as With

Command Object