Member Avatar for lendyl16

Call Open_conn
If BEdt = False Then
Rs.Open "Select * from tblpayroll where EmployeeID='" + Trim(txtEmpID.Text) + " '", Cn
If Rs.EOF = False Then
MsgBox "Duplicate Employee ID", vbInformation, "Duplicate"
Else
Cn.Execute "Insert into tblpayroll values('" + Trim(txtEmpID.Text) + "', '" _
+ Trim(txtfullname.Text) + "','" + Trim(cbogender.Text) + "','" _
+ Trim(cbostatus.Text) + "','" + Trim(txtposition.Text) + "')'" _
+ Trim(txtBs.Text) + "','" + Trim(cboEStatus.Text) + "','" _
+ Trim(txtAdd.Text) + "','" + Trim(txtContact.Text) + "','" _
+ Trim(txtSSS.Text) + "','" + Trim(txtTin.Text) + "','" _
+ Trim(txtphil.Text) + "')"";"
MsgBox "New Record Saved!", vbInformation, "Employee Info"
End If

Close_Conn

end sub

what should i do?
tnx in advance....

  • 3 Contributors
  • 2 Replies
  • 178 Views
  • 5 Months Discussion Span
  • Latest Post Latest Post by mustaffa hasan

Dani AI

Generated

The problem in the snippets is not a missing semicolon — it’s broken string construction: mismatched quotes, stray quote characters before/after the VB line-continuation, and concatenation split in the wrong places. ’s original shows the symptom, pointed at the SELECT line, and ’s reply highlights the line-continuation confusion. In VB/ADO the final SQL must be one valid string; any stray quote, extra double-quote or misplaced underscore will produce the syntax error seen here.

Key fixes and a concise example:

  • Build the SQL in a single String variable (use & for concatenation in VB rather than +).
  • List INSERT column names so values match columns explicitly.
  • Escape single quotes inside text fields (Replace(..., "'", "''")).
  • Do not wrap numeric columns in quotes.
  • Confirm the line-continuation syntax: a space then underscore (_) at the end of the line, not inside a quoted literal.
  • For production, prefer parameterized ADODB.Command to avoid injection and quoting headaches.

A corrected-build example (VB-style) follows — it avoids the quote/underscore problem and shows escaping:

Dim sql As String

sql = "INSERT INTO tblpayroll (EmployeeID, FullName, Gender, Status, Position, BasicSalary, EStatus, Address, Contact, SSS, TIN, PhilHealth) VALUES (" _
  & "'" & Replace(Trim(txtEmpID.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(txtfullname.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(cbogender.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(cbostatus.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(txtposition.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(txtBs.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(cboEStatus.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(txtAdd.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(txtContact.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(txtSSS.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(txtTin.Text), "'", "''") & "', " _
  & "'" & Replace(Trim(txtphil.Text), "'", "''") & "')"

Cn.Execute sql

Troubleshooting checklist: Debug.Print or MsgBox the sql string to inspect the final statement; ensure columns that are numeric are not quoted; consider adding a UNIQUE constraint on EmployeeID and handling the INSERT error instead of a pre-SELECT.

Recommended Answers

All 2 Replies

Member Avatar for StephNicolaou

Exactly what the title says...

Look closely at line 3.

Rs.Open "Select * from tblpayroll where EmployeeID= '"+ Trim(txtEmpID.Text) +"' ", Cn
Member Avatar for mustaffa hasan

Call Open_conn
If BEdt = False Then
Rs.Open "Select * from tblpayroll where EmployeeID='" + Trim(txtEmpID.Text) + " '", Cn
If Rs.EOF = False Then
MsgBox "Duplicate Employee ID", vbInformation, "Duplicate"
Else
Cn.Execute "Insert into tblpayroll values('" + Trim(txtEmpID.Text) + "', '" _
+ Trim(txtfullname.Text) + "','" + Trim(cbogender.Text) + "','" _
+ Trim(cbostatus.Text) + "','" + Trim(txtposition.Text) + "')'" _
+ Trim(txtBs.Text) + "','" + Trim(cboEStatus.Text) + "','" _
+ Trim(txtAdd.Text) + "','" + Trim(txtContact.Text) + "','" _

  • Trim(txtSSS.Text) + "','" + Trim(txtTin.Text) + **"*';"****Bold Text Here
  • _" we get just change lightly and see
    MsgBox "New Record Saved!", vbInformation, "Employee Info"
    End If
Edited by mustaffa hasan
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.