[BeginDeveloper]

Hi Guys,

I am try to create windows form Application in visual basic 2008 Express edition. Now I have create a form for register the candidate information and stored into access database. I have ms access 2010 installed on my machine.

Now When I run the form and click on register it come with error saying oledbexception was unhandled syntax error in insert into statement.

I tried and check my connection string and also tried to use only two text boxes instead of 11. but it still generating same error. I also tried to debug my code line by line and when it comes to code where cmd.ExecuteNonQuery() after Insert into statement it keep generating error.

Now I am getting confused about the error that is it a syntax error into insert statement or database connection error.


Here I paste my code

 Imports System.Data.OleDb
Public Class Candidate_Resgistration_Form
    Dim con As New OleDb.OleDbConnection

    Private Sub Candidate_Resgistration_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con = New OleDb.OleDbConnection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\sa\Documents\Visual Studio 2008\Projects\Capital Group\Capital Group\Registration Info.accdb"
        con.Open()
    End Sub
Private Sub btnregister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnregister.Click
        Dim cmd As New OleDb.OleDbCommand
        If Not con.State = ConnectionState.Open Then
            con.Open()
        End If

        cmd.Connection = con
        If Me.txtid.Tag & "" = "" Then
            cmd.CommandText = "INSERT INTO Candidate Info (ID, Candidate Name, DOB, Place of Birth, Full Address, Email Address , Contact Number, Course Title, Course Start Date, Course End Date, Instructor) " & _
                            " VALUES(" & Me.txtid.Text & ",'" & Me.txtname.Text & "','" & _
                              Me.txtdob.Text & "','" & Me.txtpob.Text & "', '" & _
                              Me.RTB_Address.Text & "', '" & Me.txtemailadd.Text & "', '" & _
                              Me.txtcontactnumber.Text & "', '" & Me.txtcoursetitle.Text & "', '" & _
                              Me.Start_date.Text & "', '" & Me.End_date.Text & "', '" & _
                              Me.txtinstructorname.Text & "')"
            cmd.ExecuteNonQuery()
        Else
            cmd.CommandText = "UPDATE Candidate Info " & _
                        "  SET ID=" & Me.txtid.Text & _
                        ", Candidate Name='" & Me.txtname.Text & "'" & _
                        ", DOB='" & Me.txtdob.Text & "'" & _
                        ", Place of birth='" & Me.txtpob.Text & "'" & _
                        ", Full Address='" & Me.RTB_Address.Text & "'" & _
                        "' Email Adderss='" & Me.txtemailadd.Text & "'" & _
                        "' Contact Number= '" & Me.txtcontactnumber.Text & "'" & _
                        "' Course Title= '" & Me.txtcoursetitle.Text & "'" & _
                        "' Course Start Date= '" & Me.Start_date.Text & "'" & _
                        "' Course End Date= '" & Me.End_date.Text & "' " & _
                        "' Instructor= '" & Me.txtinstructorname.Text & " ' " & _
                        " WHERE ID=" & Me.txtid.Tag
            cmd.ExecuteNonQuery()
        End If
        Me.txtid.Text = ""
        Me.txtname.Text = ""
        Me.txtdob.Text = ""
        Me.txtpob.Text = ""
        Me.RTB_Address.Text = ""
        Me.txtemailadd.Text = ""
        Me.txtcontactnumber.Text = ""
        Me.txtcoursetitle.Text = ""
        Me.Start_date.Text = ""
        Me.End_date.Text = ""
        Me.txtinstructorname.Text = ""
        con.Close()
    End Sub
End Class 

[_HAWK_]

Database table names and field names should not have spaces in them - use underscore instead.

Also parameterize your queries.

This post has been edited by _HAWK_: 11 July 2012 - 07:47 AM

[jgferguson]

Hi BeginDeveloper,

As _Hawk_ says table and field names should not have spaces in them.

However assuming the job of re-naming all the tables and fields is too big for now try using square brackets around each object name:-

 
cmd.CommandText = "UPDATE [Candidate Info] " & _  
"  SET ID=" & Me.txtid.Text & _  
", [Candidate Name] ='" & Me.txtname.Text & "'" & _  
", DOB='" & Me.txtdob.Text & "'" & _  
", [Place of birth]='" & Me.txtpob.Text & "'"

You will need to put every table or field name which contains a space within the square brackets. If there is no space (as in the ID and DOB fields) you don't need the square brackets.

If you were to create the query in MS Access Query Editor and view the SQL string, Access would put the square brackets in for you.

Hope this helps,
Jerry.

[BeginDeveloper]

Thanks Hawk and Jerry for reply I just Changed Insert statement but its give me an error saying syntax error in query Expression.
Syntax error (missing operator) in query expression '', [Full Address] = ' ', [Email Address] = '', ''.

cmd.CommandText = "INSERT INTO Candidate_Info (ID, [Candidate Name], DOB, [Place of Birth], [Full Address], [Email Address] , [Contact Number], [Course Title], [Course Start Date], [Course End Date], Instructor) " & _
                            " VALUES(' ID =" & Me.txtid.Text & ",[Candidate Name] ='" & Me.txtname.Text & "','" & _
                             ", DOB = '" & Me.txtdob.Text & "',[Place of Birth] ='" & Me.txtpob.Text & "', '" & _
                             ", [Full Address] = '" & Me.RTB_Address.Text & " ', [Email Address] = '" & Me.txtemailadd.Text & "', '" & _
                             ", [Contact Number] = '" & Me.txtcontactnumber.Text & "', [Course Title]='" & Me.txtcoursetitle.Text & "', '" & _
                              ", [Course Start Date]= '" & Me.Start_date.Text & " ', [Course End Date] = '" & Me.End_date.Text & "', '" & _
                              ", Instructor = '" & Me.txtinstructorname.Text & " ')"
            cmd.ExecuteNonQuery()
        Else
            cmd.CommandText = "UPDATE Candidate_Info " & _
                        "  SET ID=" & Me.txtid.Text & _
                        ", [Candidate Name]='" & Me.txtname.Text & "'" & _
                        ", DOB='" & Me.txtdob.Text & "'" & _
                        ", [Place of birth]='" & Me.txtpob.Text & "'" & _
                        ", [Full Address]='" & Me.RTB_Address.Text & "'" & _
                        "' [Email Adderss]='" & Me.txtemailadd.Text & "'" & _
                        "' [Contact Number]= '" & Me.txtcontactnumber.Text & "'" & _
                        "' [Course Title]= '" & Me.txtcoursetitle.Text & "'" & _
                        "' [Course Start Date]= '" & Me.Start_date.Text & "'" & _
                        "' [Course End Date]= '" & Me.End_date.Text & "' " & _
                        "' Instructor= '" & Me.txtinstructorname.Text & " ' " & _
                        " WHERE ID=" & Me.txtid.Tag

[Ionut]

This thing DOB = doesn't have to appear in an insert statement.
A correct Insert statement is

INSERT INTO TableName (Column1, Column2, Column3 ) VALUES (ValueForColumn1, ValueForColumn2, ValueForColumn3)

With the following note: datetime values and string values(varchar) have to be enclosed between apostrophes.

This post has been edited by Ionut: 11 July 2012 - 09:51 AM

[BeginDeveloper]

Thanks to everyone for their suggestion & help. It's solve my problem and code is working fine.
Thank you very much!