How to deal with inserting values into two tables when exception occurs.

Question

Hi,

I have 2 tables loginInfo and UserInfo. LoginInfo Stores username and passwords while userinfo stores other user details such as Address,postalcode,phone etc.

I insert values into loginInfo first and if successful I enter the userInfo details. If exception occurs while entering details into userInfo i delete the loginInfo details.

This is how I do now

'login details are entered successfully
If CreateLogin(uName.uPass) Then
Try
'Create the user details
 CreateUser('Userdetails)
Catch ex As Exception
'if exception occurs then delete the login
DeleteLogin(uName)
End Try
End IF

Is there a better way to deal with this situation? can use Transactions?

Thanks in advance.

Answer 1

Yes, this is the perfect occasion for a transaction. Otherwise, what if the network goes down or your computer crashes before the call to DeleteLogin, you might be left with only half the data in your database.

They're quite easy to use. If you're using a SqlConnection, look at the SqlTRansaction class or if OleDB, look at OleDbTransaction etc. You'll find easy samples in MSDN.

Answer 2

Let's look at this from a higher level ...

You are better off normalizing the database, and put the user ID and password into the same table as all of the other user data. Since one user should have one and only one login ID, you are creating an unnecessary one-to-one relationship between two tables.