UPDATE error (Data type mismatch in criteria expression.)

Data type mismatch in criteria expression.

Page 1 of 1

1 Replies - 10901 Views - Last Post: 19 March 2008 - 06:38 AM Rate Topic: -----

#1 jleen  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-March 08

UPDATE error (Data type mismatch in criteria expression.)

Post icon  Posted 18 March 2008 - 11:31 PM

i have come up with an error showing :
Data type mismatch in criteria expression. <---error

this is how my code looks like
please help me on this one :blink:

Protected Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

		Dim strUpdate As String = "UPDATE Customization SET Customer_Consultant = '" & ddlCusConsul.SelectedValue.ToString & _
		"',Customer_Name = '" & ddlCusName.SelectedValue.ToString & "',Customer_PO_Number = '" & txtCusPONum.Text & _
		"',Delivered_Date = '" & txtDeliverDate.Text & "',Trial_Start_Date = '" & txtTrialStart.Text & _
		"',Trial_End_Date = '" & txtTrialEnd.Text & "',Payment_Term_1 = '" & txtPaymTerm1.Text & _
		"',Payment_Term_2 = '" & txtPaymTerm2.Text & "',Payment_Term_3 = '" & txtPaymTerm3.Text & _
		"' WHERE ID = '" & lblID.Text & "'"

		cn.Open()
		cmd = New OleDbCommand(strUpdate, cn)
		cmd.ExecuteNonQuery()  <--- error highlighted here
		cn.Close()
	End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: UPDATE error (Data type mismatch in criteria expression.)

#2 orcasquall  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 12
  • View blog
  • Posts: 158
  • Joined: 14-September 07

Re: UPDATE error (Data type mismatch in criteria expression.)

Posted 19 March 2008 - 06:38 AM

Have you checked the column data types for those used in the update statement? Numeric columns don't need the single quotes like so
"Payment_Term_1 = " & txtPaymTerm1.Text


assuming your "Payment_Term_n" fields are ints or other numeric types.

The obvious one would your "ID" column. If it's a numeric field like int, then the last part of the update statement should be
"' WHERE ID = " & lblID.Text


No single quotes needed.

If the column is a datetime, then you might want to do explicit conversion, like so (assuming txtTrialEnd.Text is in YYYYMMDD format)
"Trial_End_Date = convert(datetime,'" & txtTrialEnd.Text & "',112)"


assuming your Delivered_Date, Trial_Start_Date and Trial_End_Date are datetime fields.
A list of date formats can be found here
http://msdn2.microso...y/ms187928.aspx
Sybase uses similar datetime format numbers.

If you're using Oracle, then try
"Trial_End_Date = to_date('" & txtTrialEnd.Text & "', 'YYYYMMDD')"


A list of the format strings can be found here
http://www.oradev.co...date_format.jsp

It's highly recommended that you use parameters instead of concatenating the values. But that's your choice.

Hope that helps.
Was This Post Helpful? 1

Page 1 of 1