How do you read values from a SQLDataSource and use in a 'FOR EACH' statement?

In code behind, how do I read a SqlDataSource list of values to use a for/next loop (as designated as "TargetList" in the snippet below)? I may not have the right DIM for the TargetList. Right now, I receive an error saying that the TargetList.DataSource = PromoList part is not a member of the System.Collection.ArrayList.

Thanks.
		Dim TargetList As ArrayList
		Dim i As Integer
 
		Dim PromoList As SqlDataSource
		PromoList.ConnectionString = "<%$ ConnectionStrings:SiteSqlServer%>"
		PromoList.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
		PromoList.SelectCommand = "GetPromoList"
		PromoList.DataBind()
 
		TargetList.DataSource = PromoList
		TargetList.DataValueField = "PromoID"
		TargetList.DataBind()
 
 
For i = 0 To TargetList.Items.Count - 1
 
            If TargetList.Items(i).Selected Then
 
                cmd.Parameters("@ProdID").Value = Convert.ToInt16(TargetList.Items(i).Value)
 
                cmd.ExecuteNonQuery()
 
            End If
 
        Next

Open in new window

barnescoAsked:
Who is Participating?
 
McExpConnect With a Mentor Commented:
create a new List and then add new items to it during the while loop which iterates over the returned data
0
 
McExpCommented:
The Simplest and in my opinion correct solution is to not use the SqlDataSource.

See below for an example using SqlConnection and SqlCommand: -
Dim myReader as SqlDataReader
    Dim mySqlConnection as SqlConnection
    Dim mySqlCommand as SqlCommand
 
 
    mySqlConnection = new SqlConnection("server=(local)\NetSDK;Trusted_Connection=yes;database=northwind")
    mySqlCommand = new SqlCommand("select * from customers", mySqlConnection)
 
    try
      mySqlConnection.Open()
      myReader = mySqlCommand.ExecuteReader()
 
      Console.Write("Customer ID    ")
      Console.WriteLine("Company Name")
 
      do while (myReader.Read())
        Console.Write(myReader("CustomerID").ToString() + "    ")
        Console.WriteLine(myReader("CompanyName").ToString())
      loop
    catch e as Exception
      Console.WriteLine(e.ToString())
    finally
      if Not (myReader is Nothing)
        myReader.Close()
      end if
 
      if (mySqlConnection.State = ConnectionState.Open)
        mySqlConnection.Close()
      end if
    end try

Open in new window

0
 
barnescoAuthor Commented:
I have many values from a field that are being returned. How would you assign a list of values from the datareader?

This substitute for:

Console.Write(myReader("CustomerID").ToString() + "    ")
Console.WriteLine(myReader("CompanyName").ToString())
0
 
McExpCommented:
I don't understand your question.
0
 
barnescoAuthor Commented:
I need to return the values to an arraylist or a list of some sort. It's easy to return them to a ddl or some other control, but I need to put those values somewhere so that I can access them for a for/next loop.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.