ASP.NET RSS ASP.NET RSS

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

Please support our ASP.NET advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!
Reply

•
•
Join Date: Feb 2003
Posts: 793
Reputation: Paladine has a spectacular aura about Paladine has a spectacular aura about Paladine has a spectacular aura about 
Solved Threads: 27
Team Colleague
Paladine's Avatar
Paladine Paladine is offline Offline
Master Poster

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

 
0
  #1
Jun 1st, 2004
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
ASP.NET Syntax (Toggle Plain Text) 
  1. <form id="Form1" method="post" runat="server">
  2. <!-- |||||	BEGIN OUTER TABLE	||||| -->
  3. <table id="tblMain" style="WIDTH: 568px; HEIGHT: 340px" cellSpacing="1" cellPadding="1" width="568" border="0">
  4.   <tr>
  5.     <td align="center" colSpan="2">Products</td></tr>
  6.   <tr>
  7.     <td style="WIDTH: 219px" rowSpan="9">
  8. 		<asp:listbox id=lstProducts runat="server" autopostback="True" width="232px" height="299px"></asp:listbox>
  9.     </td>
  10.     <td style="HEIGHT: 199px"><!--- |||||	Start Inner Table	||||| -->
  11. 		<table id="tblInner" cellSpacing="1" cellPadding="1" width="300" border="1">
  12.         <tr>
  13.           <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>
  14.         </tr>
  15.         <tr>
  16.           <td><asp:label id="lblProductName" runat="server" Width="112px">Product Name</asp:label><asp:textbox id="txtName" tabIndex="2" runat="server" ></asp:textbox></td>
  17.         </tr>
  18.         <tr>
  19.           <td><asp:label id="lblSupplier" runat="server" Width="112px">Supplier</asp:label><asp:textbox id="txtSupplier" tabIndex="3" runat="server" ></asp:textbox></td>
  20.         </tr>
  21.         <tr>
  22.           <td><asp:label id="lblCategory" runat="server" Width="112px">Category</asp:label><asp:textbox id="txtCategory" tabIndex="4" runat="server" ></asp:textbox></td>
  23.         </tr>
  24.         <tr>
  25.           <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>
  26.         </tr>
  27.         <tr>
  28.           <td><asp:label id="lblUnitPrice" runat="server" Width="112px">Unit Price</asp:label><asp:textbox id="txtUnitPrice" tabIndex="6" runat="server" ></asp:textbox></td>
  29.         </tr>
  30.         <tr>
  31.           <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>
  32.           </tr>
  33.         <tr>
  34.           <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>
  35.         </tr>
  36.         <tr>
  37.           <td><asp:label id="lblReorderLevel" runat="server" width="112px">Reorder Point</asp:label><asp:textbox id="txtReOrder" tabIndex="9" runat="server" ></asp:textbox></td>
  38.         </tr>
  39.         <tr>
  40.           <td><asp:Label id="lblDC" runat="server" Width="112px">Discontinued</asp:Label>
  41. 				<asp:checkbox id="chkDC" tabIndex="10" runat="server"/></td>
  42. 		</tr>
  43. 		</table><!--	|||||	END OF INNER TABLE	||||| --->
  44. 	</td>
  45. 	</tr>
  46. </table><!--	|||||	END OUTER TABLE	|||||	-->
  47. <p><asp:label id=lblMsg runat="server" width="568px"></asp:label></p>
  48. </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 as it does add a performance boost, as well as simplify coding a web application that consistantly uses the same DataSource.
-CASE SENSITIVE!

Sample Code: - Partial Code listing
ASP.NET Syntax (Toggle Plain Text) 
  1. <configuration>
  2.   <!--	|||||	Application Settings	|||||	-->
  3.   <appSettings>
  4. 	<add key="strConn" value="Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Northwind.mdb;User ID=Admin;Password=;" />
  5.   </appSettings>
  6.   <system.web>
  7.   '	...
  8.   '	...
  9.   '	...
  10.  

3. Write the code behind for the WebForm
a. Import Required Namespaces
ASP.NET Syntax (Toggle Plain Text) 
  1. 	Imports System.Web.Security     '   |||||   Required Class for Authentication
  2. 	Imports System.Data             '   |||||   DB Accessing Import
  3. 	Imports System.Data.OleDb       '   ||||||  Access Database Required Import!
  4. 	Imports System.Configuration    '   ||||||  Required for Web.Config appSettings |||||

b. Create Class Variable(s)
ASP.NET Syntax (Toggle Plain Text) 
  1. 	Dim MyConn As OleDbConnection = New OleDbConnection(ConfigurationSettings.AppSettings("strConn"))

c. Add the code in the Page_Load Event to populate the listbox
ASP.NET Syntax (Toggle Plain Text) 
  1. 	    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  2. 	        If Not Page.IsPostBack Then
  3. 	            '   |||||   Fill the Listbox with Product Items
  4. 	            ListLoad()
  5. 	            '   |||||   Set the default selected item (usually the first on the list)
  6. 	            lstProducts.SelectedIndex = 0
  7. 	        End If
  8.     	    End Sub

ListLoad() Subroutine
ASP.NET Syntax (Toggle Plain Text) 
  1.     	  '   |||||   FILL THE LIST BOX   |||||
  2. 	     Private Sub ListLoad()
  3. 	         '   |||||   DECLARE VARIABLES   |||||
  4. 	         Dim strProducts As String = "SELECT ProductID, ProductName FROM Products"
  5. 	         Dim oCmd As New OleDbCommand(strProducts, MyConn)
  6. 	         Dim objReader As OleDbDataReader
  7.  
  8. 	         Try
  9. 	             '   |||||   Check if Connection to DB is already open, if not, then open a connection
  10. 	             If MyConn.State = ConnectionState.Closed Then
  11. 	                 '   |||||   DB not already Open...so open it
  12. 	                 MyConn.Open()
  13. 	             End If
  14. 	             '   |||||   Set DataReader Obj to Results of the connection made
  15. 	             '   |||||   Setting the DataReader this way will allow you to close the reader
  16. 	             '   |||||   and the connection as the same time.
  17. 	             objReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
  18. 	             '   |||||   Clear any possible items in the Listbox prior to databinding.
  19. 	             lstProducts.Items.Clear()
  20. 	             '   |||||   Set the DataValueField to the Primary Key
  21. 	             lstProducts.DataValueField = "ProductID"
  22. 	             '   |||||   Set the DataTextField to the text/data you want to display
  23. 	             lstProducts.DataTextField = "ProductName"
  24. 	             '   |||||   Set the DataSource the the OleDBDataReader's result
  25. 	             lstProducts.DataSource = objReader
  26. 	             '   |||||   Bind the Source Data to the Web/Server Control
  27. 	             lstProducts.DataBind()
  28. 	             '   |||||   Close the Connection and the Reader
  29. 	             objReader.Close()
  30. 	         Catch ex As Exception
  31. 	             '   |||||   Handle any Exceptions
  32. 	             MsgBox("Error Connecting to Database!", MsgBoxStyle.Critical)
  33. 	         End Try
  34. 	       End Sub
  35.  

-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 :
ASP.NET Syntax (Toggle Plain Text) 
  1. 	 rivate Sub lstProducts_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstProducts.SelectedIndexChanged
  2. 	         '   |||||   "This event will post to the server only if the AutoPostBack property is TRUE ||||
  3. 	         '   |||||   If the Index of the selected item is changed 
  4. 	         '   |||||   (default = 0, i.e. First item in list)
  5. 	         '   |||||   Then display the details of the selected item
  6. 	         '   |||||   DELCARE VARIABLES   |||||
  7. 	         Dim objCmd As OleDbCommand
  8. 	         Dim objReader As OleDbDataReader
  9. 	         Dim strSQL As String
  10. 	         '   |||||   Create SQL String
  11. 	         strSQL = "SELECT ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock,"
  12. 	         strSQL &= " UnitsOnOrder, ReorderLevel, Discontinued FROM Products"
  13. 	         strSQL &= " WHERE ProductID = "
  14. 	         '   ||||| Returns the DataValueField
  15. 	         strSQL &= lstProducts.SelectedItem.Value
  16.  
  17. 	         objCmd = New OleDbCommand(strSQL, MyConn)
  18.  
  19. 	         Try
  20. 	             If MyConn.State = ConnectionState.Closed Then
  21. 	                 MyConn.Open()
  22. 	             End If
  23.  
  24. 	             objReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
  25. 	             '   |||||   Loop through the Reader to retrieve data    
  26. 	             '   |||||   and set each textbox/checkbox to appropriate data from datasource
  27. 	             While objReader.Read()
  28. 	                 With objReader
  29. 	                     txtID.Text = .Item("ProductID")
  30. 	                     txtName.Text = .Item("ProductName")
  31. 	                     txtQtyPerUnit.Text = .Item("QuantityPerUnit")
  32. 	                     txtUnitPrice.Text = .Item("UnitPrice")
  33. 	                     txtUnitsInStock.Text = .Item("UnitsInStock")
  34. 	                     txtUnitsOnOrder.Text = .Item("UnitsOnOrder")
  35. 	                     txtReOrder.Text = .Item("ReorderLevel")
  36. 	                     chkDC.Checked = .Item("Discontinued")
  37. 	                 End With
  38. 	             End While
  39.  
  40. 	         Catch ex As Exception
  41. 	             lblMsg.Text = ex.Message
  42. 	         End Try
  43. 	         'End If
  44.  
  45. 	     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!
Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2003
Posts: 793
Reputation: Paladine has a spectacular aura about Paladine has a spectacular aura about Paladine has a spectacular aura about 
Solved Threads: 27
Team Colleague
Paladine's Avatar
Paladine Paladine is offline Offline
Master Poster

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

 
0
  #2
Jun 1st, 2004
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:
ASP.NET Syntax (Toggle Plain Text) 
  1.  '   |||||   Create SQL String
  2.         strSQL = "SELECT ProductID, ProductName, SupplierID, CategoryID, "
  3.         strSQL &= "QuantityPerUnit, UnitPrice, UnitsInStock,"
  4.         strSQL &= " UnitsOnOrder, ReorderLevel, Discontinued FROM Products"
  5.         strSQL &= " WHERE ProductID = "
  6.         '   ||||| Returns the DataValueField
  7.         strSQL &= lstProducts.SelectedItem.Value


3. Add TWO new variables to the ListLoad() subroutine
ASP.NET Syntax (Toggle Plain Text) 
  1.  '   |||||   Variables to Store/Pass SupplierID & CategoryID
  2.  Dim intSupplier, intCat As Integer
  3.  


4. Modify the Try...Catch block in the ListLoad() subroutine to the following:
ASP.NET Syntax (Toggle Plain Text) 
  1. 	Try
  2.             If MyConn.State = ConnectionState.Closed Then
  3.                 MyConn.Open()
  4.             End If
  5.  
  6.             objReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
  7.             '   |||||   Loop through the Reader to retrieve data    
  8.             '   |||||   and set each textbox/checkbox to appropriate data from datasource
  9.             While objReader.Read()
  10.                 With objReader
  11.                     txtID.Text = .Item("ProductID")
  12.                     txtName.Text = .Item("ProductName")
  13.                     txtQtyPerUnit.Text = .Item("QuantityPerUnit")
  14.                     txtUnitPrice.Text = .Item("UnitPrice")
  15.                     txtUnitsInStock.Text = .Item("UnitsInStock")
  16.                     txtUnitsOnOrder.Text = .Item("UnitsOnOrder")
  17.                     txtReOrder.Text = .Item("ReorderLevel")
  18.                     chkDC.Checked = .Item("Discontinued")
  19.                     '   |||||   Get Supplier
  20.                     intSupplier = CInt(.Item("SupplierID"))
  21.                     intCat = CInt(.Item("CategoryID"))
  22.                 End With
  23.             End While
  24.  
  25.         Catch ex As Exception
  26.             lblMsg.Text = ex.Message
  27.         Finally
  28.             objReader.Close()
  29.             '   |||||   Load Supplier
  30.             LoadSuppliers(intSupplier)
  31.             '   |||||   Load Category
  32.             LoadCategories(intCat)
  33.         End Try
  34.  


5. Create the LoadSuppliers & LoadCategories subroutines

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

ASP.NET Syntax (Toggle Plain Text) 
  1.      Private Sub LoadSuppliers(ByVal intValue As Integer)
  2.          Dim strSuppliers As String = "SELECT SupplierID, CompanyName FROM Suppliers"
  3.          Dim oCmd As New OleDbCommand(strSuppliers, MyConn)
  4.          Dim objReader As OleDbDataReader
  5.  
  6.          Try
  7.              If MyConn.State = ConnectionState.Closed Then
  8.                  '   |||||   DB not already Open...so open it
  9.                  MyConn.Open()
  10.              End If
  11.              objReader = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
  12.              cboSupplier.Items.Clear()
  13.              cboSupplier.DataValueField = "SupplierID"
  14.              cboSupplier.DataTextField = "CompanyName"
  15.              cboSupplier.DataSource = objReader
  16.              cboSupplier.DataBind()
  17.              cboSupplier.SelectedIndex = intValue
  18.          Catch ex As Exception
  19.              lblMsg.Text = ex.Message
  20.          Finally
  21.              objReader.Close()
  22.          End Try
  23.  
  24.     End Sub
  25.  

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

Happy Coding!
Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2003
Posts: 793
Reputation: Paladine has a spectacular aura about Paladine has a spectacular aura about Paladine has a spectacular aura about 
Solved Threads: 27
Team Colleague
Paladine's Avatar
Paladine Paladine is offline Offline
Master Poster

Adding Next & Previous Record Controls

 
0
  #3
Jun 3rd, 2004
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
Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ASP.NET Forum
Thread Tools Search this Thread



.net 2.0 activexcontrol advice ajax alltypeofvideos asp asp.net bc30451 beginner bottomasp.net browser c# c#gridviewcolumn cac checkbox class commonfunctions compatible confirmationcodegeneration content contenttype countryselector courier dataaccesslayer database datagridview datagridviewcheckbox datalist deployment development dgv dropdownlist dynamic dynamically edit embeddingactivexcontrol fileuploader fill findcontrol flash flv formatdecimal forms formview gridview gudi homeedition iframe iis javascript jquery listbox microsoft mouse mssql multistepregistration news objects opera panelmasterpagebuttoncontrols problem redirect registration relationaldatabases reportemail rotatepage schoolproject security serializesmo.table sessionvariables silverlight smartcard smoobjects software sql sql-server sqlserver2005 ssl textbox tracking treeview unauthorized validatedate validation vb.net video videos virtualdirectory vista visual-studio visualstudio web webapplications webarchitecture webdevelopemnt webdevelopment webprogramming webservice youareanotmemberofthedebuggerusers
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC