Member Avatar for pritesh2010

hello every one when I'm Updating a table in access it gives syntax error in update statement can any one help how to sole this problem

vbstr = "UPDATE EmpMaster SET Name= '" & UCase(txtname.Text) & "',Address='" & Trim(UCase(txtadd.Text)) & "',TeleNo='" & txttel.Text & "',MobileNo='" & txtmob.Text & "',E-mail='" & Trim(txtemail.Text) & "',ARight='" & Trim(cmbrht.SelectedItem.ToString()) & "',Post='" & txtpst.Text & "',JoiningDate='" & Trim(txtdate.Text) & "',Salary='" & Trim(txtsal.Text) & "', Comission='" & Trim(txtcom.Text) & "' WHERE EmployeeNO = " & no.ToString & " "
con.connectionstring=""
con.open()
cmd=new Oldbcommand(vbstr,con)
cmd.ExecuteNonQuery()

please help me mail it to ...

Edited by kvprajapati because: Email Snipped.
  • 2 Contributors
  • 1 Reply
  • 241 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by kvprajapati

Dani AI

Generated

Syntax errors in Access UPDATE statements most often come from a small set of causes: unescaped or illegal column names, mismatched quotes from string concatenation, data-type mismatches (numbers/dates quoted as text), stray commas or punctuation, or a malformed WHERE clause. In 's original code the SQL is built by concatenation, which is fragile; was correct to flag identifier escaping as one fix when column names contain special characters or conflict with reserved words.

Quick checklist to find the exact cause:

  • Dump the final SQL string to the debug window (Debug.Print) and paste it into Access — Access will point to the error location.
  • Watch for embedded apostrophes in text (O'Connor). Concatenation will break unless quotes are escaped; parameters avoid this entirely.
  • Ensure numeric columns are not sent as quoted text; convert values to numeric types before sending.
  • Treat dates as dates. Prefer parameters; if a literal must be used, Jet/ACE uses #date# syntax.
  • Scan for a trailing comma before WHERE or any missing closing quote.
  • Confirm no typos in object/class names or an empty connection string (these can cause other failures that look like SQL problems).

A better approach is a parameterized UPDATE inside Using blocks so types, quoting, and disposal are handled safely. Note: OleDb uses positional parameters (?), so parameters must be added in the same order they appear in the SQL.

Dim sql As String = "UPDATE EmpMaster SET [Name]=?, [Address]=?, [TeleNo]=?, [MobileNo]=?, [E-mail]=?, [ARight]=?, [Post]=?, [JoiningDate]=?, [Salary]=?, [Comission]=? WHERE [EmployeeNO]=?"
Using cn As New OleDbConnection(connString)
  Using cmd As New OleDbCommand(sql, cn)
    cmd.Parameters.AddWithValue("p1", UCase(txtName.Text))
    ' ... add remaining parameters in order ...
    cn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

Best practices: rename columns to remove hyphens/reserved words where possible, enforce correct column types in Access, validate and convert inputs in code, and use parameters to eliminate most syntax and injection issues.

Member Avatar for kvprajapati

Please read the rules before posting - http://www.daniweb.com/forums/thread78223.html

vbstr = "UPDATE [EmpMaster] SET [Name]= '" & UCase(txtname.Text) & "',[Address]='" & Trim(UCase(txtadd.Text)) & "',[TeleNo]='" & txttel.Text & "',[MobileNo]='" & txtmob.Text & "',[E-mail]='" & Trim(txtemail.Text) & "',[ARight]='" & Trim(cmbrht.SelectedItem.ToString()) & "',[Post]='" & txtpst.Text & "',[JoiningDate]='" & Trim(txtdate.Text) & "',[Salary]=" & Trim(txtsal.Text) & ", [Comission]=" & Trim(txtcom.Text) & " WHERE [EmployeeNO]= " & no.ToString 

con.connectionstring=""
con.open()
cmd=new Oldbcommand(vbstr,con)
cmd.ExecuteNonQuery()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.