none
Data type mismatch in criteria expression

    Question

  • Hi there,

     

    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(MySQL, Conn)

    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

    Thursday, November 24, 2005 10:30 AM

All replies

  • did you find a solution?
    Tuesday, January 10, 2006 4:42 PM
  • 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= ?"

    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"

    And 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

    hope it helps,

    Ivan Wong

    Wednesday, January 11, 2006 12:02 PM


  • 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 --



    Saturday, November 10, 2007 12:03 AM