ASP.NET

Insert to DB & View Results on Different Page

Please support our ASP.NET advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Last »

•

Join Date: Feb 2008

Posts: 179

Reputation: foundsheep is an unknown quantity at this point

Solved Threads: 0

foundsheep foundsheep is offline

Offline

Junior Poster

Insert to DB & View Results on Different Page

Feb 19th, 2008

I have a page that inserts information to multiple tables. This part is working fine. What I want to add is when a user adds a new company not only is it inserting to the db but it is also posting the results on a separate page. For instance it will fill in some of the fields of a new form that will be submitted to create a new ticket. I'm not sure how to this part.

This is the code I have for currently:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
 
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)
            cmd.Parameters.AddWithValue("@cusID", cusID)
            cmd.Parameters.AddWithValue("@City", CityTextBox.Text)
            cmd.Parameters.AddWithValue("@State", StateTextBox.Text)
            cmd.Parameters.AddWithValue("@Zip", ZipTextBox.Text)
 
            cmd.ExecuteScalar()
        Finally
            If conn.State = System.Data.ConnectionState.Open Then conn.Close()
        End Try
 
        'Response.Redirect("default.aspx")
 
    End Sub
 
End Class
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)
            cmd.Parameters.AddWithValue("@cusID", cusID)
            cmd.Parameters.AddWithValue("@City", CityTextBox.Text)
            cmd.Parameters.AddWithValue("@State", StateTextBox.Text)
            cmd.Parameters.AddWithValue("@Zip", ZipTextBox.Text)

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

        'Response.Redirect("default.aspx")

    End Sub

End Class

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Insert to DB & View Results on Different Page

Feb 19th, 2008

I'm a bit confused on this part, but are you trying to insert the new information, then redirect the page to a new ticket page, where most of the data is already loaded into the fields?

•

Join Date: Feb 2008

Posts: 179

Reputation: foundsheep is an unknown quantity at this point

Solved Threads: 0

foundsheep foundsheep is offline

Offline

Junior Poster

Re: Insert to DB & View Results on Different Page

Feb 19th, 2008

The info submitted on this page is needed before a new service ticket is created. So, once this has been inserted in the database I'd like it to automatically go to the insert new ticket form and the user will fill out the rest of the necessary info.

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Insert to DB & View Results on Different Page

Feb 19th, 2008

Okay, then where you have the response redirect at the end of your code, put this:

Response.Redirect("default.aspx?id=" & cusID)

Replace default.aspx with whatever page it is going to. Then on that following page have something like this:

Help with Code Tags

(Toggle Plain Text)

Dim id As String = Trim(Request.QueryString("id"))

Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("HRIServiceConnectionString1")
Dim cmd As New SqlCommand("SELECT * FROM Customers WHERE cusID=@cusID", conn)
cmd.Parameters.AddWithValue("@cusID", id)
Try
  conn.Open()
  Dim dtrReader As SqlDataReader = cmd.ExecuteReader()
  if dtrReader.HasRows then
    'found record with that id
    while dtrReader.Read()
      'set your textboxes here
      tbCompanyname.Text = dtrReader("Company")
      tbAddress1.Text = dtrReader("address1")
      'this is sample code. follow the pattern
    end while
  else
    'no customer found for that id. redirect.
    response.redirect("default.aspx")
  end if
  dtrReader.Close()
  conn.Close()
Catch ex As SqlException
  response.write(ex)
End Try

Last edited by SheSaidImaPregy; Feb 19th, 2008 at 10:38 am.

•

Join Date: Feb 2008

Posts: 179

Reputation: foundsheep is an unknown quantity at this point

Solved Threads: 0

foundsheep foundsheep is offline

Offline

Junior Poster

Re: Insert to DB & View Results on Different Page

Feb 19th, 2008

Should this go in a new protected sub? BTW, this is on the same page with the drop downs we've been working on.

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Insert to DB & View Results on Different Page

Feb 19th, 2008

Ok good to know, let me see if I have this straight. You will have a page where they will add a company or become a customer, then it will lead to the dropdownlist page where the information should already be populated. Right?

•

Join Date: Feb 2008

Posts: 179

Reputation: foundsheep is an unknown quantity at this point

Solved Threads: 0

foundsheep foundsheep is offline

Offline

Junior Poster

Re: Insert to DB & View Results on Different Page

Feb 19th, 2008

Here is the structure:
Company DDL
Contact DDL
Add Customer/Company Link: (takes you to new page to fill out form)

Dispatch form that is populated by ddls or add Customer Page
This is the one that include the equipment ddl

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Insert to DB & View Results on Different Page

Feb 19th, 2008

Then after you fill out the Addcustomer/company form, it redirects you back to that page, where the information is pre-filled out with the information submitted on the form, right?

•

Join Date: Feb 2008

Posts: 179

Reputation: foundsheep is an unknown quantity at this point

Solved Threads: 0

foundsheep foundsheep is offline

Offline

Junior Poster

Re: Insert to DB & View Results on Different Page

Feb 19th, 2008

You got it.

•

Join Date: Sep 2007

Posts: 1,080

Reputation: SheSaidImaPregy is an unknown quantity at this point

Solved Threads: 68

SheSaidImaPregy SheSaidImaPregy is offline

Offline

Veteran Poster

Re: Insert to DB & View Results on Different Page

#10

Feb 19th, 2008

Ok. Then so on this page with the form, once you insert the information, redirect to the page back there with a querystring.

Response.Redirect("default.aspx?cusID=" & cusID & "&conID=" & conID)

Then on that page, (copied code from before) do this under page_load AFTER you populated the dropdownlists:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
Dim cusID As String = Trim(Request.QueryString("cusID"))
Dim conID As String = Trim(Request.QueryString("conID"))
DropDownList1.Items.FindByValue(cusID).Selected = True
Dim eventarg As New EventArgs
Call DropDownList1_SelectedIndexChanged(DropDownList1, eventarg.Empty)
DropDownList2.Items.FindByValue(conID).Selected = True
eventarg = New EventArgs
Call DropDownList2_SelectedIndexChanged(DropDownList2, eventarg.Empty)
Dim cusID As String = Trim(Request.QueryString("cusID"))
Dim conID As String = Trim(Request.QueryString("conID"))
DropDownList1.Items.FindByValue(cusID).Selected = True
Dim eventarg As New EventArgs
Call DropDownList1_SelectedIndexChanged(DropDownList1, eventarg.Empty)
DropDownList2.Items.FindByValue(conID).Selected = True
eventarg = New EventArgs
Call DropDownList2_SelectedIndexChanged(DropDownList2, eventarg.Empty)

You also need to change the INSERT to:

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
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)
 
            Dim conID As Integer = Conver.ToInt32(cmd.ExecuteScalar())
 
            cmd = New SqlCommand("INSERT INTO [Zip] ([cusID], [City], [State], [Zip]) VALUES (@cusID, @City, @State, @Zip)", conn)
            cmd.Parameters.AddWithValue("@cusID", cusID)
            cmd.Parameters.AddWithValue("@City", CityTextBox.Text)
            cmd.Parameters.AddWithValue("@State", StateTextBox.Text)
            cmd.Parameters.AddWithValue("@Zip", ZipTextBox.Text)
 
            cmd.ExecuteNonQuery()
        Finally
            If conn.State = System.Data.ConnectionState.Open Then conn.Close()
        End Try
 
        Response.Redirect("default.aspx?cusID=" & cusID & "&conID=" & conID)
 
    End Sub
 
End Class
 
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)

            Dim conID As Integer = Conver.ToInt32(cmd.ExecuteScalar())

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

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

        Response.Redirect("default.aspx?cusID=" & cusID & "&conID=" & conID)

    End Sub

End Class