Nested SQL Queries

Hey guys,

I'm new to DB programming in .net and am having trouble with trying to display an catalouge on a webpage.

I have created the following table:

category{id, title, level, parent}

id - identifier
title - text value
level - the level of the catalouge entry
parent - the parent of this category

In classic asp i would have neested a query e.g.

loop all db entries where level = 0
      current = rs(id).value
      loop all entries where parent = current
            display sub categories
      next
next

My current approach in .net uses a datareader to display the data gathered, but its not possible to have 2 data readers with the same connection. So how do i overcome this??

 

Many Thanks

Josh

0
joshgosselin
4/12/2006 10:59:21 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

2 Replies
745 Views

Similar Articles

[PageSpeed] 44
Get it on Google Play
Get it on Apple App Store

Hi Josh

We can't open 2 datareader with same connection simulteniously
U can do one thing that all db entries where level = 0 collect into an arraylist and
close the reader. now make a loop to iterate all the sub entries

arraylist=select all db entries where level = 0
loop arraylist
      current = arraylist(id).value
      loop all entries where parent = current
            display sub categories
      next
next

 


Manav Burghate
Software Engineer
0
Manav
4/14/2006 8:00:10 AM

Can you use nested query to do it from database end/

Like this:

SELECT    DISTINCT b.title, b.mylevel, b.parent
FROM   twoqueryTable b  INNER JOIN (SELECT     a.title, a.mylevel, a.parent
FROM         twoqueryTable a ) as c
ON c.parent = b.mylevel
WHERE  b.mylevel=0

 


Limno

0
limno
4/14/2006 7:08:29 PM
Reply:

Similar Artilces:

Can sql queries be nested in sql queries?
I am having problems with a template that uses the information from one sql query to generate a query on another table. Let me be more specific. The template in question receives a category ID value from a web page that provides a menu of several categories to choose from. Each Category ID represents several Items or products to choose from. When the template receives the category ID, the first thing it does is run a query on my item_category table (this table lists all of the items and associates them with the appropriate category). This query returns several itemIDs that are all...

SQL DataSource : Object Reference not set to an instance of an object
 Dear friendsI am getting this error from few days (it was not in past)when i add new "sqlDataSource" , I set the Server name, user name and password then i select the Database, when i click on "Test Connection" button, it does not give me any Error and it shows that Test connection Successful, but when i click "OK", It shows the Error box with the error "Object Reference not set to an instance of an object"From few days it is happening, I was seting the datasource without any problem in past.I am using SQL Server 2005Any one can help? aliusmankha...

C# Ado.net Sql query parameter query
Hi All, I'm trying to pass in a parameter value from an array in a loop that is used in a sql query and the results are populated to an xml file. The trouble is that I'm only getting the colums values in the outputted xml file. So I feel that the paramter is not being read. So can anyone help as I'm really stuck on this one. The code is as follows : public void DisplayUserInfo() { ArrayList UserIdArrayList = IdentifyUserID(); foreach(string ShowUserIDString in UserIdArrayList) { try { SqlConnection SqlConn = new SqlConnection(DBConnString)...

DataSource Nested Query??
I want to run a Select query based on the SelectedValue of a dropdownlist, but If a specific value is selected...I need a different result. Not sure how to write a nested query for SQL server using a SQLDataSource control. Any assistance would be greatly appreciated. Hello rock056,I would love to help you, but I'm not sure what you are getting at.  You can build nested queries in SQL without a problem:SELECT  Field1,  Field2FROM (  SELECT Field1, Field2, Field3 FROM MyTable WHERE Field6 < 100 ) SubQueryWHERE  SubQuery.Field3 > 500What do you mea...

Datasource Sql query
 Hi, I am a coldfusion developer, but have now moved over to ASP.NET using VS2008 Pro, so trying to learn it quickly. My problem is that I have created a datasource with a simple query and when I run it in the query builder it works fine, however when I run it on the page I get nothing. Here is my query    <asp:SqlDataSource ID="qAllTrucks" runat="server"         ConnectionString="<%$ ConnectionStrings:ConnectionString %>"         ProviderName="<%$ Con...

Nest SQL queries
Hi,I have the followind code, which selects product categories and writes them to a <div> then for each product category, it also gets the associated products and writes them to a list inside the relevant div, so my output would be something like: ProductCategory number 1First product in category 1Second product in category 1Third product in category 1Product Category number 2First product in category 2Second product in category 2etc etcThe code is like this:        SqlCommand comm_productcategory = new SqlCommand("SELECT productcategory_id,...

SQL
Apologies for asking such a simple question, but here goes. I have two tables, for the sake of argument call them a and b . create table a (id numeric, checkdate datetime) create table b (id numeric, a_id numeric, checkdate datetime) I want to update the a table with the most recent checkdate of any related b record. In Oracle, this would be update a ta set checkdate = (select max(checkdate) from b tb where tb.a_id = ta.id) When I try to run this in Sybase, I get: The column prefix 'ta' does not match with a table name or alias name used in the query. Either th...

SQL query that works in SQL Server Management Studio, but doesn't on .NET 2.0 page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasourc...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

Nested Loop in SQL Query
Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit          Day1         Day2           Day3        Day4          Day5Name1     25             45       &n...

.net + dynamic SQL-query
On our page we have texbox, dropdownlist, database and gridview for it.It DropDownList we have all names of columns from database (in our situation they are Item_Name, Item_Type,  City and Country). In database we have two more columns, but we do not need them in this task.And the task is to choose from DropDownList item/value and deliver it to SQL-query. We have done searching from database, but the column to search from is static, we want to make it dynamic, we want to choose from Dropdownlist column, from which we will search.We are making all in aspx, not in .cs code. We are not fam...

sql datasource and update query
well i m using asp.net 2005 and sqlserver 2005,,i have a page called as sme_insert.aspx thr which user can enter new records for sme_id,name,no agency_id ,,etc..i have used sqldatasource and formview for tht,,,well for agency_id i have replaced the textfield with a dropdown list and it displays the agency_id values from the table sme_master..till now everything is working fine,,whn i go for inserting a new record and select a value from the dropdownlist,the value doesnot gets inserted in the table,,so everything is working fine except the dropdownlist,,,another prob is tht the dropdown list ...

Select Query in SQL Datasource
Hi! I am using a oracle, I put this code (below) to a sqldatasource control (Select Query) SELECT A.part_no, (A.beg_invty_qty + A.qty) QTY, X.TT       FROM inventory_master A,     (SELECT C.part_no,TRUNC(((C.beg_invty_qty + C.qty) /      DECODE(SUM(D.NEED),0,1,SUM(D.NEED))),2) TT          FROM inventory_master C, pcs_header D      WHERE C.part_no = D.part_no       and (trunc(D.prodn_date) >= trunc(sysdate) and trunc(D.prodn...

DataList Object with SQL Query
Hi All.  I'm new to ASP and VB.net and I am trying to create a simple navigation menu from values populated from my department table in sqlexpress.  Here is what i have and don't know what i'm doing wrong.  Please help anyone.  This code is in NavLeft.ascx<%@ Control Language="VB" AutoEventWireup="false" CodeFile="NavLeft.ascx.vb" Inherits="User_Controls_NavLeft" %><asp:DataList ID="NavLeft1" Runat="server" Font-Names="Arial" Font-Size="Small"ForeColor="Silver" W...

Web resources about - Nested SQL Queries - asp.net.object-datasource

The Queries - Wikipedia, the free encyclopedia
In science , The Queries or simply "Queries" is the third book to English physicist Isaac Newton 's Opticks , with various numbers of Query sections ...

Publishers Can See What Graph Search Queries Led To Their Page
It seems Facebook is tracking Graph Search queries, allowing publishers to see what words typed into the search bar led to their website. Sister ...

Facebook adds search history to Activity Log so users can delete queries
Facebook today announced that it will start including search history in users’ Activity Logs. The feature will be rolled out to all users in ...

faroo_p2p: Six month after launch the FAROO Search API http://t.co/RQlyOBZC serves 24 million queries/month ...
faroo_p2p: Six month after launch the FAROO Search API http://t.co/RQlyOBZC serves 24 million queries/month. A tenfold increase in the last month. ...

App Store - IDdx: Infectious Disease Queries
Get IDdx: Infectious Disease Queries on the App Store. See screenshots and ratings, and read customer reviews.

Michael Jackson queries - Google - Flickr - Photo Sharing!
Explore Search Engine Land's photos on Flickr. Search Engine Land has uploaded 1714 photos to Flickr.

'Safari UniBar' Brings Searches and Web Queries to One Unified Bar - YouTube
Retweet: http://clicktotweet.com/27wfR Name: Safari UniBar Description: Combined web queries and search from one bar, just like Google Chrome. ...

Gold Coast queries AFL's values as academy stoush intensifies
Gold Coast chairman John Witheriff&#10003; has questioned the AFL&#8217;s business values and accused it of a lack of courtesy in the decision ...

Malcolm Turnbull queries Foxtel's free content
Communications Minister Malcolm Turnbull is preparing to rip up a controversial clause in the retransmission regime in a move that could force ...

Firefox 14 protects search queries from 'bad guys,' not advertisers
Firefox 14 protects search queries from 'bad guys,' not advertisers Australian Macworld However, search engine guru Danny Sullivan from Search ...

Resources last updated: 12/16/2015 7:57:35 PM