Glad it's not just me then :)

If you are trying to populate dropdown boxes based on the choice in the first and maybe subsequent ones one approach is to just grab all the data at the first request and manipulate on the client. This is a good choice if the dataset is not too large and the data is basically read only. Otherwise you pass the results of dropdown combination on each choice. I can see that the latter could use an array but with only five choices it would seem easier to use a five (one fewer than the number of dropdowns) parameter stored procedure. The first time you pass the first dropdown choice and leave other parameters null, the second time you pass the first two choices and leave the rest null etc.
Perhaps you need multiple select dropdowns? Then you would have to use a pseudo-array, each parameter holding the choices of the dropdowns. If this is the case and you can't retrieve all the data at once then look at the link I posted above for some examples of handling arrays.

--

Joe (Microsoft MVP - XML)

You may also want to think about the load balancing issues on your approach. You can only have one sql server. Generally its a good idea to offload the fine manipulation of data to the web server(different box) reducing the load on the SQL server, thus balancing the load more between the web server box and the sql server box. If they are the same box, it's still a good idea for when you move your web server off the SQL box or vice versa. The type of thing your trying to do is probably best handled on the web server.

That's good info, Rob, but what's that got to do with identifying the information to be passed? We're trying to figure out what msbsam is doing... it doesn't sound like he/she needs to pass an array into SQL Server at all but there's a bit of a language barrier problem... and I don't understand why msbsam thinks an array needs to be passed.

--Jeff Moden

O.o I dislike language barries, it would be wonderful if the entire world spoke one language =\ Anyway.

msbsam, I think what you are saying is that you have a GUI with a bunch of dropdowns and, as you work your way down, the item you select in X drop down affects the values displayed in the subsequent dropdownlist.

Based on this statement you made:I DONT WANT TO MAKE ARRAY INSIDE MY C# what I assume is that you are looking for an alternative way to bind a dataset to dropdown list, am I correct in thinking that?

for example I think you might be doing someting like this:

ArrayList arr = new ArrayList();
//populate data
dropdown1.datasource arr;
...

And this is causing your application to slow down, is that correct?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

I have a similar problem I am attempting to tackle... have a slideshow database and am trying to resort the slides because the user sometimes decides to delete one right out of the middle. Here's the code I have so far. The problem is I can't seem to SORT BY the sort_order column prior to the sequence number being assinged. I tried throwing an ORDER BY in the COUNT(SELECT... ORDER BY) but then SQL kicks back an error telling me I violated it's rules).

SELECT (SELECT COUNT(PK_ID)
                       FROM slideshow_slide AS x
                       WHERE x.PK_ID <= y.PK_ID) AS sequence, PK_ID, sort_order
FROM slideshow_slide y
WHERE (slideshowID = '3')
ORDER BY sequence

What I am trying to do but SQL doesn't like is:

SELECT (SELECT COUNT(PK_ID)
                       FROM slideshow_slide AS x
                       WHERE x.PK_ID <= y.PK_ID ORDER BY sort_order) AS sequence, PK_ID, sort_order
FROM slideshow_slide y
WHERE (slideshowID = '3')
ORDER BY sequence

What is the work around other than creating and dropping tables?

The connection to arrays escapes me.
Why not just order by PK_ID? Or if you are using SQL Server 2005 then use the ROW_NUMBER function.

--

Joe (Microsoft MVP - XML)

Can't sort by PK_ID because user deletes a row (ie slide 25 of 50) then creates a new row (identity becomes 51) but user wants the slide to be #25, so they set the sort order to 25, but you order by PK_ID then it shows up at the end of the list. Don't have SQL 2005, that's why the post is here in the SQL 2000 area.

SQL 2000 and PK_ID is the identity and increments by each time a new record is added, so if you delete a record (ie 25) and then add a new one, the new one would be 51, so sorting by PK_ID doesn't fly.

In that case I can't see how the count of rows with a lower id will help. When a user deletes a row and adds a new one what gets put in the sortorder field?
Can you show an example of a before and after table with a few rows?

--

Joe (Microsoft MVP - XML)

Well, when a user is deleting an entry, there is a gap in the sort_order, ie record 25 is missing. So the point of the sequence column is to resort the data with numbers that start at 1 and increment by 1 without any gaps, like an ASP array but without relying on ASP because it's so slow and a resource hog. The sequence number generated becomes the page number so they need to be in order, without gaps.