Update Query Error

Gagan 20

Hi all... When I execute update query, I get error "data type mismatch in criteria expression" I used following code :

Try
DBConnection.connect() 'code to open database.
Dim sql As String = "update MortgageDetails set [Borrower_Name]='" & txtBorrowerName.Text & "', [Caste]='" & txtCaste.Text & "', " & _
"[Area]='" & txtArea.Text & "', [Address]='" & txtAddress.Text & "', [Product_Detail]='" & txtProductDetail.Text & "', " & _
"[Current_Date]='" & txtCurrentDate.Text & "',[Current_Value]=" & txtProductValue.Text & "," & _
"[Amount_Borrowed]=" & txtAmountBorrowed.Text & " where [S_No]=" & txtSNo.Text

        Dim cmd As New OleDbCommand(sql, con)
        cmd.ExecuteNonQuery()
        MsgBox("Record Updated!", MsgBoxStyle.Information)
        Me.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

I am using VB2008 and MS Access 2003 as database. Suggest me what to do. Thanks. Gagan

Simon_Whale

Sounds like you are passing some data to the table as text when it should be numeric or some other non string format but without seeing your table definition its hard to say something else.

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Luc Pattyn

Passing literal data like that always is asking for trouble. The exact problem is most likely the date you have there. Passing dates and other non-string types depends on the exact database you use, and is often problematic. Suggestion: 1. use programming variables, and TryParse() your textual input before passing the info to the database; this traps input errors and avoids SQL injection; 2. use SqlParameter and pass the above variables; don't use literals in SQL. In will take a bit more code, but the result will be much more robust. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Lost User

Seems the problems is with this piece of code:

"[Current_Date]='" & txtCurrentDate.Text & "'"

Access expects dates to be passed in a specific format, try this:

"[Current_Date]=#" & Convert.ToDateTime(txtCurrentDate.Text).ToString("MM/DD/YYYY") & "#"

Gagan 20

Thanks for your answer. It worked.

Lost User

You're welcome. Please mark it as answer so your post doesn't appear unanswered.