473,837 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query based on data in another dataset

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="d sCustomer">
<ItemTemplate >
First Name:
<asp:Label ID="lblFirstNam e" runat="server" Text='<%#
Bind("FirstName ") %>'></asp:Label><br />
Family Name:
<asp:Label ID="lblFamilyNa me" runat="server" Text='<%#
Bind("FamilyNam e") %>'></asp:Label><br />
City:
<asp:Label ID="lblCity" runat="server" Text='<%# Bind("City")
%>'></asp:Label>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSou rce ID="dsCustomer " runat="server" />

<asp:GridView ID="UsingQuery2 " runat="server"
DataSourceID="d sOrders" GridLines="None ">
<Columns>
<asp:BoundFie ld DataField="Orde rNum" HeaderText="Ord er Num" />
<asp:BoundFie ld DataField="Name " HeaderText="Nam e" />
<asp:BoundFie ld DataField="Orde rDate" HeaderText="Ord er Date" />
</Columns>
</asp:GridView>
<asp:SqlDataSou rce ID="dsOrders" runat="server" />
..aspx.vb:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
dsCustomer.Conn ectionString =
System.Web.Conf iguration.WebCo nfigurationMana ger.ConnectionS trings.Item("In tranetConnectio nString").Conne ctionString
dsCustomer.Sele ctCommand = "HR_GetCustomer "
dsCustomer.Sele ctCommandType =
SqlDataSourceCo mmandType.Store dProcedure
dsCustomer.Sele ctParameters.Ad d("CustomerID ", TypeCode.Int32,
Request.Queryst ring("CustomerI D"))

dsOrders.Connec tionString =
System.Web.Conf iguration.WebCo nfigurationMana ger.ConnectionS trings.Item("In tranetConnectio nString").Conne ctionString
dsOrders.Select Command = "Select OrderNum, Name, OrderDate From
Orders Where CompanyID = @CompanyID"
dsOrders.Select CommandType = SqlDataSourceCo mmandType.Text
dsOrders.Select Parameters.Add( "CompanyID" , TypeCode.Int32,
dsApplicationDe tails.SelectPar ameters("Compan yID").ToString )
End Sub
I get the null reference/pointer? exception at the line before "End sub"
for the
dsApplicationDe tails.SelectPar ameters("Compan yID").ToString

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

Cheers
Scott
Feb 7 '06 #1
1 3501
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="d sCustomer">
<ItemTemplate >
First Name:<asp:Label ID="lblFirstNam e" runat="server" Text='<%#
Bind("FirstName ") %>'></asp:Label><br />
Family Name:<asp:Label ID="lblFamilyNa me" runat="server" Text='<%#
Bind("FamilyNam e") %>'></asp:Label><br />
City: <asp:Label ID="lblCity" runat="server" Text='<%# Bind("City")
%>'></asp:Label><br />
<!-- HIDDEN LABEL TO HOLD COMPANYID -->
<asp:label id="lblCompanyI d" runat="server" visible="false" text='<%@
Eval("CompanyId ")%>' /><br />
<!-- ORDERS GRIDVIEW INSIDE CUSTOMER FORMVIEW -->
<asp:GridView ID="UsingQuery2 " runat="server" DataSourceID="d sOrders"
GridLines="None ">
<Columns>
<asp:BoundFie ld DataField="Orde rNum" HeaderText="Ord er Num" />
<asp:BoundFie ld DataField="Name " HeaderText="Nam e" />
<asp:BoundFie ld DataField="Orde rDate" HeaderText="Ord er Date" />
</Columns>
</asp:GridView>
<!-- DATASOURCE FOR ORDERS GRIDVIEW -->
<asp:sqldatasou rce
id="dsOrders"
runat="server"
connectionstrin g=<%$ ConnectionStrin gs:IntranetConn ectionString %>"
selectcommand=" Select OrderNum, Name, OrderDate From orders Where
CompanyID = @CompanyID">
<selectparamete rs>
<asp:controlpar ameter controlid="lblC ompanyId" name="CompanyId "
propertyname="T ext" />
</selectparameter s>
</asp:sqldatasour ce>
</ItemTemplate>
</asp:FormView>

<!-- DATASOURCE FOR CUSTOMER FORMVIEW -->
<asp:sqldatasou rce
id="dsCustomer "
runat="server"
connectionstrin g="<%$ ConnectionStrin gs:IntranetConn ectionString %>"
selectcommand=" HR_GetCustomer"
selectcommandty pe="StoredProce dure">
<selectparamete rs>
<asp:querystrin gparameter name="CustomerI D"
querystringfiel d="UserId" />
</selectparameter s>
</asp:sqldatasour ce>

Hope this helps.

-Phil

"SomebodyEl se" wrote:
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="d sCustomer">
<ItemTemplate >
First Name:
<asp:Label ID="lblFirstNam e" runat="server" Text='<%#
Bind("FirstName ") %>'></asp:Label><br />
Family Name:
<asp:Label ID="lblFamilyNa me" runat="server" Text='<%#
Bind("FamilyNam e") %>'></asp:Label><br />
City:
<asp:Label ID="lblCity" runat="server" Text='<%# Bind("City")
%>'></asp:Label>
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSou rce ID="dsCustomer " runat="server" />

<asp:GridView ID="UsingQuery2 " runat="server"
DataSourceID="d sOrders" GridLines="None ">
<Columns>
<asp:BoundFie ld DataField="Orde rNum" HeaderText="Ord er Num" />
<asp:BoundFie ld DataField="Name " HeaderText="Nam e" />
<asp:BoundFie ld DataField="Orde rDate" HeaderText="Ord er Date" />
</Columns>
</asp:GridView>
<asp:SqlDataSou rce ID="dsOrders" runat="server" />
..aspx.vb:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
dsCustomer.Conn ectionString =
System.Web.Conf iguration.WebCo nfigurationMana ger.ConnectionS trings.Item("In tranetConnectio nString").Conne ctionString
dsCustomer.Sele ctCommand = "HR_GetCustomer "
dsCustomer.Sele ctCommandType =
SqlDataSourceCo mmandType.Store dProcedure
dsCustomer.Sele ctParameters.Ad d("CustomerID ", TypeCode.Int32,
Request.Queryst ring("CustomerI D"))

dsOrders.Connec tionString =
System.Web.Conf iguration.WebCo nfigurationMana ger.ConnectionS trings.Item("In tranetConnectio nString").Conne ctionString
dsOrders.Select Command = "Select OrderNum, Name, OrderDate From
Orders Where CompanyID = @CompanyID"
dsOrders.Select CommandType = SqlDataSourceCo mmandType.Text
dsOrders.Select Parameters.Add( "CompanyID" , TypeCode.Int32,
dsApplicationDe tails.SelectPar ameters("Compan yID").ToString )
End Sub
I get the null reference/pointer? exception at the line before "End sub"
for the
dsApplicationDe tails.SelectPar ameters("Compan yID").ToString

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

Cheers
Scott

Feb 7 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
4043
by: Phin | last post by:
I need your HELP! I've seen all the posts on using Crystal Reports within vs.net (vb.net) and changing a SQL query at runtime. When I tried to pass in a dataset into the crystal report at runtime, the report still showed the results from the default query (from within the Crystal Report). Then I tried the XSD solution where you define a dataset (that mataches the database and the Crystal Report) and have the Crystal Report use this....
1
6964
by: David | last post by:
Hi everyone. I have read every page that Google returns on this topic, but can't find anything that resolves my problem. Basically, I have an Access Database that does a number of different calculations. One of these calculations is rather complex, and could not be implemented properly using just nested IIF statements or anything similar, so was coded using VBA (in the VBE) as a function. This calculation is included in a query in the...
1
1942
by: dan_williams | last post by:
I am attempting to create a ASP.NET report whereby users can specify which columns they wish to be able to view depending on the options they select from a CheckBoxList. I have 4 tables, ClientGroups, Clients, Bookings & Depts. ClientGroups Clients Bookings -------------- ------------------ ------------------- cgId PK int ClientId PK int BookingId PK int cgName varchar ClientName...
10
15395
by: Marizel | last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I often find myself with a query which I'd like to reuse, but with a different datasource. These datasources generally have identical field names. The queries select a subset of the fields, so "Select *" is not really an option. Is there an easy way to change the source of a query, either in the design grid or SQL display? I suppose I could copy the SQL into WordPad...
4
302
by: Derek Van Cuyk | last post by:
Hi everyone! I'm trying to write a web application in school that utilizes a MS Access database. I can read and everything fine from it but when I try to add a new record I get an exception that states: "Operation must use an updateable query" I know it has to do with the update command on the OleDbDataAdapter. I've tried looking for the problem on the net and newsgroups but I don't seem to have the same problems as everyone else. Please,...
7
3319
by: KoliPoki | last post by:
Hello every body. I have a small issue. Problem: I have a table with 4 descriptor columns (type). I need to formulate a query to retrieve a count for each type so I can group by...etc. The view I have works, but doesn't work when I supplement the query with some functions... they just don't like the UNION. The real problem is I can't change any of the udf's or queries, just the view. The view is inner joined back on to the primary...
7
6725
by: Jlo | last post by:
Hi, I have a c# winforms application. When I call the report file, it shows me all the records in the table. How can I make it to call only a particular range. i have the following code Viewer1.ReportSource = Application.StartupPath + "//Label.rpt"; How can I assign it a dataset which have the data of a particular range.
7
2898
by: bryant | last post by:
Hi all. I am new to ASP and working in Expression Web. The following query displays the information I need in the gridview for a single record. SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM", "OE_HDR"."SLS_MAN_NO", "OE_HDR"."SLS_MAN_INITIALS", "OE_HDR"."ORD_DAT", "OE_HDR"."SHIP_DAT" FROM "OE_HDR" WHERE ("OE_HDR"."ORD_NO"='174310') I also have DropDownList1 working properly. For the WHERE portion of
9
5168
by: =?Utf-8?B?RnJhbmsgVXJheQ==?= | last post by:
Hi all On SQL Server tables I use SqlDataReader to query data like "SELECT * FROM table". Is there something similar for XML Files ? I want to use SQL Syntax like "SELECT * FROM table" on a XML file. After query I want to get something like SqlDataReader based on Query to loop. I do not want to use a DataSet. It should be more like streaming
0
9846
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9686
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10890
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10279
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7007
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5675
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5855
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4479
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4053
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.