[M.RameshKumar]

Hi,

Am getting the below error at run-time of executing query in VB.Net. Please give the solution for this

Error : "Syntax Error in INSERT INTO statement"

My code is given below

'Register User Form
Imports System.Data.OleDb
Public Class RegisterUser
       ‘ Variables Decleration
       Public Con As OleDbConnection
       Public Cmd As OleDbCommand
       Dim Emailid As Object
'TextBox(EmployeeID.Click)
	TBEmpID.Text = Trim(TBEmpID.Text)
End Sub
'TextBox(EmailName.Click)
	TBEmailName.Text = Trim(TBEmailName.Text)
End Sub
'Button(BClear.Click)
	TBEmpID.Text.Clear()
       TBEmpName.Text.Clear()
       TBEmailName.Text.Clear()
       TBPass.Text.Clear()
       TBCPass.Text.Clear()
       CBDomain.Text.Clear()
End Sub
'WindowControlButton(Help.Click) (?)
	HelpForm.Show()
End Sub


'Button(BRegister.Click)
Try
       Emailid = TBEmailName.Text & “” & CBDomain.Text
       If(TBEmpID.Text = String.Empty or TBEmpName.Text = String.Empty or TBEmailName.Text = String.Empty or TBPass.Text = 

String.Empty or TBCPass.Text = String.Empty) then
       MsgBox(“You Have miss to fill some fields”, MessageBox.Style = Exclamation)
       Else
		If(CBDomain.Text = String.Empty) then
			MsgBox(“Kindly Select the domain of your email”, MessageBox.Style = Exclamation)
       Else
       		If(TBPass.Text = TBCPass.Text)then
       		Con = New OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=DataBase\Dashbard.accdb; Persist 

Security Info = False”)
       		Con.Open()
       		Cmd = New OleDbCommand(“INSERT INTO EmpDatas(EmpID,Name,EmailID,Password) VALUES(‘” & TBEmpID.Text & 

”’,’” & TBEmpName.Text & ”’ ,’” & Emailid & ”’ ,’” & TBPass.Text & ”’)”, Con)
       		Cmd.ExecuteNonQuery()
       MsgBox(“Data Saved Successfully”, MessageBox.Style= Information)
       Con.Close()
			Else
			MsgBox(“Password and ConfirmPassword is not matched. Please try again”, 

MessageBox.Style=Exclamation)
			TBPass.Text.Clear()
       TBCPass.Text.Clear()
			End If
       End If
       End If
Catch ex As Exception
       MsgBox(ex.Message)
End Try
EntryForm.Show()
Me.Close
End Sub
End Class

[Martyr2]

Be sure to put a space between the table name and the opening parenthesis. It should be EmpDatas (EmpID and not EmpDatas(EmpID. Then make sure each of your values are being put into the query properly.

Just a tip: You should never put user input directly into your query like you are. This opens you up to SQL Injection attacks. Store the values, clean them and either test them before using them in the query or use a parameterized query.

[M.RameshKumar]

Hi Martyr2,

I have try your solution but again am getting the same error.
Kindly give another solution.

Note :

My Connection string is given below

Con = New OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=DataBase\Dashbard.accdb; Persist Security Info = False”)

Kindly make sure ny connection string is correct or not? and also am using MS Access 2007 database as backend.

Martyr2, on 08 July 2011 - 10:44 AM, said:

Be sure to put a space between the table name and the opening parenthesis. It should be EmpDatas (EmpID and not EmpDatas(EmpID. Then make sure each of your values are being put into the query properly.

Just a tip: You should never put user input directly into your query like you are. This opens you up to SQL Injection attacks. Store the values, clean them and either test them before using them in the query or use a parameterized query.

Hi Martyr2,

I have try your solution but again am getting the same error.
Kindly give another solution.

Note :

My Connection string is given below

Con = New OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=DataBase\Dashbard.accdb; Persist Security Info = False”)
Kindly make sure ny connection string is correct or not? and also am using MS Access 2007 database as backend.

[Martyr2]

If you are getting a syntax error about the query, it has nothing to do with your connection string. It is the query. Try putting a break point on the line with the query and see if all the values you are supplying are valid. Did you spell everything correctly? Make sure the values you supply are of the same type as defined in the database.

[fixo]

I would use in Try Catch:

Catch oex As OleDb.Exception  
        MsgBox(oex.Message & vbLf & oex.StackTrace)  

Catch ex As System.Exception  
        MsgBox(ex.Message & vbLf & ex.StackTrace)  
 End Try 

Easy to find a problem line, just a hint

[AdamSpeight2008]

Problem is in the command statement.

Cmd = New OleDbCommand(“INSERT INTO EmpDatas(EmpID,Name,EmailID,Password) VALUES(‘” & TBEmpID.Text &”’,’” & TBEmpName.Text & ”’ ,’” & Emailid & ”’ ,’” & TBPass.Text & ”’)”, Con)

Three questions to find out are.
0. Is the statement correctly formed?
1. Do have all the values?
2. Do they match types required?

[Btu]

Access is finicky with its syntax. Sometimes you need square brackets around table or field names. Especially if there is a space in it.

[CharlieMay]

Btu, on 08 July 2011 - 11:08 AM, said:

Access is finicky with its syntax. Sometimes you need square brackets around table or field names. Especially if there is a space in it.

Name is a reserved word. You either need to enclose it with square brackets or rename the field to something like FullName.

INSERT INTO EmpDatas(EmpID,[Name],EmailID,Password) VALUES ...

This post has been edited by CharlieMay: 08 July 2011 - 12:24 PM