Populate Dropdownlist with data from database and add blank row

Hello,

I'm new to ASP so I need help to do the following:

I need to populate a Dropdownlist with data from an Access database table. I can bind the control to a DataSource in design mode but I want to fill the dropdownlist with data using code so that I can add on the first row a blank row or some kind of message like "Choose your Color...". So far I've writen the code below, then I got stucked...

Can you please help me do this?

Thanks,

jppinto
Dim strSql As String = ""
strSql = "SELECT [Descricao] FROM [Leis] ORDER BY [Descricao]"
Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Programação\AEDemo\db\manpower.mdb")
Dim Cmd As New OleDbCommand(strSql, Conn)
Conn.Open()

Open in new window

LVL 33
jppintoAsked:
Who is Participating?
 
sijishJohnConnect With a Mentor Commented:
try this..
Dim strSql As String = ""
        strSql = "SELECT [Descricao] FROM [Leis] ORDER BY [Descricao]"
        Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Programação\AEDemo\db\manpower.mdb")
        Dim Cmd As New OleDbCommand(strSql, Conn)
        Conn.Open()
        DropDownList1.DataSource = Cmd.ExecuteNonQuery()
        DropDownList1.DataBind()
        DropDownList1.Items.Add(New ListItem("Choose your Color...", "-1"))
        Conn.Close()

Open in new window

0
 
Obadiah ChristopherCommented:
Sorry dude have c# code only...
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
 
DataSet ds = new DataSet();
 
da.Fill(ds);
 
ddl.DataSource = ds.Tables[0];
ddl.DataTextField = "Descricao";
ddl.DataValueField="Descricao";
ddl.DataBind();
 
ListItem li = new ListItem("Select avalue","0");
ddl.Items.Insert(0,li);

Open in new window

0
 
BinuthConnect With a Mentor Commented:
try this

        Dim strSql As String = ""
        strSql = "SELECT [Descricao] FROM [Leis] ORDER BY [Descricao]"
        Dim Conn As New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Programação\AEDemo\db\manpower.mdb")
        Dim Cmd As New Data.OleDb.OleDbCommand(strSql, Conn)
        Dim adp As New Data.OleDb.OleDbDataAdapter(Cmd)
 
        Dim dt As New Data.DataTable
        adp.Fill(dt)
 
        DropDownList1.DataSource = dt
        DropDownList1.DataTextField = "Descricao"
        DropDownList1.DataValueField = "Descricao"
        DropDownList1.DataBind()
 
        DropDownList1.Items.Insert(0, "Choose your Color...")

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
ajollyConnect With a Mentor Commented:
You can add this item at design time, Choose your Color...
and specify AppendDataBoundItems property of the dropdownlist as true.


<asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="true">
    <asp:ListItem Value="" Text="Choose your Color...">
    </asp:ListItem>
</asp:DropDownList>

Open in new window

0
 
Obadiah ChristopherConnect With a Mentor Commented:
Here's the entire code
Dim strSql As String = ""
strSql = "SELECT [Descricao] FROM [Leis] ORDER BY [Descricao]"
Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Programação\AEDemo\db\manpower.mdb")
Dim Cmd As New OleDbCommand(strSql, Conn)
Conn.Open()
 
Dim da As OleDbDataAdapter =  New OleDbDataAdapter() 
da.SelectCommand = cmd
 
Dim ds As DataSet =  New DataSet() 
 
da.Fill(ds)
 
ddl.DataSource = ds.Tables(0)
ddl.DataTextField = "Descricao"
ddl.DataValueField="Descricao"
ddl.DataBind()
 
Dim li As ListItem =  New ListItem("Select a value","0") 
ddl.Items.Insert(0,li)

Open in new window

0
 
jppintoAuthor Commented:
I've chosen to put only a blank row and mantain the control bound to the datasource.
Thanks annyway.

jppinto
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.