Inserting an auto generated primary key field into related multiple tables

Question

0.00/5 (No votes)

See more:

Hie guys i'm trying to insert an auto generating primary key field value to multple related tables below is my sample insert code it suposed to save 2 tables at once

sql = "INSERT INTO dbo.Clients (FirstName,Surname) values('" & (txtName.Text) & "','" & (txtSuname.Text) & "' )"
        sql2 = "INSERT INTO dbo.PersonalProduct(Product,Details)values(,'" & (txtProduct.Text) & "','" & (txtDetails.Text) & "')"

the primary key for the Clients table is auto generated the primary key is a foreign key in the PersonProduct Table but the value of primary key is not inserted into the PersonProduct Table how best can overcome this problem the database is SQL Server

Posted 22-Jul-11 4:07am

sijimann

Updated 22-Jul-11 4:08am

Manas Bhardwaj

v2

Add a Solution

2 solutions

Solution 3

There are so many things here...
1) I hate autogenerated keys. Why? Because if you need to refer to them, you either have to use a stored procedure to create a dummy record and return the ID, then fill it later, or create the record, and then retrieve it back to find out what the id it just created was. Yes, there are some ways to get round this, but they are a PITA and don't always work in an multiuser environment. Instead, I use GUID id's if I need to refer to that record later. This has the advantage that since the PC is assigning the ID you only need one trip to the DB to save the record, and you can move on.
2) You really need to look at using a Transaction around that code, or moving the two updates into a single Stored Procedure and transact it there. Why? What happens if the second insert fails? You have created the client, but then there will be an error, and you won;t be sure if you really did - so you will likely try to create him again... With a transaction, you either commit all the changes at once, or you roll them all back, so your database always contains valid data.
3) Don't concatenate your strings! Particularly with textbox contents! Google for "Bobby Tables" or "Sql Injection attack" and then use parametrized queries before you accidentally or deliberately get your database destroyed...

Posted 22-Jul-11 4:24am

OriginalGriff

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2011-07-22T04:19:00

Since you posted what looks like VB.NET code, I take it this is not in a stored procedure?

It's easier to do in a stored procedure, but you need to get the @@IDENTITY of the record that was added in the first INSERT command. You have nothing returning that ID. Once you get the ID, it's trivial to put it in the INSERT of the second table.

Read this[^] for a discussion of the concept.

Now, other problems I see with your code is that you're using string contatentation to build the SQL queries. DON'T! Use parameterized queries instead, or better yet, a stored procedure to do this, and populate the parameters using SqlParameter objects.

You're also not validating the data you have in the textbox fields.

Want to know why this is bad?? What do you think would happen if I typed:

x','x'); DROP TABLE Clients; --

into either of those textboxes??

Try reading this article[^] on SQL Injection Attacks and find out how to avoid this problem.

Inserting an auto generated primary key field into related multiple tables

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0

Inserting an auto generated primary key field into related multiple tables

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us