Link to home
Create AccountLog in
Avatar of cbrick04
cbrick04

asked on

Select data from multiple rows from SQL

I have a Table(PicturesTable) with some URLS for some images,:

Image ID       PicURL            Page
----------        -----------       ------
1                  Image1             1
2                  Image2             1
3                  Image3             2
4                  Image4             2


I would like to pull the data and assign the URLS to some image buttons where the page column is = 1;

This is what I have so far, it works for one image , but when I add the second, I get an error "Index was outside the bounds of the array."

//Get Pic info from database
        SqlConnection cn1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PicturesConnectionString"].ConnectionString);
        SqlCommand cmd1 = new SqlCommand("Select PicURL  from [PicturesTable] where Page = '" + 1 + "'", cn1);
        cn1.Open();
        SqlDataReader reader1 = cmd1.ExecuteReader();
        while (reader1.Read())
        {
            Picture1  = reader1.GetValue(0).ToString();
            Picture2 = reader1.GetValue(1).ToString();
        }
        reader1.Close();
        cn1.Close();
        ImageButton1.ImageUrl =  Picture1;
        ImageButton2.ImageUrl =  Picture2;
Avatar of DireOrbAnt
DireOrbAnt

GetValue(x) gives you the FIELD, not the ROW. that select would return 2 rows like this:
Image1
Image2

So GetValue(1) will fail since only one field per row is present.
You need to loop like you are doing now, but then assign the proper PictureX to the proper row.
Avatar of cbrick04

ASKER

can you show me how to do this loop?
Avatar of EugeneZ
first check if it is for your case (you did not post VS\sql server version edition):

FIX: "Index was outside the bounds of the array" error message in Visual Studio .NET when the ODBC driver does not support the SQLGetInfo(SQL_SEARCH_PATTERN_ESCAPE) function
http://support.microsoft.com/kb/833050/

also check:
Arrays and the Index Number
http://www.homeandlearn.co.uk/net/nets6p2.html
Im using VWD C# and SQL 2005
can you help me with code to accomplish what I need?
ASKER CERTIFIED SOLUTION
Avatar of DireOrbAnt
DireOrbAnt

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
That works great for what i need.
Can you help me simplify this with an array, I plan to do this with about 30 different pictures, seems that there will be an shorter way than coding each 1. Can you help me set an array to this to define Picture1-Picture30 ?
You should hit a few C# tutorials, I don't do C#, but I guess the syntax is familiar and I can Google some stuff ;)

Sometihng like that:
int RowNo = 0;
int[] URLArray = new int[30];
SqlDataReader reader1 = cmd1.ExecuteReader();
        while (reader1.Read())
        {
           URLArray[RowNo] = reader1.GetValue(0).ToString();
           RowNo++;
        }

Now though, you still need to reassign those to your buttons... How do you create your buttons? Maybe you can create them into the while loop and avoid the array?
Can you show the ImageButton1, ImageButton2... creation code?