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

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:

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

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:

Adding Next & Previous Record Controls
 

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: