Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server VB.NET
ok so i have a web form in which data is being added into two tables. one table has a FK(email) which refers to the other table primary key(email).
when i run the code insert values in to the form to store it in the db...but i get an error
 
what im doing wrong in my code below, please help
 
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Tech_Admin". The conflict occurred in database "E:\ATOM\06012013\APP_DATA\ATOM.MDF",

 

 
Below is my code:

connect.ConnectionString = Constr

Dim query1 As String = "INSERT into Tech VALUES (@first, @last, @add, @phone, @email, @status, @photo, @registration, @DOB)"
Dim query2 As String = "INSERT into Admin VALUES (@email, @password, @type)"
 

Dim cmd As New SqlCommand
Dim cmd2 As New SqlCommand
cmd.CommandText = query1
cmd2.CommandText = query2
cmd.Connection = connect
cmd2.Connection = connect

 
cmd.Parameters.AddWithValue("@first", txtFirst.Text)
cmd.Parameters.AddWithValue("@last", txtLast.Text)
cmd.Parameters.AddWithValue("@add", txtAddress.Text)
cmd.Parameters.AddWithValue("@phone", txtPhone.Text)
cmd.Parameters.AddWithValue("@email", txtEmail.Text)
cmd.Parameters.AddWithValue("@status", txtStatus.Text)
cmd.Parameters.AddWithValue("@photo", lblPath.Text)
cmd.Parameters.AddWithValue("@registration", txtRegistration.Text)
cmd.Parameters.AddWithValue("@DOB", txtDOB.Text)

cmd2.Parameters.AddWithValue("@email", txtEmail.Text)
cmd2.Parameters.AddWithValue("@password", txtPassword.Text)
cmd2.Parameters.AddWithValue("@type", txtType.Text)

Dim Ts As SqlTransaction
Try
connect.Open()
Ts = connect.BeginTransaction
cmd.Transaction = Ts
cmd2.Transaction = Ts

Dim i As Integer = cmd.ExecuteNonQuery
If i > 0 Then
MsgBox("tech")
End If

Dim j As Integer = cmd2.ExecuteNonQuery
If j > 0 Then
MsgBox("admin table")
End If

Ts.Commit()

Catch ex As Exception
MsgBox("unsuccessfull")
Throw ex

Ts.Rollback()

Finally
connect.Close()

End Try

 
Please help me out...
Posted 25-Jan-13 15:50pm
Edited 25-Jan-13 15:51pm
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Unsure, but I do notice in the catch you throw before before you rollback.
Also, just use throw, not throw ex.
 
Have you tried executing the commands in the other order?
  Permalink  
v3

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



Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 25 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100