?
Solved

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

Posted on 2007-12-02
5
Medium Priority
?
1,363 Views
Last Modified: 2008-02-01
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

0
Comment
Question by:barnesco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 16

Expert Comment

by:McExp
ID: 20392391
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
 

Author Comment

by:barnesco
ID: 20392580
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
 
LVL 16

Expert Comment

by:McExp
ID: 20392676
I don't understand your question.
0
 

Author Comment

by:barnesco
ID: 20393051
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
 
LVL 16

Accepted Solution

by:
McExp earned 2000 total points
ID: 20397264
create a new List and then add new items to it during the while loop which iterates over the returned data
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question