0

I am trying to insert data from VB.NET to multiple tables in MS Access but I am getting "Missing semicolon (;) at end of SQL statement." from MessageBox.Show(ex.Message). However, once I added a ; I'm getting this error: "Characters found after end of SQL statement."

Where am I doing wrong? Is anyone free to take a look at my code and help me to fix my problem? Also, if you guys have better alternative way of coding this I will highly appreciate it. Thanks in advance.

Imports System.Data.OleDb
Public Class addNew
Dim conn As New OleDbConnection()
Dim sqlCom As New OleDbCommand()

Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
If txtFirstName.Text = "" Or txtLastName.Text = "" Or txtContact.Text = "" Or txtEmail.Text = "" Or comboMembershipType.Text = "" Then
    MessageBox.Show("Please complete the required fields.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
    Try
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Application.StartupPath & "\igcDatabase.accdb"
        sqlCom.Connection = conn
        conn.Open()

        sqlCom.CommandText = "INSERT INTO [Members] ([Member First Name], [Member Last Name], [Gender], [Contact], [Email])" _
            & " VALUES (@FirstName, @LastName, @Gender, @Contact, @Email)"
        sqlCom.CommandText &= "INSERT INTO [Membership Types] ([Membership Type])" & "VALUES (@MembershipType)"


        Dim gender As String
        If rbtnMale.Checked = True Then
            gender = "Male"
        Else
            gender = "Female"
        End If

            sqlCom.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
            sqlCom.Parameters.AddWithValue("@LastName", txtLastName.Text)
            sqlCom.Parameters.AddWithValue("@Gender", gender)
            sqlCom.Parameters.AddWithValue("@Contact", txtContact.Text)
            sqlCom.Parameters.AddWithValue("@Email", txtEmail.Text)
            sqlCom.Parameters.AddWithValue("@MembershipType", comboMembershipType.Text)

        sqlCom.ExecuteNonQuery()
        MessageBox.Show("Successfully added member!")
        conn.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End If
End Sub
End Class

Updated working code:

Imports System.Data.OleDb
Public Class addNew
Dim conn As New OleDbConnection()
Dim sqlCom As New OleDbCommand()

Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
If txtFirstName.Text = "" Or txtLastName.Text = "" Or txtContact.Text = "" Or txtEmail.Text = "" Or comboMembershipType.Text = "" Then
    MessageBox.Show("Please complete the required fields.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
    Try
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Application.StartupPath & "\igcDatabase.accdb"
        sqlCom.Connection = conn
        conn.Open()

                        sqlCom.CommandText = "INSERT INTO [Members] " _
                                    & "([Member First Name], [Member Last Name], [Gender], [Contact], [Email])" _
                                      & " VALUES (@FirstName, @LastName, @Gender, @Contact, @Email)"



            Dim gender As String
            If rbtnMale.Checked = True Then
                gender = "Male"
            Else
                gender = "Female"
            End If

            'Parameter is used below to prevent SQL Injection
            sqlCom.Parameters.AddWithValue("FirstName", txtFirstName.Text)
            sqlCom.Parameters.AddWithValue("LastName", txtLastName.Text)
            sqlCom.Parameters.AddWithValue("Gender", gender)
            sqlCom.Parameters.AddWithValue("Contact", txtContact.Text)
            sqlCom.Parameters.AddWithValue("Email", txtEmail.Text)

            'Code below is used as the query does not return any data back to the form
            sqlCom.ExecuteNonQuery()

            'Query for Membership Types Table in MS Acess
            sqlCom.CommandText = "INSERT INTO [Membership Types] ([Membership Type])" & " VALUES (@MembershipType)"

            'Parameter is used below to prevent SQL Injection
            'sqlCom.Parameters.Clear() is used to clear the previous input of data
            sqlCom.Parameters.Clear()
            sqlCom.Parameters.AddWithValue("MembershipType", comboMembershipType.Text)

            'Code below is used as the query does not return any data back to the form
            sqlCom.ExecuteNonQuery()

            MessageBox.Show("Successfully added member!")

            'Close Data Connection
            conn.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End If
End Sub
End Class
9
  • can u try this sqlCom.CommandText = "INSERT INTO [Members] ([Member First Name], [Member Last Name], [Gender], [Contact], [Email])" _ & " VALUES (@FirstName, @LastName, @Gender, @Contact, @Email);".. added semicolon at the end of both the statements Commented Feb 26, 2016 at 7:06
  • why there is no @ in 'AddWithValue' of parameters and why you have not added the parameter @MembershipType Commented Feb 26, 2016 at 7:11
  • @Olivarsham If you refer back to my question, I stated I already tried to add a semicolon. However, I'm getting "Characters found after end of SQL statement." after doing so. Commented Feb 26, 2016 at 7:11
  • @Olivarsham , @ is not necessary in Parameters.Add function. Commented Feb 26, 2016 at 7:15
  • 1
    MS Access does not require @ in its parameters. It doesn't even have to be the same name. It is using ordinal position to place its parameters. As for the question, I don't believe you can do this the way you're doing it right now. You can however execute your SQL statements separately. Define the first SQL statement in your commandtext, add the parameters then execute. Next, define your second SQL statement, clear the parameters (from the first statement), add the new parameters, then execute. Commented Feb 26, 2016 at 8:12

3 Answers 3

1

Try this

Imports System.Data.OleDb
Public Class addNew
Dim conn As New OleDbConnection()
Dim sqlCom As New OleDbCommand()

Private Sub btnAddNew_Click(sender As Object, e As EventArgs) Handles btnAddNew.Click
If txtFirstName.Text = "" Or txtLastName.Text = "" Or txtContact.Text = "" Or txtEmail.Text = "" Or comboMembershipType.Text = "" Then
MessageBox.Show("Please complete the required fields.", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Try
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Application.StartupPath & "\igcDatabase.accdb"
    sqlCom.Connection = conn
    conn.Open()

    sqlCom.CommandText = "INSERT INTO [Members] ([Member First Name], [Member Last Name], [Gender], [Contact], [Email])" _
        & " VALUES (@FirstName, @LastName, @Gender, @Contact, @Email)"        

    Dim gender As String
    If rbtnMale.Checked = True Then
        gender = "Male"
    Else
        gender = "Female"
    End If

        sqlCom.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
        sqlCom.Parameters.AddWithValue("@LastName", txtLastName.Text)
        sqlCom.Parameters.AddWithValue("@Gender", gender)
        sqlCom.Parameters.AddWithValue("@Contact", txtContact.Text)
        sqlCom.Parameters.AddWithValue("@Email", txtEmail.Text)
sqlCom.ExecuteNonQuery()


sqlCom.CommandText = "INSERT INTO [Membership Types] ([Membership Type])" & "VALUES (@MembershipType)"

sqlCom.Parameters.Clear()
        sqlCom.Parameters.AddWithValue("@MembershipType", comboMembershipType.SelectedText)

    sqlCom.ExecuteNonQuery()
    MessageBox.Show("Successfully added member!")
    conn.Close()

Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try
End If
End Sub
End Class
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for your help, really appreciate it. However, I'm still getting Missing semicolon (;) at end of SQL statement.
try setting the command type to text. sqlCom.CommandType = System.Data.CommandType.Text
Problem fixed. Thank you very much!
1

What you've done is concatenate two strings - the & is not a space. Get rid of the & and the quotes in the second query, and just have membershiptype]) VALUES ... And then at the end of the first query add a space otherwise it reads ... @email)INSERT ... membershiptype])VALUES. Also have you tried adding just one ; after the first query, but not after the second one?

4 Comments

It may be nothing - but there's no space between membershiptype]) and VALUES in your code - it may be reading that line incorrectly. Try adding a space there, and at the end of your first query (so there's a space between the two queries)
I don't quite get what are you trying to tell me. I thought I did put a spacing between membershiptype]) and VALUES? Will be much appreciate you can provide me the code after fixed. Thanks in advance.
What you've done is concatenate two strings - the & is not a space. Get rid of the & and the quotes in the second query, and just have membershiptype]) VALUES ... And then at the end of the first query add a space otherwise it reads ... @email)INSERT ... membershiptype])VALUES. Also have you tried adding just one ; after the first query, but not after the second one?
Thanks for your help, problem fixed.
0

There is no @ in Addwithvalue it should be sqlCom.Parameters.AddWithValue("@Email", txtEmail.Text);

not sure about this but i am working on C# and the syntax be like sqlCom.CommandText = "INSERT INTO [Members] ([Member First Name], [Member Last Name], [Gender], [Contact], [Email]) VALUES (@FirstName, @LastName, @Gender, @Contact, @Email)" in C# on sure about VB

and try to use stored procedures it makes the code neat and work easy and also Prevents SQL Injections which is a big security concern.

and also you have not added gender's value to sqlcommand, pass it with parameters

10 Comments

@ is not necessary in Parameters.Add function. Even I added the @, the problem I am facing is still there.
stackoverflow.com/questions/11139791/… check this out @ is necessary and did u try to use it with query i wrote? and also you have not added gender's value to sqlcommand, pass it with parameters
Opps, sorry for the typo. I did include both in my latest VB.NET project. Just updated the code in question. And thanks for the link you provided, I already added the @ but the problem is still there.
kindly share latest screen of code and output error
After I added ; at the end of all sqlCom.Parameters.AddWithValue("@...", ....Text); , I'm getting Character is not valid. error
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.