SQL add data to two tables at once using VB.NET

Question

I'd like to insert data into two tables, the second table being a foreign key (which is auto-incremented) using VB.

Here is my code so far...

cmd.CommandText = "INSERT INTO Customer (CustomerUsername, CustomerAge, CustomerPassword)  " +
                   "VALUES (' " & txtusername.Text & " ' ,'" & txtage.Text & " ', '" & txtpassword.Text & " ')";
cmd.CommandText = "INSERT INTO Premier (CustomerID) FROM Customer.CustomerID";
cmd.ExecuteNonQuery();

My first INSERT works fine. Just not sure how to INSERT into the second table (as it is auto-incremented) and I am not using a textbox for the input.

Thanks in advance!

Answer 1

You can receive back the autoincrement value (A.K.A. IDENTITY) inserted in the first table with this query

cmd.CommandText = "INSERT INTO Customer (CustomerUsername, CustomerAge, CustomerPassword) 
                  VALUES(@user, @age, @pass); SELECT SCOPE_IDENTITY()"

cmd.Parameters.Add("@user", SqlDbType.NVarChar).Value =  txtusername.Text
cmd.Parameters.Add("@age", SqlDbType.Int).Value = Convert.ToInt32(txtage.Text)
cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = txtPassword.Text
Dim idCustomer = Convert.ToInt32(cmd.ExecuteScalar())

At this point is easy to add to the other table

cmd.CommandText = "INSERT INTO Premier (CustomerID) VALUES (@id)"
cmd.Parameters.Add("@id", SqlDbType.Int).Value = idCustomer
cmd.ExecuteNonQuery()

Notice how you can execute two commands together with the same call just separating them with a semicolon. Now, SCOPE_IDENTITY

Returns the last identity value inserted into an identity column in the same scope

In alternative you can use the OUTPUT clause with this syntax

cmd.CommandText = "INSERT INTO Customer (CustomerUsername, CustomerAge, CustomerPassword) 
                  OUTPUT INSERTED.ID
                  VALUES(@user, @age, @pass)"

where ID is the name of the autoincrement column from your Customer table represented by the keyword INSERTED

But the most important thing that you should look at is how I have changed your query to use a parameterized approach. There are many reasons to do it in this way and you can find the most important one looking for SQL Injection. Other reasons are parsing problems (what if your user name contains a single quote?) or some simple errors like the one you have when you insert the name of your customer. Did you notice that you add a spurious space before and after the customer name?

Finally, a bit outside the scope, but you should also consider that storing passwords in clear text inside a database is a big security risk.

See Best way to store passwords in database