Query based on data in another dataset

[SomebodyElse]

Hi.

Apologies if this has been asked here before - I've searched & searched
but can't find anything. It's probably my serach parameters, but I'm
having trouble even describing it to a search engine!

I'm sure this is a very simple problem, with straighforward solution. It
certainly is in classic asp/ado, but I'm finally learning this .NET
business. I like it, but can't get my head around some concepts. Anyway....

I need to retrieve a dataset with one (or possibly more) parameters
based on another dataset on the same page.

One of possibly many examples:
Returning a dataset from a database. Dataset contains only one row. One
column contains a value that needs to be passed to another Stored
Procedure or select statement to return another dataset to be used as
part of the same page.

When I pass this value to to the second stored procedure I get a null
reference exception, or null pointer exception, or something similar
(not at my work computer at the mo...)
I suspect this is because when I pass the value to the second procedure,
the first hasn't yet been executed, and so doesn't contain a value. What
do I need to do...?

Here is some (simplified) code I'm using. (as I said - I'm learning - so
it may look odd!!!)
Supposed to return details of a customer, then a datagrid of all orders
from the customers company.

..aspx:
<asp:FormView ID="UsingQuery1" runat="server" DataSourceID="dsCustomer">
<ItemTemplate>
First Name:
<asp:Label ID="lblFirstName" runat="server" Text='<%#
Bind("FirstName") %>'></asp:Label><br />
Family Name:
<asp:Label ID="lblFamilyName" runat="server" Text='<%#
Bind("FamilyName") %>'></asp:Label><br />
City:
<asp:Label ID="lblCity" runat="server" Text='<%# Bind("City")
%>'></asp:Label>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="dsCustomer" runat="server" />

<asp:GridView ID="UsingQuery2" runat="server"
DataSourceID="dsOrders" GridLines="None">
<Columns>
<asp:BoundField DataField="OrderNum" HeaderText="Order Num" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="dsOrders" runat="server" />


..aspx.vb:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
dsCustomer.ConnectionString =
System.Web.Configuration.WebConfigurationManager.ConnectionStrings.Item("IntranetConnectionString").ConnectionString
dsCustomer.SelectCommand = "HR_GetCustomer"
dsCustomer.SelectCommandType =
SqlDataSourceCommandType.StoredProcedure
dsCustomer.SelectParameters.Add("CustomerID", TypeCode.Int32,
Request.Querystring("CustomerID"))

dsOrders.ConnectionString =
System.Web.Configuration.WebConfigurationManager.ConnectionStrings.Item("IntranetConnectionString").ConnectionString
dsOrders.SelectCommand = "Select OrderNum, Name, OrderDate From
Orders Where CompanyID = @CompanyID"
dsOrders.SelectCommandType = SqlDataSourceCommandType.Text
dsOrders.SelectParameters.Add("CompanyID", TypeCode.Int32,
dsApplicationDetails.SelectParameters("CompanyID").ToString)
End Sub


I get the null reference/pointer? exception at the line before "End sub"
for the
dsApplicationDetails.SelectParameters("CompanyID").ToString

I'm fairly sure that's because dsCustomer doesn't yet contain anything,
but I can't find a dsCustomer.Execute() or .Open() or anything. What am
I missing here?

Cheers
Scott

 

[Guest]

I've run into something similar to this and found a little trick that should
help. Yes, your are correct that the second procedure errors out because it
is looking for a parameter value based on the a 'Selected Row' in your form.
However, you haven't actually selected a row in the formview, so that value
doesn't exist. Try this:

Change your SqlDataSources to be all declaritive (this is illustrated
below), instead of using codebehind.

Add to you FormView a hidden label to hold the CompanyId when the FormView
is filled.
<asp:label id="CompanyId" runat="server" visible="false" text='<%@
Eval("CompanyId")%>' />

Put your GridView and it's DataSource INSIDE your FormView template (since
they will be referencing the Label control that is in the template).

<asp:FormView ID="UsingQuery1" runat="server" DataSourceID="dsCustomer">
<ItemTemplate>
First Name:<asp:Label ID="lblFirstName" runat="server" Text='<%#
Bind("FirstName") %>'></asp:Label><br />
Family Name:<asp:Label ID="lblFamilyName" runat="server" Text='<%#
Bind("FamilyName") %>'></asp:Label><br />
City: <asp:Label ID="lblCity" runat="server" Text='<%# Bind("City")
%>'></asp:Label><br />
<!-- HIDDEN LABEL TO HOLD COMPANYID -->
<asp:label id="lblCompanyId" runat="server" visible="false" text='<%@
Eval("CompanyId")%>' /><br />
<!-- ORDERS GRIDVIEW INSIDE CUSTOMER FORMVIEW -->
<asp:GridView ID="UsingQuery2" runat="server" DataSourceID="dsOrders"
GridLines="None">
<Columns>
<asp:BoundField DataField="OrderNum" HeaderText="Order Num" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="OrderDate" HeaderText="Order Date" />
</Columns>
</asp:GridView>
<!-- DATASOURCE FOR ORDERS GRIDVIEW -->
<asp:sqldatasource
id="dsOrders"
runat="server"
connectionstring=<%$ ConnectionStrings:IntranetConnectionString %>"
selectcommand="Select OrderNum, Name, OrderDate From orders Where
CompanyID = @CompanyID">
<selectparameters>
<asp:controlparameter controlid="lblCompanyId" name="CompanyId"
propertyname="Text" />
</selectparameters>
</asp:sqldatasource>
</ItemTemplate>
</asp:FormView>

<!-- DATASOURCE FOR CUSTOMER FORMVIEW -->
<asp:sqldatasource
id="dsCustomer"
runat="server"
connectionstring="<%$ ConnectionStrings:IntranetConnectionString %>"
selectcommand="HR_GetCustomer"
selectcommandtype="StoredProcedure">
<selectparameters>
<asp:querystringparameter name="CustomerID"
querystringfield="UserId" />
</selectparameters>
</asp:sqldatasource>

Hope this helps.

-Phil