Solved

How to loop through Sqldatareader and populate list box without binding?

Posted on 2007-03-23
6
728 Views
Last Modified: 2012-05-05
Using Asp.net I need to populate a listbox at runtime with values from a datareader.  I can't bind, because I will be moving values out of that list box (or maybe I could bind and unbind at run time?) Here is my existing code:
protected void PopulateListBox()
    {

        string myconnstr = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
        SqlCommand objCmdFun;
        SqlDataReader drfun;

        try
        {
           
            objCnFun = new SqlConnection(myconnstr);
            objCnFun.Open();
            objCmdFun = new SqlCommand("FunctionList",objCnFun);
            objCmdFun.CommandType = CommandType.StoredProcedure;
            objCmd.Parameters.Add(new SqlParameter("@siteid", myvar);
            drfun = objCmd.ExecuteReader(CommandBehavior.SequentialAccess);
            while (drfun.Read())
            {
                //add items to listbox here, with field 0 of rowset being value, and field 1 being text
            }
           

        }

        catch (Exception e)

        {
            string errormsg = e.Message.ToString();
        }

        finally
        {
            objCnFun.Close();

        }

           
           
           
           


       




    }



}
   
0
Comment
Question by:vhpcomp
6 Comments
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 125 total points
ID: 18780456
while (drfun.read())
{
  MyListBox.Items.Add(drfun.item(0).value,drfun.item(1).value);
}

Should be all you need to do.
0
 
LVL 22

Assisted Solution

by:_TAD_
_TAD_ earned 125 total points
ID: 18780477

Your code looks fine.  What problem are you having?

while (drfun.Read())
            {
                listbox1.Items.Add(drfun[1].Value, drfun[0].Value);  // Column 0 has a name, column 1 has the value
            }


The only catch is that if this is an ASP.Net application, you have to run this code during the page_load process, otherwise the endusers will not see it.
0
 
LVL 8

Expert Comment

by:gelbert
ID: 18780501
You can create temporary data source (DataTable for example), replace your while() loop with following

DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("StringValue", typeof(string)));
while(true == drfun.Read() )
{
   DataRow dr = dt.NewRow();
   dr[0] = drfun.GetString();
   dt.Rows.Add(dr);
}
  YourDataList.DataSource = new DataView(dt);

where YourDataList is yout DataList type  of object
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Author Comment

by:vhpcomp
ID: 18781076
Tad,
Your code

listbox1.Items.Add(drfun[1].Value, drfun[0].Value);

looks like the C# version  that I am looking for, but when I try that line I get:
object does not contain a definition for 'Value'
So, I looked at some of my previous interactions with a datareader and tried:
listbox1.Items.Add(drfun[1].ToString(), drfun[0].ToString());
I then get 'No overload for method add takes 2 arguments'

I'm sure it's something simple I'm overlooking....
0
 
LVL 8

Accepted Solution

by:
gelbert earned 250 total points
ID: 18781246
if you want ot pass 2 parameters than you have to use following version of Add()
public void Add(   ListItem item )
0
 
LVL 1

Author Comment

by:vhpcomp
ID: 18781451
Ok , I split points because everyone's response helped in figuring out.
I ended up with
ListItem myListItem = newListIItem();
myListItem.Value = drfun[0].ToString();
myListItem.Text = drfun[1].ToString(;
Listbox1.Items.Add(myListItem)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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