CODEWITHSUNDEEP
vb.netms-access
Gideon
Gideon

Reputation: 69

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

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

Upvotes: 0

Views: 2369

Answers (3)

ninjaRoche
ninjaRoche

Reputation: 62

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?

Upvotes: 1

F0r3v3r-A-N00b
F0r3v3r-A-N00b

Reputation: 3003

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

Upvotes: 1

Ravdeep Aurora
Ravdeep Aurora

Reputation: 32

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

Upvotes: 0

Related Questions