Whats the best way to bind a dropdownlist to data from a database?
Previously I've been using a hashtable/sortedlist but I've come across
the situation where I want it ordered in the order *I* give it. Im after
storing an ID and a name, so an arraylist isnt much use as that only
stores one field..

-- 
Dogers (dogers@t...)
http://www.the-marines.com

Dogers,

	I've probably broken some of the intent of System.Data and
System.Data.OleDb that MS has made, but I basically wrote a series of web
controls that added a "SqlSelect" property and a "ConnectionString"
property.  From there, I overrided the DataBind method to get the data from
where ever the connection string specified (you could go one better and use
a project level data whatever it is rather than a ConnectionString).
Finally for my lookup tables, I wrote a LookupCache class that resides in
the global.asax file (I really just needed access to System.Web.HttpContext
to get to the Cache object for the AppDomain) and some other DropDownLists
that request a table's name from the LookupCache Cache collection and
LookupCache will build the dataset instead of throwing the
NullReferenceException exception from Cache when necessary.

	Of course if you can get your data into a dataset, you can always do
sorts on it there.

- Chuck

-----Original Message-----
From: Dogers [mailto:dogers@t...]
Sent: Saturday, May 04, 2002 8:23 PM
To: ASP+
Subject: [aspx] Dropdownlist Databinding


Whats the best way to bind a dropdownlist to data from a database?
Previously I've been using a hashtable/sortedlist but I've come across
the situation where I want it ordered in the order *I* give it. Im after
storing an ID and a name, so an arraylist isnt much use as that only
stores one field..

-- 
Dogers (dogers@t...)
http://www.the-marines.com

I wrote a "helper" function that you might try....

Public Sub Create_Dropdown(ByRef dd As
System.Web.UI.WebControls.DropDownList, _

ByVal defaultvalue As String, _

ByVal tablename As String, _

ByVal filter As String, _

ByVal valuefield As String, _

ByVal displayfield As String, _

ByVal orderby As String)

Dim dv As DataView

Dim qry As String

Dim i As Integer

Dim selected As Boolean

' this code is necessary incase the drop down pull list is being regenerated

dd.Dispose()

dd.Items.Clear()

dd.ClearSelection()

qry = "select " & valuefield & ", " & displayfield & _

" from " & tablename & " "

If Trim(filter) <> vbNullString Then

qry = qry & " where " & filter

End If

If Trim(orderby) <> vbNullString Then

If valuefield <> displayfield Then

qry = qry & " order by " & orderby

Else

qry = qry & " order by " & 1

End If

End If

dv = Get_DataView(qry)

selected = False

For i = 0 To (dv.Count - 1)

' add a line to the pull list

dd.Items.Add(i)

dd.Items(i).Text = dv(i)(displayfield)

dd.Items(i).Value = dv(i)(valuefield)

If Trim(dv(i)(valuefield)) = Trim(defaultvalue) And selected = False Then

dd.Items(i).Selected = True

selected = True

Else

dd.Items(i).Selected = False

End If

Next i

' add a blank line to the pull list

dd.Items.Add(dv.Count)

dd.Items(dv.Count).Text = ""

dd.Items(dv.Count).Value = ""

If selected Then

dd.Items(dv.Count).Selected = False

Else

dd.Items(dv.Count).Selected = True

End If

End Sub




----- Original Message -----
From: "Dogers" <dogers@t...>
To: "ASP+" <aspx@p...>
Sent: Saturday, May 04, 2002 6:22 PM
Subject: [aspx] Dropdownlist Databinding


> Whats the best way to bind a dropdownlist to data from a database?
> Previously I've been using a hashtable/sortedlist but I've come across
> the situation where I want it ordered in the order *I* give it. Im after
> storing an ID and a name, so an arraylist isnt much use as that only
> stores one field..
>
> --
> Dogers (dogers@t...)
> http://www.the-marines.com
>
>
>

I do it using the following example.


	Function FillBranchList()
		dim objCmd as new OleDbDataAdapter("SELECT TblBranch.fldBranchID,
TblBranch.fldBranch FROM TblBranch Order By fldBranch", conn)
		dim ds1 as dataset = new DataSet
		objCmd.Fill(ds1, "tblBranch1")
		lstBranch.DataSource = ds1
		lstBranch.DataMember = "tblBranch1"
		lstBranch.DataValueField = "fldBranchID"
		lstBranch.datatextfield = "fldBranch"
		lstBranch.databind()
	end Function

I hope this helps.

Warren

-----Original Message-----
From: Dogers [mailto:dogers@t...]
Sent: Sunday, May 05, 2002 10:23 AM
To: ASP+
Subject: [aspx] Dropdownlist Databinding


Whats the best way to bind a dropdownlist to data from a database?
Previously I've been using a hashtable/sortedlist but I've come across
the situation where I want it ordered in the order *I* give it. Im after
storing an ID and a name, so an arraylist isnt much use as that only
stores one field..

--
Dogers (dogers@t...)
http://www.the-marines.com