Hi there,< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
I am trying to edit and update a chosen database record, the edit part is working properly but the update isn’t. I get the following error: “OleDbException (0x80040e07): Data type mismatch in criteria expression. System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior). All fields In my database are type Text. Should my update statement include everything in the database even if I am not read everything
Help will be appreciated.
Regards.
Public intID As String
Public strFirst, strName, strTeam, strFloor, strTel As String
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
BindData()
End If
End Sub
Public Sub BindData()
If Not Page.IsPostBack Then
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _"c:\DataMe\Telecom.mdb"
Dim MySQL As String = "Select Employee_No, Floor_No, Dept, Name, First_Name, Tel_No from Employee order by Name"
Dim MyConn As New OleDbConnection(strConn)
Dim Cmd As New OleDbCommand(MySQL, MyConn)
Dim objDR As OleDbDataReader
MyConn.Open()
objDR = Cmd.ExecuteReaderSystem.Data.CommandBehavior.CloseConnection)
NameDropDownList.DataSource = objDR
NameDropDownList.DataTextField = "Name"
NameDropDownList.DataValueField = "Employee_No"
NameDropDownList.DataBind()
NameDropDownList.SelectedIndex = 0
MyConn.Close()
End If
End Sub
Private Sub editRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles editRecord.Click
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataMe\TELECOM.mdb;"
Dim sql As String = "Select Employee_No, Floor_No, Dept, Name, First_Name, Tel_No from Employee Where Employee_No = " & NameDropDownList.SelectedItem.Value
Dim conn As New OleDbConnection(strConn)
Dim objDR As OleDbDataReader
Dim Cmd As New OleDbCommand(sql, conn)
conn.Open()
objDR = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
While objDR.Read()
intID = objDR("Employee_No")
strFloor = objDR("Floor_No")
strTeam = objDR("Dept")
strName = objDR("Name")
strFirst = objDR("First_Name")
strTel = objDR("Tel_No")
End While
Page.DataBind()
Label8.Text = ""
End Sub
Private Sub doUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles doUpdate.Click
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataMe\TELECOM.mdb;"
Dim MySQL As String = "Update Employee Set First_Name=@First_Name, Floor_No=@Floor_No, Tel_No=@Tel_No, Dept=@Dept, Name=@Name Where Employee_No= @Employee_No"
Dim Conn As New OleDbConnection(strConn)
Dim Cmd As New OleDbCommand(< xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />
With Cmd.Parameters
'.Add(New OleDbParameter("@Employee_No", lblID.Text))
.Add(New OleDbParameter("@First_Name", txtFName.Text))
.Add(New OleDbParameter("@Floor_No", txtFloor.Text))
.Add(New OleDbParameter("@Tel_No", txtTel.Text))
.Add(New OleDbParameter("@Dept", txtTeam.Text))
.Add(New OleDbParameter("@Name", txtName.Text))
End With
Conn.Open()
Cmd.ExecuteNonQuery()
Conn.Close()
Label8.Text = "Successfully updated -- - "
NameDropDownList.SelectedIndex = NameDropDownList.Items.IndexOf(NameDropDownList.Items.FindByValue(lblID.Text))
BindData()
End Sub
Data type mismatch in criteria expression
Alexx4
I could see two mistakes you have made in your code,
First of all, when you specify the name of your parameter, you shouldn't use the "@" sign since you are using OleDb not Sql
try this:
New OleDbParameter("First_Name", txtFName.Text)
instead of this:
New OleDbParameter("@First_Name", txtFName.Text)
Secondly, in the query you use " " to specify where to put your parameter
try this:
Dim MySQL As String = "Update Employee Set First_Name= , Floor_No= , Tel_No= , Dept= , Name= Where Employee_No= "< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
instead of this:
Dim MySQL As String = "Update Employee Set First_Name=@First_Name, Floor_No=@Floor_No, Tel_No=@Tel_No, Dept=@Dept, Name=@Name Where Employee_No= @Employee_No"
silvarea
M Ram
The operative part of this advice is:
... you have to add your parameter in the order that you put the parameter in, in this case, the order will be First_name, Floor_no, Tel_no, Dept, Name, Employee_no
I just spent two hours tracking down this obscurity. Makes supporting
parameters by name pretty pointless, really.
Thanks Ian for pointing me in the right direction!
And thanks also to MS for that really meaningful and accurate error message
-- Mike --