Populating an ASP datagrid

lukin

I have googled for long enough on this problem and can't find a solution anywhere so I am posting here and I hope someone can help.
I have a grid on an ASP page that I want to be populated by a stored procedure when the page loads.
That's fairly easy except that the arguments that are passed to the stored procedure are in an Arraylist and are different values each time.


The stored procedure is this:

ALTER PROCEDURE dbo.Load_Double_Hotels(@HotelID Varchar(50)) 
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	
AS

BEGIN
SELECT * FROM Hotels WHERE HotelID = @HotelID 
	/* SET NOCOUNT ON */ 
	RETURN
END

As you can see, I just want the grid to be populated with everything from the hotel table where the hotel id is equal to what is passed in (@HotelID)




The backend code of the ASP page is C# and I have just added values to the Arraylist in code to make it simple (that's not actually how it will work when I have it running but that's nothing to do with my problem).
I have a loop that runs through the arraylist (testList), adding each value to the stored procedure at each loop.
ie:first loop:give me everything from the hotel table where hotel id is equal to "1"
second loop:give me everything from the hotel table where hotel id is equal to "2"
third loop:give me everything from the hotel table where hotel id is equal to "6"
etc. etc. until it reaches the end of the arraylist.

I thought adding command.Parameters.Clear(); after each pass through of the Array list would fix it but it just returns an empty grid (you can see where I have it commented out).
Leaving it the way it is gives me an exception stating that the stored proc has been given too many arguments (ie it's passing each of the hotel ids all at once)

public void Page_Load(object sender, EventArgs e)
    {

        testList.Add(1);
        testList.Add(2);
        testList.Add(6);
        testList.Add(7);
        testList.Add(8);
        testList.Add(9);
       

       try
{    
       


                SqlConnection cnn = new SqlConnection(ConfigurationSettings.AppSettings["CnnStr"]);
                SqlCommand command = new SqlCommand("dbo.Load_Double_Hotels", cnn);
                command.CommandType = CommandType.StoredProcedure;
               
                cnn.Open();
                command.Connection = cnn;

//here I start a loop to pass in the values from the ArrayList
                for (int i = 0; i < testList.Count; i++)
                {
                    int z = Convert.ToInt32(testList[i]);
                    command.Parameters.Add(new SqlParameter("@HotelID", z));
                   // command.Parameters.Clear();

                }



                SqlDataAdapter da = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                da.Fill(ds);

                HotelDataGrid2.DataSource = ds;
                HotelDataGrid2.DataBind();

            
        }
        catch (Exception excep)
        {
            
        }
        finally
        {
           

            if (cnn != null)
          {
              cnn.Close();
               cnn.Dispose();
            }
        }
           

        }

        }

I don't know C# all that well so apologies if there is a simple solution to this, but I certainly couldn't find it!

stevenmu

The sqlcommand object (and therefore the stored procedure) isn't executed until you call da.Fill(). At this point you have set up the command to call the stored procedure with multiple parameters (or with just hotelid = 9). Each time you are going to call fill you need to be clearing the parameters on the command and setting them up again.

Also, iirc each time you call fill() it will clear out the datatables to be populated (or possibly give an error if they already have data). If you want to get back results for multiple HotelID values you should set up your stored procedure that way.

lukin

stevenmu wrote: »

The sqlcommand object (and therefore the stored procedure) isn't executed until you call da.Fill(). At this point you have set up the command to call the stored procedure with multiple parameters (or with just hotelid = 9). Each time you are going to call fill you need to be clearing the parameters on the command and setting them up again.

Also, iirc each time you call fill() it will clear out the datatables to be populated (or possibly give an error if they already have data). If you want to get back results for multiple HotelID values you should set up your stored procedure that way.

I changed the for loop to this;

 for (int i = 0; i < testList.Count; i++)
               {
                    int z = Convert.ToInt32(testList[i]);
                    command.Parameters.Add(new SqlParameter("@HotelID", z));
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    HotelDataGrid2.DataSource = ds;
                    HotelDataGrid2.DataBind();
                    command.Parameters.Clear();

                }

As you were saying, da.fill() clears out the grid so I only get the grid to display the values for hotel id 9 (the last value from the arraylist).
It looks like my stored proc is the problem, I need to change that perhaps?

mewso

No need to use a stored procedure here at all or a dataset for that matter. A datareader would be sufficient. Loop through your arraylist and create a comma delimited string so you can use the IN keyword in your query:-

"SELECT * FROM Hotels WHERE HotelID in (" + idDelimitedString + ")"

Thats assuming the list of id values aren't coming from a query string or some other source that might expose this to sql injection.

lukin

musician wrote: »

No need to use a stored procedure here at all or a dataset for that matter. A datareader would be sufficient. Loop through your arraylist and create a comma delimited string so you can use the IN keyword in your query:-

"SELECT * FROM Hotels WHERE HotelID in (" + idDelimitedString + ")"

Thats assuming the list of id values aren't coming from a query string or some other source that might expose this to sql injection.

Yeah I know I could do it that way but the site it's on uses stored procs everywhere else so I want to keep it kind of consistent. I don't want any SQL statements in the back-end code anywhere.
The site is also a kind of learning experience so I don't want to take any shortcuts.
From what stevenmu said it seems I need to change the stored proc not the C# so I'll have a look at that. Thanks for your reply however.

mewso

lol though at the idea that not using a stored procedure is a short cut. There is a myth out there that stored procedures are somehow superior to paramaterised queries which is essentially false. The old argument of the execution plan being cached is redundant unless you are using Sql Server 6 or earlier since paramaterised queries are also cached in every version since.

lukin

I think I know what I need to do now.
What I want to do is pass an Arraylist of values to a stored proc, with a different one being executed each time.
I can't pass the Arraylist to the stored proc, so I need to convert it to a string with each number separated by a comma.
Then I need to pass this string to the stored proc.
Inside the stored proc I need a loop to get each value after each comma and return the appropriate value(s) from the table.