I'm a relative newbie on ASP.Net and am struggling with the idea of building a dropdown list dynamically from an array. Unfortunately my case isn't as simple as the examples I keep seeing.

Esentially, I will be pulling data from an Access database, but I must also manipulate the fields a little before they can go into the drop down. So I used a datareader to get the fields, but then I need to combine 2 fields (lastname comma firstname) and then use a 3rd field as the value. I can use the datareader and I can manipulate what it retrieves placing the results in an array, but how do I bind the array to the listbox control? I see examples of doing it with an arraylist, but if I understand correctly I don't need an array of objects, just 2 simple arrays of values.

How about a datatable =]

First setup your select statement like this:

SELECT
    lastname + ', ' + firstname as [Name],
    otherField
From
    Table

Now, I personally have a wrapper class that handles all of my database functionality so that all my calling code knows is that it is recieving a datatable from a function, it knows nothing about my database.

Here is my Datatable function:

        public DataTable getDataTable(string sqlcmd)
        {
            DataTable dt = new DataTable();
            sqlConn = new SqlConnection([connectionstring]);
            cmd = new SqlCommand(sqlcmd, sqlConn);
            daSql = new SqlDataAdapter(sqlcmd, sqlConn);
            daSql.Fill(dt);

            return dt;
        }

so in your calling page you might have

DataTable dt = new DataTable();
string sSQL;

sSQL = [SELECT Statement from above];
dt = getDataTable(sSQL);

foreach(DataRow dr in dt.Rows){
    drp.Items.Add(new ListItem(dr["name"], dr["valuecolumn"]));
}

hth

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

Sorry you wanted VB (and i forgot the initial variable declartaion):

Private daSql As SqlDataAdapter
Private sqlCmd As SqlCommand
Private sqlConn As SqlConnection

Public Function getDataTable(ByVal sqlCmd as string) as DataTable
Dim dt as new Datatable
sqlConn = New SqlConnection([connectionString])
cmd = New SqlCommand(sqlCmd, sqlConn)
daSql = New SqlDataAdapter(sqlcmd, sqlConn)
daSql.Fill(dt)
Return dt
End Function

Dim dt as New DataTable
Dim sSql as string

sSQL = [SELECT Statement]
dt = getDataTable(sSQL)

For Each dr as DataRow in dt.Rows
   drp.Items.Add(new ListItem(dr.item("name"), dr.item("valuecolumn")))
Next

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

It seems to me that what you are doing is adding the items in the drop down a line at a time, using the statements:

For Each dr as DataRow in dt.Rows
   drp.Items.Add(new ListItem(dr.item("name"), dr.item("valuecolumn")))
Next

That's one way to create the list, but I had thought there would be some simple way of creating an array of names and an array of values and binding them to the dropdown(s) with one databind (or similar) command. Am I wrong?

In my particular case, I will be populating up to 4 different dropdowns on one page with the same name/value pairs, and I may have as many as 200 entries in each list. So I was striving to use a technique that both works and is fairly efficient. 800+ adds to ListItems seems like a lot.

Just use the datatable then:

drp.datasource = dt
drp.DataTextField = "name"
drp.DataValueField = "value"
drp.databind()

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

Sorry, but I'm still struggling. (I'm actually a fairly competent programmer, but this .Net stuff has really confused me.) Seeing as I'm using Access (not SQL server) and seeing as I don't have a wrapper class, I've tried to pull the relevant lines from your example and convert them for my needs. As a result, my code now says:

    'Open a connection
    Dim objConn as OleDbConnection
    objConn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=E:\My Documents\ ..... ;")
    objConn.Open()

    'Specify the SQL string
    Dim strSQL as String = "SELECT LastName, FirstName, UserName FROM Users WHERE Status = 'Active' ORDER BY LastName, FirstName"

    'Create a command object
    Dim objCommand as OleDbCommand
    objCommand = New OleDbCommand(strSQL, objConn)

    ' Data Adapter to DataTable
    Dim dt as new DataTable
    Dim da as new OleDBDataAdapter
    da = new OleDBDataAdapter(objCommand,objConn)
    da.Fill(dt)

    'Databind the table to the listbox Web control
    lstDepartments.DataSource = dt
    lstDepartments.DataTextField = "LastName"
    lstDepartments.DataValueField = "UserName"
    lstDepartments.DataBind()


    objConn.Close()

I'm not comfortable with what all this means yet, but when I run it I get:

BC30518: Overload resolution failed because no accessible 'New' can be called with these arguments:

Line 27: da = new OleDBDataAdapter(objCommand,objConn)

I've tried a few minor things like dropping the word "new" but I just get replacement error messages. Can you advise what I'm missing here? (Honestly, I'd love to be able to do this myself and I've invested many hours trying to do exactly that.)

Ooops ... I found it (funny how that works, just after you post the question).

Line 27 s/b "da = new OleDBDataAdapter(strSQL,objConn)"