Populating & Retrieving Data in a listbox : ASP.NET (w/ VB.NET)

[Paladine]

This a just a basic webform setup of a listbox being populated with data from a database, in this case the Northwind Access database, and then populating textboxes with data related to the item the user selects.

1. WebForm
-Create an ASP.NET webform page
-Add a listbox server control and set AutoPostBack property to True
-Add 9 textbox server controls and give them appropriate ID's to match the FieldNames from the datasource.
i.e txtProductID for ProductID, txtProductName for ProductName, etc
-Add a checkbox server control for the discontinued option for a particular product.
-For visual design purposes I would recommended associated labels to indicate what will appear in the textboxes.

Sample Code: - Partial Code listing

<form id="Form1" method="post" runat="server">
<!-- |||||	BEGIN OUTER TABLE	||||| -->
<table id="tblMain" style="WIDTH: 568px; HEIGHT: 340px" cellSpacing="1" cellPadding="1" width="568" border="0">
  <tr>
    <td align="center" colSpan="2">Products</td></tr>
  <tr>
    <td style="WIDTH: 219px" rowSpan="9">
		<asp:listbox id=lstProducts runat="server" autopostback="True" width="232px" height="299px"></asp:listbox>
    </td>
    <td style="HEIGHT: 199px"><!--- |||||	Start Inner Table	||||| -->
		<table id="tblInner" cellSpacing="1" cellPadding="1" width="300" border="1">
        <tr>
          <td style="HEIGHT: 26px"><asp:label id="lblID" runat="server" Width="112px">Product ID</asp:label><asp:textbox id=txtID tabIndex="1" runat="server" width="48px" font-bold="True" enabled="False"></asp:textbox></td>
        </tr>
        <tr>
          <td><asp:label id="lblProductName" runat="server" Width="112px">Product Name</asp:label><asp:textbox id="txtName" tabIndex="2" runat="server" ></asp:textbox></td>
        </tr>
        <tr>
          <td><asp:label id="lblSupplier" runat="server" Width="112px">Supplier</asp:label><asp:textbox id="txtSupplier" tabIndex="3" runat="server" ></asp:textbox></td>
        </tr>
        <tr>
          <td><asp:label id="lblCategory" runat="server" Width="112px">Category</asp:label><asp:textbox id="txtCategory" tabIndex="4" runat="server" ></asp:textbox></td>
        </tr>
        <tr>
          <td><asp:label id="lblQtyPerUnit" runat="server" Width="112px">Quantity Per Unit</asp:label><asp:textbox id="txtQtyPerUnit" tabIndex="5" runat="server" ></asp:textbox></td>
        </tr>
        <tr>
          <td><asp:label id="lblUnitPrice" runat="server" Width="112px">Unit Price</asp:label><asp:textbox id="txtUnitPrice" tabIndex="6" runat="server" ></asp:textbox></td>
        </tr>
        <tr>
          <td><asp:label id="lblUnitsInStock" runat="server" Width="112px">Units In Stock</asp:label><asp:textbox id="txtUnitsInStock" tabIndex="7" runat="server" ></asp:textbox></td>
          </tr>
        <tr>
          <td><asp:label id="lblUnitsOnOrder" runat="server" Width="112px">Units on Order</asp:label><asp:textbox id="txtUnitsOnOrder" tabIndex="8" runat="server" ></asp:textbox></td>
        </tr>
        <tr>
          <td><asp:label id="lblReorderLevel" runat="server" width="112px">Reorder Point</asp:label><asp:textbox id="txtReOrder" tabIndex="9" runat="server" ></asp:textbox></td>
        </tr>
        <tr>
          <td><asp:Label id="lblDC" runat="server" Width="112px">Discontinued</asp:Label>
				<asp:checkbox id="chkDC" tabIndex="10" runat="server"/></td>
		</tr>
		</table><!--	|||||	END OF INNER TABLE	||||| --->
	</td>
	</tr>
</table><!--	|||||	END OUTER TABLE	|||||	-->
<p><asp:label id=lblMsg runat="server" width="568px"></asp:label></p>
</form>

2. Web.Config Settings - Connection String
-Modify to meets your particular setup.
-I prefere to place the connection string in the web.config file :cool: as it does add a performance boost, as well as simplify coding a web application that consistantly uses the same DataSource.
-CASE SENSITIVE! :D

Sample Code: - Partial Code listing

<configuration>
  <!--	|||||	Application Settings	|||||	-->
  <appSettings>
	<add key="strConn" value="Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Northwind.mdb;User ID=Admin;Password=;" />
  </appSettings>
  <system.web>
  '	...
  '	...
  '	...

3. Write the code behind for the WebForm
a. Import Required Namespaces

Imports System.Web.Security     '   |||||   Required Class for Authentication
	Imports System.Data             '   |||||   DB Accessing Import
	Imports System.Data.OleDb       '   ||||||  Access Database Required Import!
	Imports System.Configuration    '   ||||||  Required for Web.Config appSettings |||||

b. Create Class Variable(s)

Dim MyConn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("strConn"))

c. Add the code in the Page_Load Event to populate the listbox

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
	        If Not Page.IsPostBack Then
	            '   |||||   Fill the Listbox with Product Items
	            ListLoad()
	            '   |||||   Set the default selected item (usually the first on the list)
	            lstProducts.SelectedIndex = 0
	        End If
    	    End Sub

ListLoad() Subroutine

'   |||||   FILL THE LIST BOX   |||||
	     Private Sub ListLoad()
	         '   |||||   DECLARE VARIABLES   |||||
	         Dim strProducts As String = "SELECT ProductID, ProductName FROM Products"
	         Dim oCmd As New OleDbCommand(strProducts, MyConn)
	         Dim objReader As OleDbDataReader
	 
	         Try
	             '   |||||   Check if Connection to DB is already open, if not, then open a connection
	             If MyConn.State = ConnectionState.Closed Then
	                 '   |||||   DB not already Open...so open it
	                 MyConn.Open()
	             End If
	             '   |||||   Set DataReader Obj to Results of the connection made
	             '   |||||   Setting the DataReader this way will allow you to close the reader
	             '   |||||   and the connection as the same time.
	             objReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
	             '   |||||   Clear any possible items in the Listbox prior to databinding.
	             lstProducts.Items.Clear()
	             '   |||||   Set the DataValueField to the Primary Key
	             lstProducts.DataValueField = "ProductID"
	             '   |||||   Set the DataTextField to the text/data you want to display
	             lstProducts.DataTextField = "ProductName"
	             '   |||||   Set the DataSource the the OleDBDataReader's result
	             lstProducts.DataSource = objReader
	             '   |||||   Bind the Source Data to the Web/Server Control
	             lstProducts.DataBind()
	             '   |||||   Close the Connection and the Reader
	             objReader.Close()
	         Catch ex As Exception
	             '   |||||   Handle any Exceptions
	             MsgBox("Error Connecting to Database!", MsgBoxStyle.Critical)
	         End Try
	       End Sub

-Now that you have the listbox populated, what about the textboxes?
-Well, what we want to happen is that when the user selects any item in the listbox we want to have the data on that item populate the textboxes.
-The first two steps have been done to make this work. The first was setting the AutoPostBack property to True for the listbox, and the second was
setting the DataValueField (primary key) and DataTextField for the listbox data. The third part is now obtain the data on the selection the user has made, which is done by the code in the SelectedIndexChanged event

SelectIndexChanged code :

rivate Sub lstProducts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstProducts.SelectedIndexChanged
	         '   |||||   "This event will post to the server only if the AutoPostBack property is TRUE ||||
	         '   |||||   If the Index of the selected item is changed 
	         '   |||||   (default = 0, i.e. First item in list)
	         '   |||||   Then display the details of the selected item
	         '   |||||   DELCARE VARIABLES   |||||
	         Dim objCmd As OleDbCommand
	         Dim objReader As OleDbDataReader
	         Dim strSQL As String
	         '   |||||   Create SQL String
	         strSQL = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock,"
	         strSQL &= " UnitsOnOrder, ReorderLevel, Discontinued FROM Products"
	         strSQL &= " WHERE ProductID = "
	         '   ||||| Returns the DataValueField
	         strSQL &= lstProducts.SelectedItem.Value
	 
	         objCmd = New OleDbCommand(strSQL, MyConn)
	 
	         Try
	             If MyConn.State = ConnectionState.Closed Then
	                 MyConn.Open()
	             End If
	 
	             objReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
	             '   |||||   Loop through the Reader to retrieve data    
	             '   |||||   and set each textbox/checkbox to appropriate data from datasource
	             While objReader.Read()
	                 With objReader
	                     txtID.Text = .Item("ProductID")
	                     txtName.Text = .Item("ProductName")
	                     txtQtyPerUnit.Text = .Item("QuantityPerUnit")
	                     txtUnitPrice.Text = .Item("UnitPrice")
	                     txtUnitsInStock.Text = .Item("UnitsInStock")
	                     txtUnitsOnOrder.Text = .Item("UnitsOnOrder")
	                     txtReOrder.Text = .Item("ReorderLevel")
	                     chkDC.Checked = .Item("Discontinued")
	                 End With
	             End While
	 
	         Catch ex As Exception
	             lblMsg.Text = ex.Message
	         End Try
	         'End If
	 
	     End Sub

Now just "run" the webform and you are done.

Of course this is a simple set of code and concept, but the principles that you can now build on from here will enable you do a variety to things, like dropdown menu lists, or listboxes of background color choices, just to name a few.

I could have done a number of things different, or have add more funcitonality, which I do plan to do. As I modify and expand this concept further I will post the changes and the results.

Happy Coding! :cool:

[Paladine]

Additions and Changes to the Above - For Supplier & Category DropDownLists:

1. Change both txtSupplier and txtCategory to a DropDownList server control

2. Change the SQL string in ListLoad() subroutine to the following:

'   |||||   Create SQL String
        strSQL = "SELECT ProductID, ProductName, SupplierID, CategoryID, "
        strSQL &= "QuantityPerUnit, UnitPrice, UnitsInStock,"
        strSQL &= " UnitsOnOrder, ReorderLevel, Discontinued FROM Products"
        strSQL &= " WHERE ProductID = "
        '   ||||| Returns the DataValueField
        strSQL &= lstProducts.SelectedItem.Value

3. Add TWO new variables to the ListLoad() subroutine

'   |||||   Variables to Store/Pass SupplierID & CategoryID
 Dim intSupplier, intCat As Integer

4. Modify the Try...Catch block in the ListLoad() subroutine to the following:

Try
            If MyConn.State = ConnectionState.Closed Then
                MyConn.Open()
            End If

            objReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
            '   |||||   Loop through the Reader to retrieve data    
            '   |||||   and set each textbox/checkbox to appropriate data from datasource
            While objReader.Read()
                With objReader
                    txtID.Text = .Item("ProductID")
                    txtName.Text = .Item("ProductName")
                    txtQtyPerUnit.Text = .Item("QuantityPerUnit")
                    txtUnitPrice.Text = .Item("UnitPrice")
                    txtUnitsInStock.Text = .Item("UnitsInStock")
                    txtUnitsOnOrder.Text = .Item("UnitsOnOrder")
                    txtReOrder.Text = .Item("ReorderLevel")
                    chkDC.Checked = .Item("Discontinued")
                    '   |||||   Get Supplier
                    intSupplier = CInt(.Item("SupplierID"))
                    intCat = CInt(.Item("CategoryID"))
                End With
            End While

        Catch ex As Exception
            lblMsg.Text = ex.Message
        Finally
            objReader.Close()
            '   |||||   Load Supplier
            LoadSuppliers(intSupplier)
            '   |||||   Load Category
            LoadCategories(intCat)
        End Try

5. Create the LoadSuppliers & LoadCategories subroutines

-Code provided is the for LoadSuppliers subroutine, which is a carbon copy of the LoadCategories subroutine

Private Sub LoadSuppliers(ByVal intValue As Integer)
         Dim strSuppliers As String = "SELECT SupplierID, CompanyName FROM Suppliers"
         Dim oCmd As New OleDbCommand(strSuppliers, MyConn)
         Dim objReader As OleDbDataReader
 
         Try
             If MyConn.State = ConnectionState.Closed Then
                 '   |||||   DB not already Open...so open it
                 MyConn.Open()
             End If
             objReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
             cboSupplier.Items.Clear()
             cboSupplier.DataValueField = "SupplierID"
             cboSupplier.DataTextField = "CompanyName"
             cboSupplier.DataSource = objReader
             cboSupplier.DataBind()
             cboSupplier.SelectedIndex = intValue
         Catch ex As Exception
             lblMsg.Text = ex.Message
         Finally
             objReader.Close()
         End Try
 
    End Sub

These are just some minor changes to add further functionality to the initial code.

Happy Coding! :cool:

[Paladine]

And addition of sorts. This small addition will allow the user to click a Next and Previous button to move within the data in the listbox and dynamically update the textboxes/drop downlists displaying the item details.

Add two Webform button controls, give them appropriate ID's and add the code for their OnClick events.

Next Button

Private Sub imgNext_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgNext.Click
        '   |||||   Move to next Record & re-populate the textboxes/dropdownlists
        '   ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 
        '   Watch for the range values, as the Items.Count gives the total number of
        '   items, and the Index values of the listbox are zero based, therefore 
        '   deduct 1 from the count value to get the last index value.
        '   |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
        If lstProducts.SelectedIndex >= lstProducts.Items.Count - 1 Then
            lstProducts.SelectedIndex = 0
        Else
            lstProducts.SelectedIndex += 1
        End If
        '   |||||   Index has now changed, so call the appropriate method
        Call lstProducts_SelectedIndexChanged(sender, e)

End Sub

Previous Button

Private Sub imgPrev_Click(ByVal sender As System.Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgPrev.Click
        '   |||||   Move to the previous record in the list
        '   |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
        '   Remember that the Index value can not be less than Zero (must be a 
        '   positive number value) and if the Index is at 0, then previous would 
        '   take you to the last item in the list; Count -1
        '   |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
        If lstProducts.SelectedIndex > 0 Then
            lstProducts.SelectedIndex -= 1
        Else
            lstProducts.SelectedIndex = lstProducts.Items.Count - 1
        End If
        '   ||||||  Index has now changed, so call the IndexChanged event
        Call lstProducts_SelectedIndexChanged(sender, e)

    End Sub

It should be noted that I used an ImageButton control, but you can use the standard webform button control or even a HyperLink button control if you want. Just be sure to modify the above code accordingly.

Happy Coding :cool:

[SDSWarlord]

I would like to know if you have something similar for popualting Labels with results from you SQL query. I have and order form with the client details at the top and I have used labels. I would like to populate this automatically based on the user logged into the site. Dont have to be labels, could be text boxes. However I do not want the clients to have access to other client details, so I would not like to use dropdown list boxes

[HamplusTech]

How can i read data sequentially from database using sqldatareader. Such that when i click next button the reader will read the next row in the database. Thanks. Hamplus Tech
[e-mail]
[e-mail]
[phone]
[phone]
[phone]