VB.NET

Syntax error in SQL update string

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: May 2009

Posts: 8

Reputation: nanoh2001 is an unknown quantity at this point

Solved Threads: 0

nanoh2001

Offline

Newbie Poster

Syntax error in SQL update string

Jun 9th, 2009

Hi all

Sorry- The title was wrong, My problem is when I type a the ID number in the text box for updating the record, it gives me an error "Data type mismatch in criteria expression."

Could anyone help me please in solving this problem?

This is my update code.

 Help with Code Tags 
 VB.NET Syntax (Toggle Plain Text) 
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HRIS.mdb;")
        cn.Open()
        Dim strupdate As String
 
        strupdate = "update Employees set [Name] ='" & txtName.Text & "',Mobile ='" & txtMob.Text & "',Home_Phn ='" & txtHomePhn.Text & "',Date_Birth ='" & txtAge.Text & "',CPR='" & txtCPR.Text & _
        "', Address='" & txtAdd.Text & "',email='" & txtEmail.Text & "',Date_hiring='" & txtWorkDate.Text & "',Department='" & txtDep.Text & "',basic_salary='" & txtSal.Text & "'where ID='" & txtSearch.Text & "'"
 
        cmd = New OleDbCommand(strupdate, cn)
        cmd.ExecuteNonQuery()
        cn.Close()
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HRIS.mdb;")
        cn.Open()
        Dim strupdate As String

        strupdate = "update Employees set [Name] ='" & txtName.Text & "',Mobile ='" & txtMob.Text & "',Home_Phn ='" & txtHomePhn.Text & "',Date_Birth ='" & txtAge.Text & "',CPR='" & txtCPR.Text & _
        "', Address='" & txtAdd.Text & "',email='" & txtEmail.Text & "',Date_hiring='" & txtWorkDate.Text & "',Department='" & txtDep.Text & "',basic_salary='" & txtSal.Text & "'where ID='" & txtSearch.Text & "'"

        cmd = New OleDbCommand(strupdate, cn)
        cmd.ExecuteNonQuery()
        cn.Close()

Thanks in Advance

Last edited by nanoh2001; Jun 9th, 2009 at 3:18 pm. Reason: wrong title and case

•

Join Date: May 2009

Posts: 12

Reputation: Vandamn is an unknown quantity at this point

Solved Threads: 0

Vandamn

Offline

Newbie Poster

Re: Syntax error in SQL update string

Jun 9th, 2009

Hi nanoh2001,

I believe your problem is the fact that you are trying to update all fields in your table with string values. If the fields in the table are not all strings then you will get the error you are geting.

I notice that there is a date field you are updating. If this field is a date field and the value you are supplying is not in a valid date format, then the error you are gettting will be generated.

AndyM
asmoody@talktalk.net

•

Join Date: May 2009

Posts: 8

Reputation: nanoh2001 is an unknown quantity at this point

Solved Threads: 0

nanoh2001

Offline

Newbie Poster

Re: Syntax error in SQL update string

Jun 9th, 2009

Dear Vandamn,

Thanks for your fast valuable response. I'm sure that I put valid date entries in both birthdate and hiring date,

Yes you are right, I think vb considers any input from textboxes as string, but what can I do?

How can I solve this problem? I tried to convert the "ID" to intiger in "Delete record code form" using Cint function but it doesn't work

it gave me the same error,

please help

•

Join Date: May 2009

Posts: 12

Reputation: Vandamn is an unknown quantity at this point

Solved Threads: 0

Vandamn

Offline

Newbie Poster

Re: Syntax error in SQL update string

Jun 9th, 2009

Does the ID contain characters as well as numbers, or invalid "white space" characters such as <space>.

If not can you post the code in cluding the Cint function.

AndyM
asmoody@talktalk.net