Populate DropDownList with data from database

Hi there,

I really urgently need an answer to this question!

I have a page which reads and writes data from and to an Access db. My problem is this:

I cannot populate DropDownList boxes with data from a specified table.

This is to be used mainly in database



    <script runat="server">
      Sub submit(sender As Object, e As System.Web.UI.WebControls.CommandEventArgs)

        Try

          if NOT ((itemsName.Text = nothing) or (itemsDescription.Text = nothing) or (itemsPrice.Text = nothing) or (itemsPictureAddress.Text = nothing) or (itemsPictureWidth.Text = nothing) or (itemsPictureHeight.Text = nothing))  then

            dim dbconn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../admin.mdb"))
            dbconn.Open()

              dim sql As String = "insert INTO tblProducts (itemName, itemDescription, itemPrice, itemPictureAddress, itemPictureWidth, itemPictureHeight) values ('" & itemsName.Text & "', '" & itemsDescription.Text & "', '" & itemsPrice.Text & "', '" & itemsPictureAddress.Text & "', '" & itemsPictureWidth.Text & "', '" & itemsPictureHeight.Text & "')"

              dim Cmd As OleDbCommand = New OleDbCommand(sql,dbconn)
              Cmd.ExecuteNonQuery()
              dbconn.Close()

          else
            response.write("<b>Data cannot be inserted because one or more fields are empty. Please fill all fields and try again.</b>")
          end if

        Catch ex As Exception
          Response.Write (ex.Message)
        End Try

      end Sub
   </script>


    <table width="500">
      <td>
    <form runat="server">
        <tr><b>Name of item:</b><br>
          <asp:TextBox id="itemsName" runat="server" /></td><br><br>
        <tr><b>Description: </b><br>
          <asp:TextBox id="itemsDescription" TextMode="multiline" width="300" height="100" runat="server" /></td><br><br>
        <tr><b>Price (without £ symbol (10.00)):</b><br>
          <asp:TextBox id="itemsPrice" runat="server" /></td><br><br>
        <tr><b>Picture Address: (You must upload the picture first)</b><br>
          <asp:TextBox id="itemsPictureAddress" runat="server" /></td><br><br>
        <tr><b>Picture Width: (must be an integer)</b><br>
          <asp:TextBox id="itemsPictureWidth" runat="server" /></td><br><br>
        <tr><b>Picture Height: (must be an integer)</b><br>
          <asp:TextBox id="itemsPictureHeight" runat="server" /></td><br>

        <tr><asp:Button OnCommand="submit" Text="Submit" runat="server" /></td>
      <td>
    </form>
    <table>

In the forms list I require an asp:DropDownList which reads from a seperate table (tblProductList). There are about three-5 values in this. Would I just set up another SQL query and then execute when the first SQL query is executed? Also if that's the case, how do I tell the DropDownBox where to get the code from?

Thanks in advance.
DanBAtkinsonAsked:
Who is Participating?
 
tusharashahConnect With a Mentor Commented:
<asp:DropDownList id="DropDownList1" runat="server" />
 is enough because you're binding your DropDownList in the code behind...

' Specify Database Column name here...
DropDownList1.DataTextField = "TextFieldName"   ' From your DataBase Table
DropDownList1.DataValueField = "ValueFieldName"   ' From your DataBase Table

-tushar
0
 
tusharashahConnect With a Mentor Commented:
Modify your code like following:


dim Cmd As OleDbCommand = New OleDbCommand(sql,dbconn)
Cmd.ExecuteNonQuery()
'----Add this lines...
Cmd = New OleDbCommand("NewSQLQuery",dbconn)
DropDownList1.DataSource = Cmd.ExecuteReader()
DropDownList1.DataTextField = "TextFieldName"   ' From your DataBase Table
DropDownList1.DataValueField = "ValueFieldName"   ' From your DataBase Table
DropDownList1.DataBind()
'------
dbconn.Close()


-tushar
0
 
ayha1999Commented:
Hi,

You can't populate dropdownlist or you want to populate second dropdown, coud u pls explain?

ayha

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
maXXXeECommented:
yes, setup another SQL query with result from first
then, traverse result with a loop, and  u add with    ListBox1.Items.Add("string")
0
 
DanBAtkinsonAuthor Commented:
Thanks for the quick reply.

As I have little experience with dropdownlists in asp.net, could you please explain what i need to put in the TextFieldName and ValueFieldName boxes please?

Also, do I just need the following code in the form?:

        <tr><b>Product Type:</b><br>
          <asp:DropDownList id="DropDownList1" runat="server" /></td><br>

???

Thanks again.
0
 
DanBAtkinsonAuthor Commented:
My first reply was aimed at tushar

@ayha1999
I want to make my first dropdownlist and don't know where to start. Are there any good places to go through this in tutorials? I read w3schools.com for some of the basics but from thereonin it seems like there are sporadic examples and tutorials. Any software out there that you can do this in also? I've been working in Notepad all this time.

@MaXXXeE
That makes no sense whatsoever to me!
0
 
DanBAtkinsonAuthor Commented:
Thanks.

The table name is tblProductType. The sole column is ProductType. There are 4 values in it, 'Office Furniture', 'Chairs', and 'Tables'.

Would this be:

DropDownList1.DataTextField = "ProductType"   ' From your DataBase Table
DropDownList1.DataValueField = "Office Furniture"   ' From your DataBase Table
DropDownList1.DataValueField = "Chairs"   ' From your DataBase Table
DropDownList1.DataValueField = "Tables"   ' From your DataBase Table

Once the user has made a decision, how is this inserted to the db? You used the line:
Cmd = New OleDbCommand("NewSQLQuery",dbconn)
But what is the NewSQLQuery?

Thanks.
0
 
ayha1999Commented:
Hi,

try this

put this function somewhere in page.

 Public Shared Function GetDr(ByVal sqlText As String) As Data.OleDb.OleDbDataReader
        Dim dr As OleDbDataReader
        Dim Con As New OleDbConnection( _
           "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\InetPub\wwwroot\yourite\youfolder\yourdb.mdb;Jet OLEDB:Database Password=;")

        Dim sqlCmd As OleDbCommand = New OleDbCommand(sqlText, Con)
        sqlCmd.Connection.Open()
        dr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)
        Return dr
    End Function

then

    Dim sqlText As String = "select * from yourtable"

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not Page.IsPostBack Then

            dropdown1.DataSource = General.GetDr(sqlText)
            dropdown1.DataTextField = "your text field"
            dropdown1.DataValueField = "you value field"
            dropdown1.DataBind()
            dropdown1.Items.Insert(0, New ListItem("select", ""))  <--- if you want
end sub

hope this help.s

ayha
0
 
ayha1999Commented:
Hi again,

suppose you have already a dropdownlist1 in the form.

ayha
0
 
ayha1999Commented:
Hi,

you retrive value from dropdown like this;

dim strValue as string = dropdownlist1.selectedItem.Text

ayha
0
 
YZlatCommented:
Put the floowing on your form:
<asp:dropdownlist id=ddlProduct runat="server" DataTextField="ProductType" DataValueField="ProductType" Width="155px" DataMember="tblProductList">
</asp:dropdownlist>


This code goes between <script runat="server">
 tags:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not IsPostBack Then
            BindDropdown()
            ddlProduct.DataBind()
            ddlProduct.Items.Insert(0, New ListItem("Select a Product"))
            ddlProduct.SelectedIndex = 0
        End If

    End Sub

Public Function BindDropdown()
        Dim Reader As oledbDataReader
        Dim sql As String
        sql = "SELECT * FROM tblProductList"
        Try
            Reader = GetData(sql)
        Catch oledberr As oledbException
 
            Console.Write("oledb error: " + oledberr.ToString + ", oledb error message:" & oledberr.Message)
        Catch ex As Exception
           
            Console.Write("Error Executing a Query: " + ex.ToString())
        End Try

        Return Reader
    End Function

 Public Function GetConnection() As OleDbConnection
        Dim dbconn As OleDbConnection
       
        Try
             dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../admin.mdb"))

        Catch OleDberr As OleDbException
            Send_Error(OleDberr)
            Console.Write("OleDb error: " + OleDberr.ToString + ", OleDb error message:" & OleDberr.Message)
        Catch ex As Exception
            Send_Error(ex)
            Console.Write("Error Opening Connection: " + ex.ToString())
        End Try

        Return OleDbConnection
    End Function
 
    Public Function GetData(ByVal sql As String) As OleDbDataReader
        Dim dr As OleDbDataReader
        Dim Conn As OleDbConnection = GetConnection()
        Dim Cmd As OleDbCommand = New OleDbCommand(sql, Conn)
        Try
            dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        Catch OleDberr As OleDbException
         
            Console.Write("OleDb error: " + OleDberr.ToString + ", OleDb error message:" & OleDberr.Message)
        Catch ex As Exception
       
            Console.Write("Error Retrieving Data: " + ex.ToString())
        Finally
            'If Conn.State = ConnectionState.Open Then
            'Conn.Close()
            'End If
        End Try
        Return dr
    End Function
0
 
tusharashahConnect With a Mentor Commented:
You dont need to enter Values of that table column one by one.. they'll be grabbed automatically..

DropDownList1.DataTextField = "ProductType"   ' From your DataBase Table
DropDownList1.DataValueField = "ProductType"   ' From your DataBase Table Since it's sole column in your table..
DropDownList1.DataBind()        'and you'll have your DropDownList..

-tushar
0
 
DanAtkinsonCommented:
Thanks for the quick answers. I'll have to wait until tomorrow to evaluate them and see if they need any further input!

Thanks for all the quick replies!

Kind regards,
0
 
DanBAtkinsonAuthor Commented:
Ok. I've had chance to go over the code and the most effective one was by tusharashah!

The full code for it is:



      Private Sub Page_Load

        dim dbconn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../admin.mdb"))
        dbconn.Open()

          dim NewSQLQuery As String = "SELECT * FROM tblProductType"
          dim Cmd = New OleDbCommand(NewSQLQuery,dbconn)
          DropDownList1.DataSource = Cmd.ExecuteReader()
          DropDownList1.DataTextField = "ProductType"
          DropDownList1.DataValueField = "ProductType"
          DropDownList1.DataBind()

        dbconn.Close()

....

In the form:
        <tr><b>Product Type:</b><br>
          <asp:DropDownList id="DropDownList1" runat="server" /></td><br>


Thanks for all the suggestions and comments. Much appreciated!

Thank.
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.