Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Populate DropDownList with data from database

Posted on 2005-04-13
14
Medium Priority
?
239 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:DanBAtkinson
  • 4
  • 4
  • 3
  • +3
14 Comments
 
LVL 18

Assisted Solution

by:tusharashah
tusharashah earned 2000 total points
ID: 13774057
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
 
LVL 7

Expert Comment

by:ayha1999
ID: 13774088
Hi,

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

ayha

0
 
LVL 7

Expert Comment

by:maXXXeE
ID: 13774091
yes, setup another SQL query with result from first
then, traverse result with a loop, and  u add with    ListBox1.Items.Add("string")
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DanBAtkinson
ID: 13774124
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
 

Author Comment

by:DanBAtkinson
ID: 13774156
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
 
LVL 18

Accepted Solution

by:
tusharashah earned 2000 total points
ID: 13774160
<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
 

Author Comment

by:DanBAtkinson
ID: 13774254
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
 
LVL 7

Expert Comment

by:ayha1999
ID: 13774361
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
 
LVL 7

Expert Comment

by:ayha1999
ID: 13774367
Hi again,

suppose you have already a dropdownlist1 in the form.

ayha
0
 
LVL 7

Expert Comment

by:ayha1999
ID: 13774375
Hi,

you retrive value from dropdown like this;

dim strValue as string = dropdownlist1.selectedItem.Text

ayha
0
 
LVL 35

Expert Comment

by:YZlat
ID: 13774618
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
 
LVL 18

Assisted Solution

by:tusharashah
tusharashah earned 2000 total points
ID: 13774744
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
 

Expert Comment

by:DanAtkinson
ID: 13775678
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
 

Author Comment

by:DanBAtkinson
ID: 13780785
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month21 days, 1 hour left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question