asked on

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

McExp

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

barnesco

ASKER

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())

McExp

I don't understand your question.

barnesco

ASKER

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.

ASKER CERTIFIED SOLUTION

McExp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial