954,595 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article
4 Years Ago
0

Insert to multiple tables

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
foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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.

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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

foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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 ^^

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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.

foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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.

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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:

foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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
SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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

foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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.

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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

foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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.

foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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.

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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)

foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 
4 Years Ago
0

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
SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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

foundsheep
Junior Poster
179 posts since Feb 2008
Reputation Points: 10
Solved Threads: 0
 

This article has been dead for over three months

Post: Markdown Syntax: Formatting Help
You
 
 
© 2012 DaniWeb® LLC
Home | About Us | Contact Us | Terms of Service | Advertising Opportunities
RSS Feed RSS Feed