User Name Password Register
DaniWeb IT Discussion Community
Home
Forums
Tutorials
Code Snippets
Blogs
Link Directory
All
•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the ASP.NET section within the Web Development category of DaniWeb, a massive community of 374,007 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,907 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Join our community!
Please support our ASP.NET advertiser: Lunarpages ASP Web Hosting
Views: 2399 | Replies: 52
Reply
Page 1 of 6 1 23 > Last »
•
•
Join Date: Feb 2008
Location: Va Beach
Posts: 179
Reputation: foundsheep is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
foundsheep's Avatar
foundsheep foundsheep is offline Offline
Junior Poster

Insert to multiple tables

  #1  
Feb 15th, 2008
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.

Help with Code Tags 
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
AddThis Social Bookmark Button
Reply With Quote  
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Rep Power: 3
Solved Threads: 61
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Insert to multiple tables

  #2  
Feb 15th, 2008
Here you go:
Help with Code Tags 
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
Reply With Quote  
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Rep Power: 3
Solved Threads: 61
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Insert to multiple tables

  #3  
Feb 15th, 2008
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.
Reply With Quote  
•
•
Join Date: Feb 2008
Location: Va Beach
Posts: 179
Reputation: foundsheep is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
foundsheep's Avatar
foundsheep foundsheep is offline Offline
Junior Poster

Re: Insert to multiple tables

  #4  
Feb 15th, 2008
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
Reply With Quote  
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Rep Power: 3
Solved Threads: 61
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Insert to multiple tables

  #5  
Feb 15th, 2008
oops, I think I switched that one around. Replace line 82 with this:
Help with Code Tags 
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 ^^
Reply With Quote  
•
•
Join Date: Feb 2008
Location: Va Beach
Posts: 179
Reputation: foundsheep is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
foundsheep's Avatar
foundsheep foundsheep is offline Offline
Junior Poster

Re: Insert to multiple tables

  #6  
Feb 15th, 2008
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.
Reply With Quote  
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Rep Power: 3
Solved Threads: 61
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Insert to multiple tables

  #7  
Feb 15th, 2008
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.
Reply With Quote  
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Rep Power: 3
Solved Threads: 61
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Insert to multiple tables

  #8  
Feb 15th, 2008
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.
Reply With Quote  
•
•
Join Date: Feb 2008
Location: Va Beach
Posts: 179
Reputation: foundsheep is an unknown quantity at this point 
Rep Power: 1
Solved Threads: 0
foundsheep's Avatar
foundsheep foundsheep is offline Offline
Junior Poster

Re: Insert to multiple tables

  #9  
Feb 15th, 2008
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:
Reply With Quote  
•
•
Join Date: Sep 2007
Posts: 1,054
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Rep Power: 3
Solved Threads: 61
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Insert to multiple tables

  #10  
Feb 15th, 2008
Well when you fix that portion, here's the new code:
Help with Code Tags 
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
Reply With Quote  
Reply
Page 1 of 6 1 23 > Last »

•
•
•
•

Only community members can participate in forum threads. You must register or log in to contribute.

Register
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

•
•
•
•
•
•
•
•
DaniWeb ASP.NET Marketplace
•
•
•
•
Thread Tools Display Modes
Linear Mode Linear Mode

•
•
•
•
css div html symantec tables
Similar Threads
Other Threads in the ASP.NET Forum

Advertising Opportunities on DaniWeb
Contact Us - Newsletter Archive - Sitemap - Privacy Statement
All times are GMT -4. The time now is 10:38 pm.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC