Post to Different Tables with Form View RSS

• • Reply
  

  foundsheep

  Member

  5 Points

  97 Posts

  Post to Different Tables with Form View

  Feb 07, 2008 02:27 PM|LINK

  I have a dispatch page that a user will be able to add a new company/contact if it is not in the database.  The company and contact info are in different tables. What is the best way to update both in one form?
  

  Learnin'
  
  Mark Rowan
  Web/IT Specialist
  Hackworth Reprographics
  hackworthrepro.com | hackworthimaging.com
• • Reply
  

  ecbruck

  All-Star

  98783 Points

  9691 Posts

  Moderator

  Re: Post to Different Tables with Form View

  Feb 07, 2008 02:35 PM|LINK

  I'd simply do this within the FormView.ItemInserting event. I'd retrieve the data for my first table from the FormView and do my insert retrieving back my key. Then I'd retrieve the data for my second table and then again do my insert. Also, you could wrap this in a transaction if you were so inclined.

  Thanks, Ed
  
  Microsoft MVP - ASP.NET
  
  
• • Reply
  

  foundsheep

  Member

  5 Points

  97 Posts

  Re: Post to Different Tables with Form View

  Feb 07, 2008 02:45 PM|LINK

   Ed,

  Thanks, since I'm not very familiar with .net, is there example, tutorial or something along those lines that you know of of?  I'm learning on the fly. 

  Learnin'
  
  Mark Rowan
  Web/IT Specialist
  Hackworth Reprographics
  hackworthrepro.com | hackworthimaging.com
• • Reply
  

  ecbruck

  All-Star

  98783 Points

  9691 Posts

  Moderator

  Re: Post to Different Tables with Form View

  Feb 07, 2008 03:18 PM|LINK

  Here's a real simple example minus the inserting of the second table.

  ASPX 

  <%@ page language="C#" masterpagefile="~/MasterPages/Default.master" autoeventwireup="true"
  	codefile="1216695.aspx.cs" inherits="Posts_GeneralASPNET_DataPresentationControls_1216695"
  	title="Untitled Page" %>
  
  
  <asp:content id="Content1" contentplaceholderid="ContentPlaceHolder1" runat="Server">
  	<asp:formview id="FormView1" runat="server" defaultmode="Insert"
  oniteminserting="FormView1_ItemInserting">
  		<insertitemtemplate>
  			Company Name:
  			<asp:textbox id="txtCompanyName" runat="server" /><br />
  			Phone:
  			<asp:textbox id="txtPhone" runat="server" /><br />
  			<br />
  			<asp:linkbutton id="btnInsert" runat="server" commandname="Insert" text="Insert" />
  		</insertitemtemplate>
  	</asp:formview>
  </asp:content>

  CODE-BEHIND 

  using System;
  using System.Configuration;
  using System.Data.SqlClient;
  using System.Web.UI.WebControls;
  
  public partial class Posts_GeneralASPNET_DataPresentationControls_1216695 : System.Web.UI.Page
  {
    	protected void FormView1_ItemInserting(object sender, FormViewInsertEventArgs e)
  	{
  		// Retrieve controls
  		TextBox txtCompanyName = FormView1.FindControl("txtCompanyName") as TextBox;
  		TextBox txtPhone = FormView1.FindControl("txtPhone") as TextBox;
  
  		if (txtCompanyName == null) { return; }
  		if (txtPhone == null) { return; }
  
  		// Set-up command
  		SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
  		SqlCommand cmd = new SqlCommand(
  			"INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone); SELECT SCOPE_IDENTITY();", 
  			conn);
  		cmd.Parameters.AddWithValue("CompanyName", txtCompanyName.Text);
  		cmd.Parameters.AddWithValue("Phone", txtPhone.Text);
  
  		try
  		{
  			conn.Open();
  			int shipperID = Convert.ToInt32(cmd.ExecuteScalar());
  
  			// Do your second insert here
  		}
  		finally
  		{
  			if (conn != null)
  			{
  				conn.Close();
  			}
  		}
  	}
  }

  Thanks, Ed
  
  Microsoft MVP - ASP.NET
  
  
• • Reply
  

  foundsheep

  Member

  5 Points

  97 Posts

  Re: Post to Different Tables with Form View

  Feb 07, 2008 03:35 PM|LINK

   This part confuses me.  What is the shipperID referring to?
  

   

  try
  		{
  			conn.Open();
  			int shipperID = Convert.ToInt32(cmd.ExecuteScalar());
  
  
  			// Do your second insert here
  		}

   
  

   

  Learnin'
  
  Mark Rowan
  Web/IT Specialist
  Hackworth Reprographics
  hackworthrepro.com | hackworthimaging.com
• • Reply
  

  ecbruck

  All-Star

  98783 Points

  9691 Posts

  Moderator

  Re: Post to Different Tables with Form View

  Feb 07, 2008 03:41 PM|LINK

  If you look at the end of my SqlCommand text, you'll see that I call a second SELECT which returns SCOPE_IDENTITY() which is the ID from the identity column for my previous INSERT.

  Thanks, Ed
  
  Microsoft MVP - ASP.NET
  
  
• • Reply
  

  foundsheep

  Member

  5 Points

  97 Posts

  Re: Post to Different Tables with Form View

  Feb 07, 2008 04:31 PM|LINK

  I coded it but I'm not familiar with inherits attribute and it is erroring out:

  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: ASPNET: Make sure that the class defined in this code file matches the 'inherits' attribute, and that it extends the correct base class (e.g. Page or UserControl).
  
  Source Error:
  
  

  Line 1: Imports System Line 2: Imports System.ConfigurationLine 3: Imports System.Data.SqlClient

   

  This is what I have on my .aspx page:

  <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="AddCall.aspx.vb" Inherits="Default" title="HRI Service Express" %>

  and the vb page:

  Imports System
  Imports System.Configuration
  Imports System.Data.SqlClient
  Imports System.Web.UI.WebControls
  
  Partial Public Class Posts_GeneralASPNET_DataPresentationControls_1216695
      Inherits System.Web.UI.Page
  

  Learnin'
  
  Mark Rowan
  Web/IT Specialist
  Hackworth Reprographics
  hackworthrepro.com | hackworthimaging.com
• • Reply
  

  ecbruck

  All-Star

  98783 Points

  9691 Posts

  Moderator

  Re: Post to Different Tables with Form View

  Feb 07, 2008 04:35 PM|LINK

  You have the Class name of my example when your ASPX page expects a Class name of Default.

  Partial Public Class Defaut

  Thanks, Ed
  
  Microsoft MVP - ASP.NET
  
  
• • Reply
  

  foundsheep

  Member

  5 Points

  97 Posts

  Re: Post to Different Tables with Form View

  Feb 07, 2008 04:42 PM|LINK

  That didn't do it.  I've got the same error.  Its says its on line one...

  Line 1:  Imports System

   

  Learnin'
  
  Mark Rowan
  Web/IT Specialist
  Hackworth Reprographics
  hackworthrepro.com | hackworthimaging.com
• • Reply
  

  foundsheep

  Member

  5 Points

  97 Posts

  Re: Post to Different Tables with Form View

  Feb 07, 2008 05:43 PM|LINK

  I cleared that error but it came back and I don't know why.  While it was cleared there were these errors on the aspx page like:

  Compiler Error Message: BC30456: 'InitializeCulture' is not a member of 'ASP.serviceexpress_addcall_aspx'. or 

  "Title" is not a member...

   

  Here is my vb code:

  Imports System
  Imports System.Configuration
  Imports System.Data.SqlClient
  Imports System.Web.UI.WebControls
  
  Partial Public Class Default
      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
          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())
  
              ' Do your second insert here
  
              Dim cmd As New SqlCommand("INSERT INTO [Contacts] ([FirstName], [LastName]) VALUES (@FirstName, @LastName); SELECT SCOPE_IDENTITY();", conn)
              cmd.Parameters.AddWithValue("FirstName", FirstNameTextBox.Text)
              cmd.Parameters.AddWithValue("LastName", LastNameTextBox.Text)
  
              Try
                  conn.Open()
  
  
                  Dim contactID As Integer = Convert.ToInt32(cmd.ExecuteScalar())
  
                  ' Do your third insert here
  
                  Dim cmd As New SqlCommand("INSERT INTO [Zip] ([City], [State], [Zip]) VALUES (@City, @State, @Zip); SELECT SCOPE_IDENTITY();", conn)
                  cmd.Parameters.AddWithValue("City", CityTextBox.Text)
                  cmd.Parameters.AddWithValue("State", StateTextBox.Text)
                  cmd.Parameters.AddWithValue("Zip", ZipTextBox.Text)
  
                  Try
                      conn.Open()
  
  
                      Dim zipID As Integer = Convert.ToInt32(cmd.ExecuteScalar())
  
                  Finally
                      If conn IsNot Nothing Then
                          conn.Close()
                      End If
                  End Try
      End Sub
  End Class
   

  Learnin'
  
  Mark Rowan
  Web/IT Specialist
  Hackworth Reprographics
  hackworthrepro.com | hackworthimaging.com
  • Edited by foundsheep on Feb 07, 2008 05:47 PM