VB.NET INSERT INTO ... VALUES Statement to Multiple Tables

Question

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

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 — Olivarsham, Feb 26 '16 at 07:06
why there is no `@` in 'AddWithValue' of parameters and why you have not added the parameter `@MembershipType` — Olivarsham, Feb 26 '16 at 07: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. — Gideon, Feb 26 '16 at 07:11
@Olivarsham , `@` is not necessary in Parameters.Add function. — Gideon, Feb 26 '16 at 07:15
Just added `@`. However, the problem is still there although I added it. — Gideon, Feb 26 '16 at 07:24
`sqlCom.CommandText` contains 2 `INSERT` statements which you attempt to execute together as a batch. MS Access will only allow you to execute a single statement at a time. — HansUp, Feb 26 '16 at 08:02
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. — F0r3v3r-A-N00b, Feb 26 '16 at 08:12
Do like F0r3v3r-A-N00b said ... execute one `INSERT` with its parameters, and then execute the second separately. — HansUp, Feb 26 '16 at 08:14

ninjaRoche · Answer 1 · 2016-02-26T11:08:27.840

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?

edited Feb 26 '16 at 11:08

answered Feb 26 '16 at 07:25

ninjaRoche

62
1
6

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) – ninjaRoche Feb 26 '16 at 07:35
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. – Gideon Feb 26 '16 at 07:49
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? – ninjaRoche Feb 26 '16 at 09:37
Thanks for your help, problem fixed. – Gideon Feb 26 '16 at 10:40

score 1 · Accepted Answer · answered Feb 26 '16 at 08:18

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

Thanks for your help, really appreciate it. However, I'm still getting `Missing semicolon (;) at end of SQL statement.` — Gideon, Feb 26 '16 at 08:28
try setting the command type to text. sqlCom.CommandType = System.Data.CommandType.Text — F0r3v3r-A-N00b, Feb 26 '16 at 09:05

Ravdeep Aurora · Answer 3 · 2016-02-26T07:27:51.423

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

edited Feb 26 '16 at 07:27

answered Feb 26 '16 at 07:14

Ravdeep Aurora

32
7

`@` is not necessary in Parameters.Add function. Even I added the `@`, the problem I am facing is still there. – Gideon Feb 26 '16 at 07:20
http://stackoverflow.com/questions/11139791/how-to-use-parameters-in-an-sql-command-in-vb 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 – Ravdeep Aurora Feb 26 '16 at 07:22
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. – Gideon Feb 26 '16 at 07:30
kindly share latest screen of code and output error – Ravdeep Aurora Feb 26 '16 at 07:32
After I added `;` at the end of all `sqlCom.Parameters.AddWithValue("@...", ....Text);` , I'm getting `Character is not valid.` error – Gideon Feb 26 '16 at 07:38
Try executing one command at a time.. Like sqlcom.CommandText="Command Here" ***Parameters Here Of command 1*** sqlcom.ExecuteNonQuery(); sqlcom.CommandText="Command 2 Here" ***Command 2 parameters here*** sqlcom.ExecuteNonQuery(); – Ravdeep Aurora Feb 26 '16 at 07:53
also try using the query like mine – Ravdeep Aurora Feb 26 '16 at 08:01
Follow exactly what you got, still got the error: `Missing semicolon (;) at end of SQL statement.` – Gideon Feb 26 '16 at 08:09
can you share the screenshot of visual studio with error code – Ravdeep Aurora Feb 26 '16 at 08:32
There is no error from Visual Studio. The error is from `Catch ex As Exception MessageBox.Show(ex.Message)` – Gideon Feb 26 '16 at 08:37

VB.NET INSERT INTO ... VALUES Statement to Multiple Tables

3 Answers3