Optimistic Concurrency - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Optimistic Concurrency

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2010-08-06 : 09:29:38

Hi,

Customers:
UserId
Firstname
LastName

Products:
UserId
Product

Both tables I would like to connect with each other so that by clicking on the "Save" to store data.

Variables:

Firstname: Test
LastName: test2

Product: computer

Now that we have two tables, I would like to simultaneously store data in both tables.
I do not want first to record the data in the first table, and then in another table.

Please Help

My code for first table:
public static void AddNewCustomer(string firstName, string lastName)
{
string query = "INSERT INTO Customers(FirstName,LastName) VALUES(@FirstName, @LastName)";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(query, myConnection);

myCommand.Parameters.AddWithValue("@FirstName", firstName);
myCommand.Parameters.AddWithValue("@LastName", lastName);

myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-08-06 : 11:34:40

specific issue
1. investigate triggers. might be of use to you...except you would need table#1 to contain all the info you want to save onwards into table#2. may not work in your situation.

general education comment
2. look into stored procedures
- performance
- code security
- application/database "seperation of duties"
3. read up on "SQL injection"

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 11:12:32

Is UserID in the Customers table an Identity column? If so, then you can check @@identity to get the last identity number entered. Then you can use that plus the product to enter into the Products table. You can do this in one transaction so it can be rolled back if an error occurs. Likewise you can pass all 3 parms to a stored procedure and the logic could be encapsulated there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources