Insert to multiple tables

Question

foundsheep 0 Junior Poster

16 Years Ago

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

asp.net

2 Contributors
52 Replies
449 Views
4 Days Discussion Span
Latest Post 16 Years Ago Latest Post by foundsheep

All 52 Replies

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

I'm sorry, what did you do? That's the same error as last time.

What have you done since then?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

SheSaidImaPregy 28 Veteran Poster · Answer 1 · 2008-02-16T02:24:28+00:00

Here you go:

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)", 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)

        Dim cmd2 As New SqlCommand("INSERT INTO [Contacts] ([cusID], [FirstName], [LastName]) VALUES (@cusID, @FirstName, @LastName)", conn)
        cmd2.Parameters.AddWithValue("@cusID", customerid)
        'cmd2.Parameters.AddWithValue("@contactID", FirstNameTextBox.Text)
        cmd2.Parameters.AddWithValue("@FirstName", FirstNameTextBox.Text)
        cmd2.Parameters.AddWithValue("@LastName", LastNameTextBox.Text)

        Dim cmd3 As New SqlCommand("INSERT INTO [Zip] ([cusID], [zipID], [City], [State], [Zip]) VALUES (@cusID, @zipID, @City, @State, @Zip)", 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()

            cmd.ExecuteNonQuery()
            cmd = New SqlCommand("SELECT SCOPE_IDENTITY()", conn)
            Dim cusID As Integer = Convert.ToInt32(cmd.ExecuteScalar())

            cmd2.ExecuteNonQuery()
            cmd2 = New SqlCommand("SELECT SCOPE_IDENTITY()", conn)
            Dim contactID As String = cmd2.ExecuteScalar().ToString()

            cmd3.ExecuteNonQuery()
            cmd3 = New SqlCommand("SELECT SCOPE_IDENTITY()", conn)
            Dim zipID As Integer = Convert.ToInt32(cmd3.ExecuteScalar())
        Finally
            If conn.State = ConnectionState.Open Then conn.Close()
        End Try
    End Sub
End Class

SheSaidImaPregy 28 Veteran Poster · Answer 2 · 2008-02-16T02:25:30+00:00

The reason why it was failing is that you needed to execute the insert first, and you do that by using the ExecuteNonQuery() command, which cannot return a value besides the number of records affected.

Therefore they failed.

Hope I helped.

foundsheep 0 Junior Poster · Answer 3 · 2008-02-16T02:41:09+00:00

You are a helpy helperton for sure, but I did get this:

Server Error in '/HRIService' Application.
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30451: Name 'ConnectionState' is not declared.

Source Error:

Line 80: Dim zipID As Integer = Convert.ToInt32(cmd3.ExecuteScalar())
Line 81: Finally
Line 82: If conn.State = ConnectionState.Open Then conn.Close()
Line 83: End Try
Line 84: End Sub

SheSaidImaPregy 28 Veteran Poster · Answer 4 · 2008-02-16T02:48:11+00:00

oops, I think I switched that one around. Replace line 82 with this:

If conn.State = System.Data.ConnectionState.Open Then conn.Close()

I don't do codebehind coding all because of that stuff. Anyway, there you go ^^

foundsheep 0 Junior Poster · Answer 5 · 2008-02-16T03:01:05+00:00

That fixed that but , there's always a but, I got this:

Object cannot be cast from DBNull to other types.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types.

Source Error:

Line 70: cmd.ExecuteNonQuery()
Line 71: cmd = New SqlCommand("SELECT SCOPE_IDENTITY()", conn)
Line 72: Dim cusID As Integer = Convert.ToInt32(cmd.ExecuteScalar())
Line 73:
Line 74: cmd2.ExecuteNonQuery()

I'm getting some stupid error in SQL about not being able to let certain fields be null even though they really aren't.

SheSaidImaPregy 28 Veteran Poster · Answer 6 · 2008-02-16T03:04:28+00:00

Don't convert it, just let it come. The thing is, it doesn't matter if you convert it or not, it is what it is. No one else touches it so you have no worries about it being an integer. Remove the Convert.ToInt32 and see what it does.

SheSaidImaPregy 28 Veteran Poster · Answer 7 · 2008-02-16T03:12:07+00:00

Now, it should have worked the way you had it before, with the SELECT SCOPE IDENTITY() within the sql, but since it didn't, we'll sort down the problem, then put the statements back together.

foundsheep 0 Junior Poster · Answer 8 · 2008-02-16T03:14:26+00:00

I have this crap now ... the db is set up to not allow nulls.

Server Error in '/HRIService' Application.
Cannot insert the value NULL into column 'contactID', table 'HRIService.dbo.Customers'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'contactID', table 'HRIService.dbo.Customers'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Source Error:

Line 68: conn.Open()
Line 69:
Line 70: cmd.ExecuteNonQuery()
Line 71: cmd = New SqlCommand("SELECT SCOPE_IDENTITY()", conn)
Line 72: Dim cusID As Integer = Convert.ToInt32(cmd.ExecuteScalar())

Source File: C:\Inetpub\wwwroot\HRIService\ServiceExpress\DisAddCo.aspx.vb Line: 70

Stack Trace:

SheSaidImaPregy 28 Veteran Poster · Answer 9 · 2008-02-16T03:30:11+00:00

Well when you fix that portion, here's the new code:

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)

        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)

        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 cusID As Integer = Convert.ToInt32(cmd.ExecuteScalar())

            Dim contactID As String = cmd2.ExecuteScalar().ToString()

            Dim zipID As Integer = Convert.ToInt32(cmd3.ExecuteScalar())
        Finally
            If conn.State = ConnectionState.Open Then conn.Close()
        End Try
    End Sub
End Class

foundsheep 0 Junior Poster · Answer 10 · 2008-02-16T03:41:44+00:00

I don't know why it is throwing the null error. My database is fine from looking at it. Could it be corrupted?

SheSaidImaPregy 28 Veteran Poster · Answer 11 · 2008-02-16T03:51:06+00:00

Does your column "ContactID" all integer? You're throwing a first name in ContactID, nothing else. I would suggest that you usesomething else here, like an IDENTITY primary key.

foundsheep 0 Junior Poster · Answer 12 · 2008-02-16T03:55:52+00:00

contactID is an int datatype. I'm not following your second statement. Were you referring the line that was commented out?

SheSaidImaPregy 28 Veteran Poster · Answer 13 · 2008-02-16T04:08:08+00:00

Yes and no.

Does ContactID have a default value, maybe an auto-increment (IDENTITY)? Otherwise, it will end up being NULL. And keep in mind, PRIMARY's cannot be NULL

foundsheep 0 Junior Poster · Answer 14 · 2008-02-19T00:40:35+00:00

This is no longer having an error but it the results post no info to the customers table and the zip and contact IDs are not linked. Here is the code.

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 [Contacts] ([cusID], [FirstName], [LastName]) VALUES (@cusID, @FirstName, @LastName); SELECT SCOPE_IDENTITY();", conn)
        cmd.Parameters.AddWithValue("@cusID", customerid)
        cmd.Parameters.AddWithValue("@FirstName", FirstNameTextBox.Text)
        cmd.Parameters.AddWithValue("@LastName", LastNameTextBox.Text)

        Dim cmd2 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)

        Dim cmd3 As New SqlCommand("INSERT INTO [Zip] ([cusID], [City], [State], [Zip]) VALUES (@cusID, @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 cusID As Integer = Convert.ToInt32(cmd.ExecuteScalar())

            Dim contactID As Integer = Convert.ToInt32(cmd.ExecuteScalar())

            Dim zipID As Integer = Convert.ToInt32(cmd3.ExecuteScalar())
        Finally
            If conn.State = System.Data.ConnectionState.Open Then conn.Close()
        End Try
    End Sub
End Class

foundsheep 0 Junior Poster · Answer 15 · 2008-02-19T01:09:54+00:00

By the way I was told the contact table would need to be updated before the customer table. I switched the order and that is how I came up with my current situation (no errros, but not working). But that logic doesn't make sense to me. The customer table should be first or simultaneous because it is the "main" one.

SheSaidImaPregy 28 Veteran Poster · Answer 16 · 2008-02-19T01:44:18+00:00

The order they are updated does not matter at all since the values you retrieve do not effect the statements. You are probably missing something here.

What is cusID, contactID, and ZipID for? You set them but they are never referenced.

And you set customerid to zero, but never changed it. Therefore you will successfully insert the first batch, but everytime after that you will error as there is already an id with "0".

Tell me how the tables relate to each other and I'll show you the correct path. Otherwise you are querying for the last ID entered but are never using it.

foundsheep 0 Junior Poster · Answer 17 · 2008-02-19T01:51:14+00:00

Customer Table
Name - Customer
PK- cusID
FK - contactID (links to contactID in Contacts)
FK - zipID (links to zipID in Zip)

Contacts Table
Name - Contacts
PK - contactID
FK - cusID (links to cusID in Customer)

Zip Table
Name - Zip
PK - zipID
FK - cusID (links to cusID in Customer)

SheSaidImaPregy 28 Veteran Poster · Answer 18 · 2008-02-19T02:17:31+00:00

Hmm, I don't use foreign keys, but I know what they are. Well I do use foreign keys, but I don't specify them.

Here's the code How I think it should be updated:

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)

        If CompanyTextBox Is Nothing Then Return
        If FirstNameTextBox Is Nothing Then Return
        If LastNameTextBox Is Nothing Then Return
        If Address1TextBox Is Nothing Then Return
        If CityTextBox Is Nothing Then Return
        If StateTextBox Is Nothing Then Return
        If ZipTextBox Is Nothing Then Return
        If PhoneTextBox Is Nothing Then Return

        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())

            cmd = New SqlCommand("INSERT INTO [Contacts] ([cusID], [FirstName], [LastName]) VALUES (@cusID, @FirstName, @LastName); SELECT SCOPE_IDENTITY();", conn)
            cmd.Parameters.AddWithValue("@cusID", cusID)
            cmd.Parameters.AddWithValue("@FirstName", FirstNameTextBox.Text)
            cmd.Parameters.AddWithValue("@LastName", LastNameTextBox.Text)

            cmd.ExecuteScalar()

            cmd = New SqlCommand("INSERT INTO [Zip] ([cusID], [City], [State], [Zip]) VALUES (@cusID, @City, @State, @Zip)", conn)
            cmd3.Parameters.AddWithValue("@cusID", cusID)
            cmd3.Parameters.AddWithValue("@City", CityTextBox.Text)
            cmd3.Parameters.AddWithValue("@State", StateTextBox.Text)
            cmd3.Parameters.AddWithValue("@Zip", ZipTextBox.Text)

            cmd.ExecuteScalar()
        Finally
            If conn.State = System.Data.ConnectionState.Open Then conn.Close()
        End Try
    End Sub
End Class

foundsheep 0 Junior Poster · Answer 19 · 2008-02-19T02:28:46+00:00

Now I have this error ...

Server Error in '/HRIService' Application.
Cannot insert the value NULL into column 'contactID', table 'HRIService.dbo.Customers'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'contactID', table 'HRIService.dbo.Customers'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Source Error:

Line 38: conn.Open()
Line 39:
Line 40: Dim cusID As Integer = Convert.ToInt32(cmd.ExecuteScalar())
Line 41:
Line 42: cmd = New SqlCommand("INSERT INTO [Contacts] ([cusID], [FirstName], [LastName]) VALUES (@cusID, @FirstName, @LastName); SELECT SCOPE_IDENTITY();", conn)

Source File: C:\Inetpub\wwwroot\HRIService\ServiceExpress\DisAddCo.aspx.vb Line: 40

SheSaidImaPregy 28 Veteran Poster · Answer 20 · 2008-02-19T02:35:21+00:00

it's failing on the very first call, which means yes you either have to create the contacts first, to grab the id, but I am sure you will run into the same error cause you need cusID for that.

I'll get back to you in a second.

SheSaidImaPregy 28 Veteran Poster · Answer 21 · 2008-02-19T02:46:16+00:00

I think since you have them all relating to each other, you have no main table. They are all tables that rely on each other's data, which means you cannot change or add anything. You should have a main table that isn't bound by another table.

This is your problem:
Customer Table
Name - Customer
PK- cusID
FK - contactID (links to contactID in Contacts)
FK - zipID (links to zipID in Zip)

Contacts Table
Name - Contacts
PK - contactID
FK - cusID (links to cusID in Customer)

You would have to create the contact key first, before the customer key. But you cannot do this because it is relying on you having the customer key first. It's stuck in it's own loop. Then you when you fix this, you will be stuck in the same situation with zip table and customer table. I recommend a quick database restructure to the following:

Customer Table
Name - Customer
PK- cusID

Contacts Table
Name - Contacts
FK - cusID (links to cusID in Customer)

Zip Table
Name - Zip
FK - cusID (links to cusID in Customer)

Drop all primary keys from the tables except for Customer. Then make cusID all the foreign keys. This way, they are all related based on the cusID. It's more efficient, less problem prone, and saves a lot of room and no wasted columns. I cannot see a valid reason for many different unique primary keys.

Do this, and everything will work.

foundsheep 0 Junior Poster · Answer 22 · 2008-02-19T02:56:45+00:00

So, there's no need for a primary key in contacts and zip?

SheSaidImaPregy 28 Veteran Poster · Answer 23 · 2008-02-19T03:00:54+00:00

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

Nope, not with a foreign key.

SheSaidImaPregy 28 Veteran Poster · Answer 24 · 2008-02-19T03:02:42+00:00

All the keys are there for is to relate data and basically index the database as it is the key that you "primarily" use to search. The difference between foreign key and primary key is basically that a primary key is relative to the table at hand. A foreign key shares a distinction among many tables. Therefore, if you delete the primary key that has foreign keys, then you will delete everything (primary and foreign), and same with update.

foundsheep 0 Junior Poster · Answer 25 · 2008-02-19T03:24:30+00:00

Now, I have this:

Cannot insert the value NULL into column 'contactID', table 'HRIService.dbo.Customers'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'contactID', table 'HRIService.dbo.Customers'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Source Error:

Line 38: conn.Open()
Line 39:
Line 40: Dim cusID As Integer = Convert.ToInt32(cmd.ExecuteScalar())
Line 41:
Line 42: cmd = New SqlCommand("INSERT INTO [Contacts] ([cusID], [FirstName], [LastName]) VALUES (@cusID, @FirstName, @LastName); SELECT SCOPE_IDENTITY();", conn)

Source File: C:\Inetpub\wwwroot\HRIService\ServiceExpress\DisAddCo.aspx.vb Line: 40

foundsheep 0 Junior Poster · Answer 26 · 2008-02-19T03:32:30+00:00

I changed the relations in the db. So, that it was just linking cusID from customers to contacts and zip.

SheSaidImaPregy 28 Veteran Poster · Answer 27 · 2008-02-19T03:37:51+00:00

Within the Customers table, you also have to remove contactid and zipid.

SheSaidImaPregy 28 Veteran Poster · Answer 28 · 2008-02-19T03:39:32+00:00

Those in red have to be removed. Green are optional. However, you need a main table that is unaffected by FK.

Customer Table
Name - Customer
PK- cusID
FK - contactID (links to contactID in Contacts)
FK - zipID (links to zipID in Zip)

Contacts Table
Name - Contacts
PK - contactID
FK - cusID (links to cusID in Customer)

Zip Table
Name - Zip
PK - zipID
FK - cusID (links to cusID in Customer)

Insert to multiple tables

Recommended Answers Collapse Answers

All 52 Replies

Recommended Answers