Add data from database in a drop down in vb.net

Question

I have a drop down list for cities, and I have cities in my database that I want to populate to a drop down list.

On the Page load made an sql connection

 Dim s As String = "Connection String"
 sqlconn = New SqlConnection(s)

Now on the drop down load I have a query which is working fine. How to add results from the database query to the drop down?

   Protected Sub Citydropdown_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Citydropdown.Load

        sqlCmd = New SqlCommand("select City from usrRegister_aunthentication", sqlconn)
        If sqlconn.State = Data.ConnectionState.Closed Then
            sqlconn.Open()
        End If
        Dim i As String = sqlCmd.ExecuteReader()

        If sqlconn.State = Data.ConnectionState.Open Then
            sqlconn.Close()
        End If
    End Sub

Also, sometimes the same city is repeated. How do I only show each city once?

Pro tip: you'll get better answers if you put more work into your question. See the edit for what something that's much easier for those of who answer to use. Also, I hope you pay a lot more attention to the details of your code than you did to your question here. — Joel Coehoorn
– Joel Coehoorn, Commented Dec 26, 2011 at 17:30

KV Prajapati · Accepted Answer · 2011-12-26 05:51:33Z

4

Do not use ExecuteNonQuery to fetch the result (rows). Use ExecuteReader to obtains the reader instance.

Dim dataReader as SqlDataReader 
dataReader = sqlCmd.ExecuteReader()
While dataReader.Read
  ' Write code to insert an Item into dropdownlist
  DropDownItem1.Items.Add(dataReader("City").ToString())
End While
dataReader.Close()

edited Dec 26, 2011 at 5:51

answered Dec 26, 2011 at 5:34

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Raghav Chopra Over a year ago

that i have edited but what code i have to wrtie so as to fill data in the drop -down , which code should e written in while loop?

Raghav Chopra Over a year ago

and more ova when i use ExecuteReader it says SqldataReader can not be convertedt to string

Raghav Chopra Over a year ago

can i ask u 1 more query ? in the same ques above ?

KV Prajapati Over a year ago

Do you want to fetch distinct city? SELECT DISTINCT CITY from usrRegister_aunthentication

KV Prajapati Over a year ago

You should post a question with complete problem description.

|

em3ricasforsale · Accepted Answer · 2011-12-26 06:08:03Z

0

You could also try this. It is a line or two less.

Using dr = sqlCmd.ExecuteReader()
While dr.Read
     ' Write code to insert an Item into dropdownlist
     DropDownItem1.Items.Add(dr("City").ToString())
End While
End Using

answered Dec 26, 2011 at 6:08

em3ricasforsale

3523 gold badges7 silver badges23 bronze badges

Collectives™ on Stack Overflow

Add data from database in a drop down in vb.net

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related