0

I am a newbie in asp.net, I want to ask what is more proper in terms of populating the dropdown list in asp.net? using a Datasource or using a sqldatareader and a for loop.

I am using the sqldatareader and a for loop, here is my sample code: 

For i = 1 To 20
            etc.CommandText = "select Classification from schemaItemDetails.AssetClassification where AC_ID = " & i & ""
            Dim dr As SqlDataReader = etc.ExecuteReader
            While (dr.Read())
                ddoneclassification.Items.Add(dr.GetString(0))
            End While
            dr.Close()
        Next

Is there a difference in using sqldatasource and this one?? I never use sqldatasource for populating the dropdown.

4 Answers4

1

From my point of view the easiest and the best solution is to set the DropDownList's DataSource property. In this case, all the required job is done behind the scene and you neednd't think of a synchronization data between the db server and web server.

Also, if I were you, I would modify the sql so that only a single request to be sent to the DB server, i.e. 

"select Classification from schemaItemDetails.AssetClassification where AC_ID between 1 and 20"
platon
  • 5,310
  • 1
  • 22
  • 24
0

Instead of doing a loop, Get the 20 items in your query (SELECT TOP 20 ID,Name from YourTable WHERE ..) and bind it to your dropdown control

ddoneclassification.DataSource=dr
ddoneclassification.DataTextField="Name"  
ddoneclassification.DataValueField="ID"
ddoneclassification.DataBind()

Assuming you have 2 fields called Name and ID in the result set of your query.

Shyju
  • 214,206
  • 104
  • 411
  • 497
0

This is almost the same as yours, but the "while" loop has been removed and replaced with a query that will return more results. The Read() method increments the index each time it is called, and that is each time the loop check takes place, so your original code was almost fine.

Databinding would be another option, but this is certainly valid. I certainly wouldn't consider it "wrong".

etc.CommandText = "select Classification from schemaItemDetails.AssetClassification where AC_ID between 1 and 20"
Dim dr As SqlDataReader = etc.ExecuteReader
While (dr.Read())
    ddoneclassification.Items.Add(dr.GetString(0))
End While
dr.Close()

A note of caution: different databases handle "BETWEEN" differently, so check how yours does. You may have to use where AC_ID>= 1 and AC_ID>=20, depending on which boundaries your database includes/excludes.

More information on that at the bottom of this page.

mirichan
  • 1,370
  • 1
  • 12
  • 25
0

I think you should use a datasource in order to populate the drop down list. Simply choose the data source for the dropdown list and configure it. Sqldatareader is basically used to read the data in a forward only sequential manner whereas a datasource is used to establish a connection between a database and a web page.

Have a look at this video: http://www.youtube.com/watch?v=sOE_hA2NLBI

user1976537
  • 21
  • 6