Data Type Mismatch In criteria expression

[ekonagu]

helo i am developing an vb.net access application.i am frustrated with this access that i am getting error while updating records.

Data Type Mismatch In criteria expression

here is the code , if i run the sql statement its works fine .but thru vb.net its giving error.i am not understadning the problem.pls help

sql = "update Tbl_Pat_Registration set Pat_name='" & Trim(txt_name.Text) & "',pat_id='" & Trim(Txt_id.Text) & "' where id='" & id & "'"
cmd.CommandText = Sql
cmd.Connection = CON

cmd.ExecuteNonQuery()
MsgBox("Record Updated Successfully")

[kvprajapati]

Verify data type of fields.

sql = "update Tbl_Pat_Registration set Pat_name='" & Trim(txt_name.Text) & "', pat_id='" & Trim(Txt_id.Text) & "' where id='" & id & "'"

Note: Non-numeric fields (date, text) must be surrounded with single quotes.

Use Parameterised query instead of forming sql string.

cmd.CommandText="Update TABLE set Col1=@p1,Col2=@p2 where Col3=@p3"

Read http://msdn.microsoft.com/en-us/library/system.data.common.dbparameter.aspx

[ekonagu]

i tired this before.i am getting tht records updated sucesfully.but its actually not updating.see the code pls

COMND.CommandText = "UPDATE Tbl_Pat_Registration SET Pat_Name=@PPat_Name,Pat_ID=@PPat_ID where id=@Pid"

.AddWithValue("@Pid", Id)
.AddWithValue("@PPat_Name", Trim(CChar(txt_name.Text)))
.AddWithValue("@PPat_ID", Trim(CChar(Txt_id.Text)))

End With
If ExecuteQuery(COMND) > 0 Then MsgBox("Updated successfully", MsgBoxStyle.Information)

execute query functionis

Public Function ExecuteQuery(ByVal MyCommand As OleDbCommand) As Integer
' Dim ExecResult As Integer
Dim rObj As New Integer
Try
MyCommand.CommandType = CommandType.Text
' MyCommand.Parameters(0).Direction = ParameterDirection.Output
' ExecResult = MyCommand.ExecuteNonQuery
MyCommand.ExecuteNonQuery()
' rObj = MyCommand.Parameters(0).Value
'Return ExecResult
Return 1
Catch ex As Exception
MsgBox(ex.Message)
Return -1
Finally
MyCommand.Parameters.Clear()
End Try
End Function

pls help.now no error is cmg.but records are not updating

[khinemyohtun]

in your SQL statement , if criteria field is Numeric data type no need to use '" & id & "'
please try this SQL statement

sql = "update Tbl_Pat_Registration set Pat_name='" & Trim(txt_name.Text) & "', pat_id='" & Trim(Txt_id.Text) & "' where id=" & id

[TomW]

COMND.CommandText = "UPDATE  Tbl_Pat_Registration SET Pat_Name=@PPat_Name,Pat_ID=@PPat_ID where id=@Pid"

.AddWithValue("@Pid", Id)
            .AddWithValue("@PPat_Name", Trim(CChar(txt_name.Text)))
            .AddWithValue("@PPat_ID", Trim(CChar(Txt_id.Text)))

Is Id a numeric datatype,why are you converting it to a char?

.AddWithValue("@PPat_ID", cInt(Txt_id.Text.Trim))