7 Replies - 1758 Views - Last Post: 08 July 2011 - 12:23 PM Rate Topic: -----

#1 M.RameshKumar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 05-July 11

Error : Syntax Error in INSERT INTO statement.

Posted 07 July 2011 - 10:06 PM

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




Is This A Good Question/Topic? 0
  • +

Replies To: Error : Syntax Error in INSERT INTO statement.

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4333
  • View blog
  • Posts: 12,128
  • Joined: 18-April 07

Re: Error : Syntax Error in INSERT INTO statement.

Posted 07 July 2011 - 10:14 PM

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.

:)
Was This Post Helpful? 0
  • +
  • -

#3 M.RameshKumar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 05-July 11

Re: Error : Syntax Error in INSERT INTO statement.

Posted 07 July 2011 - 10:28 PM

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.

View PostMartyr2, 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.
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4333
  • View blog
  • Posts: 12,128
  • Joined: 18-April 07

Re: Error : Syntax Error in INSERT INTO statement.

Posted 07 July 2011 - 10:33 PM

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.
Was This Post Helpful? 0
  • +
  • -

#5 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: Error : Syntax Error in INSERT INTO statement.

Posted 08 July 2011 - 05:42 AM

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
Was This Post Helpful? 0
  • +
  • -

#6 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2262
  • View blog
  • Posts: 9,464
  • Joined: 29-May 08

Re: Error : Syntax Error in INSERT INTO statement.

Posted 08 July 2011 - 07:32 AM

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?
Was This Post Helpful? 0
  • +
  • -

#7 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: Error : Syntax Error in INSERT INTO statement.

Posted 08 July 2011 - 08:08 AM

Access is finicky with its syntax. Sometimes you need square brackets around table or field names. Especially if there is a space in it.
Was This Post Helpful? 1
  • +
  • -

#8 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Error : Syntax Error in INSERT INTO statement.

Posted 08 July 2011 - 12:23 PM

View PostBtu, 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

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1