Dear sir/Madam I had tried to execute the app many times but still not work it shows the message the syntax error [missing operator] please help me how to resolve this problem
Thanks very much!!
private void btnSave_Click[object sender, EventArgs e] { //purchase table
{ string str = "INSERT INTO Sales [Invoice,CustomerName,SaleDate,TotalAmt,VAT,Discount,TotalPayAmt,Paid,Balance] VALUES ["+ Invoice_tx.Text +", '"+ CustomerName_cb.Text +"', '"+ SaleDate_dt.Value.Date.ToString[] +"', "+ TotalAmnt_tx.Text +", "+ VAT_tx.Text +", "+ Discount_tx.Text +", "+ TotalPayAmnt_tx.Text +", "+ Paid_tx.Text +", "+ Balance_tx.Text +"] ";
OleDbDataAdapter da = new OleDbDataAdapter[str, conn]; //DataSet ds = new DataSet[]; DataTable dt = new DataTable[]; da.Fill[dt];
}
That's an awfully unusual [and messy] way to be calling the SQL though. It's tough to see if you have further issues. Let me know if it doesn't work and we will figure it out together.
Bonus Tip: Debugging VBA SQL Strings
When you get a VBA run-time error on a line that includes [or references] a complicated SQL string, it can be tricky to determine exactly where the problems lies.
As with troubleshooting any code, the process is to simplify, to eliminate possible culprits.
When attempting to execute the following procedure, we will get a run-time error on the line with theOpenRecordSet
method:
Public Function SQLTest[] Dim strSQL As String strSQL = "SELECT * FROM tblMain" & _ "ORDER BY [TableID]" Debug.Print strSQL CurrentDb.OpenRecordset strSQL End Function
To help identify the problem, we can modify the code slightly by adding a single line that will print the value of the SQL string to the Immediate Window.
[Use Ctrl+G to view the immediate window.]
Public Function SQLTest[] Dim strSQL As String strSQL = "SELECT * FROM tblMain" & _ "ORDER BY [TableID]" Debug.Print strSQL CurrentDb.OpenRecordset strSQL End Function
Now, take a look at the output [in the immediate window]:
It's easy to see that we are missing a space between the table name and the
SELECT * FROM tblMainORDER BY [TableID] ↗↖
ORDER BY
clause, which is simple enough to fix:
strSQL = "SELECT * FROM tblMain " & _ "ORDER BY [TableID]"
- This is the possibly the single most helpful debugging technique for troubleshooting SQL statements in VBA.
More Information:
- MSDN : Perform Joins Using Access SQL
- TechNet : SQL Joins
EDIT:
Try this to troubleshoot:
Debug.Print "SELECT * FROM [classes] INNER JOIN [students] ON classes.StudentForename = students.Forename AND classes.StudentSurname = students.Surname AND classes.TeacherName ='" & personloggedon.Text & "' AND classes.Day ='" & System.DateTime.Now.DayOfWeek.ToString & "' AND classes.Period ='" & attendance_reg_periodComboBox.Text & "'"
Stop
Put that code just before the problem line. When the code Stops hit Ctrl+G to view the immediate window.
Copy and Paste the SQL into a new Query Window [in SQL view] and try changing to Design Mode. See if you can identify the error there.
I am trying to modify a form to use a date range instead of a single date.
Here is the original code:
Private Sub cmdprt_Click[] On Error GoTo Err_cmdprt_Click
Dim stDocName As String, stCrit As String
stCrit = "[id] 0"
If Not IsNull[RecStart] And RecStart 0 Then
stCrit = stCrit & " and [id] >= " & [RecStart]
End If
If Not IsNull[RecEnd] And RecEnd 0 Then
stCrit = stCrit & " and [id]