Inserting data into two separate tables in an Access database

Hello,

I am creating a code that allows the user to insert data into textboxes and then have it stored in an Access database. The code that I used worked previously but since I added the second query, nothing is added to either tables. Here is my code:

    Private Sub btnSetPassword_Click(sender As Object, e As EventArgs) Handles btnSetPassword.Click
            provider = "Provider=Microsoft.ACE.OLEDB.12.0;"    
            dataFile = "Data Source=|DataDirectory|\UserDatabase.accdb"     
            connString = provider & dataFile        

            Dim query As String
            query = ("INSERT INTO [Students] ([First_Name], [Surname], [Year]) VALUES ('" & FirstNameTextBox.Text & "', '" & SurnameTextBox.Text & "','" & SchoolYearTextBox.Text & "')")
            Dim query2 As String
            Dim ID As Integer
            query2 = ("INSERT INTO [Users] ([StudentID], [Username], [Password]) VALUES ('" & ID & "', '" & UsernameTextBox.Text & "', '" & PasswordTextBox.Text & "')") 'WHERE StudentID = Students.StudentID")


            Using conn As OleDbConnection = New OleDbConnection(connString)     
                dbUp = New OleDbCommand(query, conn)
                dbup2 = New OleDbCommand(query2, conn)
                Try
                    conn.Open()
                    dbUp.Parameters.AddWithValue("@First_Name", FirstNameTextBox.Text)
                    dbUp.Parameters.AddWithValue("@Surname", SurnameTextBox.Text)
                    dbUp.Parameters.AddWithValue("@Year", SchoolYearTextBox.Text)

                    ID = dbup2.ExecuteScalar()
                    dbup2.Parameters.AddWithValue("@StudentID", ID)
                    dbup2.Parameters.AddWithValue("@Username", UsernameTextBox.Text)
                    dbup2.Parameters.AddWithValue("@Password", PasswordTextBox.Text)

Thank you

[djjeavons]

Hi

Looking at your code, I am confused as to why you are creating an SQL statement with values from your text boxes and then proceeding to add parameters to your OleDbCommand objects. Usually you would only use parameters if you were using a parameterised query such as "INSERT INTO [Users] ([StudentID], [Username], [Password]) VALUES (?, ?, ?)".

Secondly, I do not see in your code where you execute the first query (dbUp OleDbCommand). Also, for your second query you use the ID variable but this has not been set yet so will be 0 (see next statement).

When you use the ExecuteScalar() method on dbup2, what are you expecting to be returned? Is it the Student ID (as your code would suggest)? Does your query return the new ID (I am assuming it does not as this is an Access Database but could be wrong).

I am assuming that you want to add a new student record, grab the ID of that student and then add their user details to the Users table. Is that correct? If so, can you post the structure of both tables.

HTH

Hi,

Thank you for your reply. You are correct, I am using both SQL and OleDbCommands but to be honest, I am not sure why I am, but this method did actually work until a few days ago. The values did not add when I used the SQL statement by itself.

In regards to the ExecuteScalar(), I want the StudentID from the Students table to be used in the Users table (as you have said), and you are correct, it does not return the new ID.

So yes, what you have explained is exactly what I would like to do, but I am not sure how. I'm not really too sure how to post the structure of the tables also, sorry, how would I do that?

Leyla

[djjeavons]

Hi

Hopefully the below code will clear things up a little. Before looking at the code, I created an Access database with a Students and Users table. One thing to note is that I have used an AutoNumber field (this is an integer field that increments by one for each new record added) in the Students table so that we get a unique StudentID for each new record. Then in the Users table, the StudentID field is set to a number field.

    Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\UserDatabase.accdb;Persist Security Info=False;"

    'Sql statements to add a new student record, get the last created StudentID and then add a record to the users table for that studentID
    Dim studentSql As String = String.Format("INSERT INTO [Students] ([First_Name], [Surname], [Year]) VALUES ('{0}', " _
                                           & "'{1}', '{2}')", firstNameTextBox.Text, surnameTextBox, Text, schoolYearTextBox.Text)
    Dim getStudentIdSql As String = "SELECT MAX(StudentID) FROM [Students]"

    'Open a connection and add the Student record
    Dim connection As New OleDbConnection(connectionString)

    connection.Open()

    'Insert student record
    Dim command As New OleDbCommand(studentSql, connection)
    command.ExecuteNonQuery()

    'Now get last created studentID
    command = New OleDbCommand(getStudentIdSql, connection)
    Dim studentID As Integer = Convert.ToInt32(command.ExecuteScalar)

    'Now add user record with the studentID
    Dim userSql As String = String.Format("INSERT INTO [Users] ([StudentID], [Username], [Password]) VALUES (" _
                                & "{0}, '{1}', '{2}')", studentID, userNameTextBox.Text, passwordTextBox.Text)

    command = New OleDbCommand(userSql, connection)
    command.ExecuteNonQuery()

    connection.Close()

The above code makes use of the MAX query to get the last student ID added. Note that if this is a multi user system then this is not 100% safe as you may get concurrency issues.

HTH

That's excellent, thank you very much. It works exactly as I wanted it to.

[Fecs]

Please friends help me out. I have created a database that has 4 separate tables inside it. How can I use VB. NET to insert data into all of the 4 tables with a single button click?
please help me to understand and learn.