I'm trying to insert data into three tables in a relational database. Right now it will only insert to the first table and then it errors out. If you have some genius to share, don't hold back. Thanks.
Imports System
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Partial Public Class DisAddCo
Inherits System.Web.UI.Page
Protected Sub FormView1_ItemInserting(ByVal sender As Object, ByVal e As FormViewInsertEventArgs)
' Retrieve controls
Dim CompanyTextBox As TextBox = TryCast(FormView1.FindControl("CompanyTextBox"), TextBox)
Dim FirstNameTextBox As TextBox = TryCast(FormView1.FindControl("FirstNameTextBox"), TextBox)
Dim LastNameTextBox As TextBox = TryCast(FormView1.FindControl("LastNameTextBox"), TextBox)
Dim Address1TextBox As TextBox = TryCast(FormView1.FindControl("Address1TextBox"), TextBox)
Dim Address2TextBox As TextBox = TryCast(FormView1.FindControl("Address2TextBox"), TextBox)
Dim CityTextBox As TextBox = TryCast(FormView1.FindControl("CityTextBox"), TextBox)
Dim StateTextBox As TextBox = TryCast(FormView1.FindControl("StateTextBox"), TextBox)
Dim ZipTextBox As TextBox = TryCast(FormView1.FindControl("ZipTextBox"), TextBox)
Dim PhoneTextBox As TextBox = TryCast(FormView1.FindControl("PhoneTextBox"), TextBox)
Dim customerid As Integer = 0
If CompanyTextBox Is Nothing Then
Return
End If
If FirstNameTextBox Is Nothing Then
Return
End If
If LastNameTextBox Is Nothing Then
Return
End If
If Address1TextBox Is Nothing Then
Return
End If
If CityTextBox Is Nothing Then
Return
End If
If StateTextBox Is Nothing Then
Return
End If
If ZipTextBox Is Nothing Then
Return
End If
If PhoneTextBox Is Nothing Then
Return
End If
' Set-up command
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("HRIServiceConnectionString1").ConnectionString)
Dim cmd As New SqlCommand("INSERT INTO [Customers] ([Company], [Address1], [Address2], [Phone]) VALUES (@Company, @Address1, @Address2, @Phone); SELECT SCOPE_IDENTITY();", conn)
cmd.Parameters.AddWithValue("Company", CompanyTextBox.Text)
cmd.Parameters.AddWithValue("Address1", Address1TextBox.Text)
cmd.Parameters.AddWithValue("Address2", Address2TextBox.Text)
cmd.Parameters.AddWithValue("Phone", PhoneTextBox.Text)
Try
conn.Open()
Dim cusID As Integer = Convert.ToInt32(cmd.ExecuteScalar())
Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
' Do your second insert here
Dim cmd2 As New SqlCommand("INSERT INTO [Contacts] ([cusID], [FirstName], [LastName]) VALUES (@cusID, @FirstName, @LastName); SELECT SCOPE_IDENTITY();", conn)
cmd2.Parameters.AddWithValue("cusID", customerid)
'cmd2.Parameters.AddWithValue("contactID", FirstNameTextBox.Text)
cmd2.Parameters.AddWithValue("FirstName", FirstNameTextBox.Text)
cmd2.Parameters.AddWithValue("LastName", LastNameTextBox.Text)
Try
conn.Open()
Dim contactID As String = cmd2.ExecuteScalar().ToString()
Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
' Do your third insert here
Dim cmd3 As New SqlCommand("INSERT INTO [Zip] ([cusID], [zipID], [City], [State], [Zip]) VALUES (@cusID, @zipID, @City, @State, @Zip); SELECT SCOPE_IDENTITY();", conn)
cmd3.Parameters.AddWithValue("cusID", customerid)
cmd3.Parameters.AddWithValue("City", CityTextBox.Text)
cmd3.Parameters.AddWithValue("State", StateTextBox.Text)
cmd3.Parameters.AddWithValue("Zip", ZipTextBox.Text)
Try
conn.Open()
Dim zipID As Integer = Convert.ToInt32(cmd3.ExecuteScalar())
Finally
If conn IsNot Nothing Then
conn.Close()
End If
End Try
End Sub
End Class