Connecting two FormViews

Filespit · April 30th, 2010, 07:07 AM

Hello!

I have a question about FormViews.

In my web application I have two FormViews (FormView1 and FormView2). Both FormViews are connected to a database; FormView1 uses Insert to send data to the database, and FormView2 uses Select to select data from the database.

In FormView1 I have a TextBox called ZipCode. When you type in a number (a zipcode) in that TextBox, you can send it to it's respective row in the database by clicking Insert. The thing is that you must type in a ZipCode that already exists in the database or else it won't work.

What I would like to do is the following; when I type something in the ZipCode TextBox (in FormView1) I would like FormView2 to select this very ZipCode as well as it's related attributes and display it.

Is this possible to do? If yes, how do I do it?

This is not related to any specific chapter in the book.

Cheers
//Peter

AnninCT · April 30th, 2010, 10:42 AM

Hi Peter,
I am in the middle of an ASP course using the same book. Formview1 is the one that inserts correct? If that zip code is written to the database when the onclick event happens for formview1, maybe you can save it in a variable or reload formview2 just after the insert happens for the record or field from formview1?

Also, are you writing in VB or C#?

Ann

Filespit · April 30th, 2010, 10:49 AM

Hi there!

Forgot to tell, I'm writing in VB. I'm currently trying to fix this using AJAX, but I've never used that before so any help here would be much appreciated!

But that is correct, FormView1 inserts data into the database. I want FormView2 to grab what you write in FormView1 (the ZipCode) + the attributes related to the ZipCode (such as City, Workshop etc). Also, I don't want FormView2 to display anything before you've typed in a ZipCode in FormView1.

As mentioned, any help is much appreciated!

//Peter

AnninCT · April 30th, 2010, 11:15 AM

HI Peter,
I just tried to do the application that you mention and was unable to. I tried messing around with the PageIndexChanging event but was unsuccessful. Sorry - Im still new at this myself and have been working all night on a website with master pages and themes that users can alter for a final project that am probably too sleepy to think of a good way to do this, or an too much of a newb. Just finished my project so after I sleep for a while will try it again - have the site all set up with formviews and a data source all set up. Maybe someone who really knows what they are doing will see this and respond before I nap for a few.
good luck!
Ann

Imar · May 1st, 2010, 04:04 AM

Hi ,

Quote:

This is not related to any specific chapter in the book.

In that case, please post these kind of messages in a more generic ASP.NET Category here: http://p2p.wrox.com/asp-net-3-5-436/ Makes it easier for readers to find what they are looking for here (stuff about this book) and will also help you attract more readers in the other forums.

Anyway, what you want can be done, but isn't necessarily easy.

Here's the general idea:

1. Find the TextBox in the first FormView, hook up its TextChanged event and set AutoPostback to True

2. On the server, in the TextChanged handler, get the entered text and rebind FormView2.

To make this easier for the user you could wrap the controls in an UpdatePanel so the update goes a lot smoother.

I may be able to cook up something later this weekend to show you how it's done. In the meantime, can you show me a simplified version of your database schema and the code you're using now for the FormViews and data source controls? (E.g. whether you're LINQ, or direct SQL and so on).

Cheers,

Imar

Filespit · May 1st, 2010, 04:57 PM

Hi Imar!

I apologize for not posting in the right area, will do so next time!

Anyway, I have just tried a tutorial for UpdatePanels and it works fine. Though I'm not entirely sure how I'm going to implement it into my code.

I will try to show you my database schema and code in a simplified version here:

The database in simplified version:

Table name (table attributes), * = Primary Key

Customer (CustomerID*, fName, lName, ZipCode)
PostalAdress (ZipCode*, wName)
Workshop (wName*, ZipCode)
Booking (BookingID*, CustomerID)
BookingOption (ReferenceNr*)

You can ignore Booking and BookingOption for now.

Relations in the database:

FK_PostalAdress_Workshop
FK_Customer_PostalAdress
FK_Booking_Customer
FK_Booking_BookingOption

The code for FormView1:

Code:

<asp:FormView ID="FormView1" runat="server" DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" DefaultMode="Insert">
    <InsertItemTemplate>
        <asp:TextBox ID="FirstNameTextBox" runat="server" Text='<%# Bind("fName") %>' />
        <asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("lName") %>' />
        <asp:TextBox ID="ZipCodeTextBox" runat="server" Text='<%# Bind("ZipCode") %>' />
        <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
            CommandName="Insert" Text="Insert" />
        <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False"
            CommandName="Cancel" Text="Cancel" />
    </InsertItemTemplate>
    <ItemTemplate>
        CustomerID:<asp:Label ID="CustomerIDLabel" runat="server" Text='<%# Eval("CustomerID") %>' />
        fName:<asp:Label ID="fNameLabel" runat="server" Text='<%# Bind("fName") %>' />
        lName:<asp:Label ID="lNameLabel" runat="server" Text='<%# Bind("lName") %>' />
        ZipCode:<asp:Label ID="ZipCodeLabel" runat="server" Text='<%# Bind("ZipCode") %>' />
        <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New" />
    </ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
	InsertCommand="INSERT INTO Customer(fName, lName, ZipCode) VALUES (@fName, @lName, @ZipCode)">
    <InsertParameters>
        <asp:Parameter Name="fName" />
        <asp:Parameter Name="lName" />
        <asp:Parameter Name="ZipCode" />
    </InsertParameters>
</asp:SqlDataSource>

The code for FormView2:

Code:

<asp:FormView ID="FormView2" runat="server" DataSourceID="SqlDataSource2">
    <EditItemTemplate>
        <asp:TextBox ID="ZipCodeTextBox" runat="server" Text='<%# Bind("ZipCode") %>' />
        <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True"
            CommandName="Update" Text="Update" />
        <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False"
            CommandName="Cancel" Text="Cancel" />
    </EditItemTemplate>
    <ItemTemplate>
        <asp:Label ID="ZipCodeLabel" runat="server" Text='<%# Bind("ZipCode") %>' />
    </ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" 
ConnectionString="<%$ ConnectionStrings:myConnectionString %>" 
InsertCommand="INSERT INTO Customer(ZipCode) VALUES ()" 
SelectCommand="SELECT Workshop.*, ZipCode.*, Customer.ZipCode AS Expr1 FROM PostalAdress INNER JOIN Workshop ON PostalAdress.wName = Workshop.wName INNER JOIN Customer ON PostalAdress.ZipCode = Customer.ZipCode">
</asp:SqlDataSource>

I wouldn't be surprised if I missed something here, but I guess this is what a simplified version of the project would look like.

Hope this can make you get a better picture.

Cheers
//Peter

Imar · May 2nd, 2010, 09:54 AM

Hi Peter,

Based on your description I created a small demo page. I am not 100% sure about your requirements and datamodel, so I may have oversimplified things.

I modified the second form view a bit to display some fields from the Workshop and PostalAddress tables. Additionally, I changed the SELECT statement to the following:

Code:

SELECT PostalAddress.ZipCode, PostalAddress.wName AS Postalname, Workshop.wName AS WorkshopName FROM PostalAddress INNER JOIN Workshop ON PostalAddress.ZipCode = Workshop.ZipCode WHERE (PostalAddress.ZipCode = @ZipCode)

This queries some fields from the two tables based on @ZipCode. @ZipCode is a parameter defined in the SqlDataSource control like this:

Code:

<SelectParameters>
  <asp:Parameter DefaultValue="" Name="ZipCode" Type="String" />
</SelectParameters>

The TextBox in the first FormView has AutoPostBack set to true and is set up to fire its TextChanged event on the server when you enter a Zip Code and hit tab. When that happens, the following code fires:

Code:

Protected Sub ZipCodeTextBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs)
  Dim myTextBox As TextBox = DirectCast(sender, TextBox)
  SqlDataSource2.SelectParameters(0).DefaultValue = myTextBox.Text
  SqlDataSource2.DataBind()
End Sub

This takes the entered ZipCode (from the sender TextBox), assigns it to the first (and only) parameter of the SqlDataSource2 control and then calls DataBind. This causes the second FormView to fire its SelectCommand which in turn gets the data from the PostalAddress and Workshop tables based on the ZipCode that it gets passed. Here's the full code for the page:

Code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
 <title></title>
</head>
<body>
 <form id="form1" runat="server">
 <div>
  <asp:FormView ID="FormView1" runat="server" DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" DefaultMode="Insert">
   <InsertItemTemplate>
    Fname
    <asp:TextBox ID="FirstNameTextBox" runat="server" Text='<%# Bind("fName") %>' />
    LName
    <asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("lName") %>' />
    ZipCode
    <asp:TextBox ID="ZipCodeTextBox" runat="server" Text='<%# Bind("ZipCode") %>' AutoPostBack="True" OnTextChanged="ZipCodeTextBox_TextChanged" />
    <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" />
    <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />
   </InsertItemTemplate>
   <ItemTemplate>
    CustomerID:<asp:Label ID="CustomerIDLabel" runat="server" Text='<%# Eval("CustomerID") %>' /> fName:<asp:Label ID="fNameLabel" runat="server" Text='<%# Bind("fName") %>' /> lName:<asp:Label ID="lNameLabel" runat="server" Text='<%# Bind("lName") %>' /> ZipCode:<asp:Label ID="ZipCodeLabel" runat="server" Text='<%# Bind("ZipCode") %>' />
    <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New" />
   </ItemTemplate>
  </asp:FormView>
  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" InsertCommand="INSERT INTO Customer(fName, lName, ZipCode) VALUES (@fName, @lName, @ZipCode)">
   <InsertParameters>
    <asp:Parameter Name="fName" />
    <asp:Parameter Name="lName" />
    <asp:Parameter Name="ZipCode" />
   </InsertParameters>
  </asp:SqlDataSource>
  <asp:FormView ID="FormView2" runat="server" DataSourceID="SqlDataSource2">
   <EditItemTemplate>
    <asp:TextBox ID="ZipCodeTextBox" runat="server" Text='<%# Bind("ZipCode") %>' />
    <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />
    <asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />
   </EditItemTemplate>
   <ItemTemplate>
    Zip: <asp:Label ID="ZipCodeLabel" runat="server" Text='<%# Bind("ZipCode") %>' /><br />
    Postal name <asp:Label ID="Label1" runat="server" Text='<%# Bind("PostalName") %>' /><br />
    Workshop name<asp:Label ID="Label2" runat="server" Text='<%# Bind("WorkshopName") %>' /><br />
   </ItemTemplate>
  </asp:FormView>
  <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="SELECT PostalAddress.ZipCode, PostalAddress.wName AS Postalname, Workshop.wName AS WorkshopName FROM PostalAddress INNER JOIN Workshop ON PostalAddress.ZipCode = Workshop.ZipCode WHERE (PostalAddress.ZipCode = @ZipCode)">
<SelectParameters>
<asp:Parameter DefaultValue="" Name="ZipCode" Type="String" />
</SelectParameters>
  </asp:SqlDataSource>
 </div>
 </form>
</body>
</html>

And here's the code behind:

Code:

Partial Class _Default
 Inherits System.Web.UI.Page
 Protected Sub ZipCodeTextBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs)
  Dim myTextBox As TextBox = DirectCast(sender, TextBox)
  SqlDataSource2.SelectParameters(0).DefaultValue = myTextBox.Text
  SqlDataSource2.DataBind()
 End Sub
End Class

To make it easier to try it out and see what I've done based on my (slightly) different database model, you can download my full project here:

http://Imar.Spaanjaars.Com/Downloads..._FormViews.zip

Hope this helps.

Cheers,

Imar

Filespit · May 2nd, 2010, 01:43 PM

Hi Imar!

I downloaded your project and it works fine. It was exactly what I was looking for. However, when I try to implement it into my code, there is one problem that occurs. My code is more or less identical to yours, with a few differences of course as I gave you a simplified version.

The thing is, my two FormViews are each placed in a Panel, that is not visible from the start. These become visible once I select something in a RadioButtonList.

The aspx code:

Code:

<asp:RadioButtonList ID="Customer" runat="server" AutoPostBack="True">
    <asp:ListItem>PrivateCustomer</asp:ListItem>
    <asp:ListItem>BusinessCustomer</asp:ListItem>
</asp:RadioButtonList>

<asp:Panel ID="PrivateCustomer" runat="server">
    <asp:FormView ID="FormView1" runat="server" DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" DefaultMode="Insert">
    "The FormView1 code here"
    </asp:FormView>
    <asp:SqlDataSource>
    </asp:SqlDataSource>
</asp:Panel>

<asp:Panel ID="Workshop" runat="server">
    <asp:FormView ID="FormView2" runat="server" DataSourceID="SqlDataSource2">
    "The FormView2 code here"
    </asp:FormView>
    <asp:SqlDataSource>
    </asp:SqlDataSource>
</asp:Panel>

The VB code:

Code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
PrivateCustomer.Visible = False
Workshop.Visible = False

If Not Customer.SelectedItem Is Nothing Then
    If Customer.SelectedItem.Value = "PrivateCustomer" Then
        PrivateCustomer.Visible = True
    Else
    End If
End If

Once FormView1 (placed in the PrivateCustomer Panel) is visible, I start typing in some values in the TextBoxes, and once I've typed something in a ZipCode, the page loads but shows nothing. Is this because of the Panels or something else?

Hope you understand what I mean.

//Peter

Imar · May 2nd, 2010, 02:35 PM

You're never setting Workshop's Visible property to True. You could that in the handler I showed you earlier that fires when the text is changed.

Cheers,

Imar

Filespit · May 2nd, 2010, 03:13 PM

Hello again,

I think I just partly managed to solve it. I deleted the Panel for FormView2, and added a WHERE to the query. When I type in a ZipCode in the TextBox, it is displayed along with the correct adress and such. However, it only works for a few ZipCodes, and not all. Not sure why, but I'm gonna settle with this for now.

Now I have a question about the Insert buttons (LinkButton). The button is placed inside the InsertItemTemplate, and when I click it the values are sent to the database. Is it possible to move this button outside the template?

I am building a form for a booking system, and I am using several FormViews to send different sorts of data to the database (they all work). But each FormView has an Insert button, which gives me several of these buttons. So I want to turn all these Insert buttons into one. Is this possible? If yes, how do I do it?

Cheers
//Peter