[RenaldoL25]

Hi, I am making a website for my VBA class that sells products which are located in a connected database. The database is already connected to my Website through other functions but I am having trouble accessing the information. My database consists of the columns ProductID, ProductName, Type, Make, Description, Price and QtyOnHan with ProductID as the primary key. I have 9 items. I am looking for a function to access each cell of this database but I cannot find it. So far, I can call to the database with a query that selects all columns and saves them as a list, but I’m not sure where to go after that. How can I access one “cell” of the list and use the information as a label.text property or an textbox.text property? Thanks for the help and I imagine the solution is an easy one!

My function for creating the list is:
Code: Select all
 Public Function GetProducts() As List(Of Product)
        Dim products As New List(Of Product)

        Dim query As String = "SELECT ProductID, ProductName, Type, Make, Description, Price, QtyOnHand " +
                              "FROM Products " +
                              "ORDER BY Price Desc"
        Dim command As New OleDbCommand(query, connection)

        Dim results = command.ExecuteReader()

        Return products
    End Function

BTW: Product is a class I created related to all the columns of the database

Here is what I do on page load:

 db = New Database
        Dim products = db.GetProducts()

[SapnaVishwas]

There are a couple of better ways to do this. Check out these links

http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/78f72403-509f-466c-9cb1-0f37ea5ce161
http://www.dreamincode.net/forums/topic/32392-sql-basics-in-vbnet/
http://www.developerfusion.com/article/4278/using-adonet-with-sql-server/ 

[Adan]

What you need is a datatable, something like this should work: Might not be 100% accurate but should be close

Public Function GetProducts() As DataTable

Dim query As String = "SELECT ProductID, ProductName, Type, Make, Description, Price, QtyOnHand FROM Products ORDER BY Price, Description"

Dim dtaTable As New DataTable
Dim dtaAdapter As New OleDb.OleDbDataAdapter(query, connection)

dtaAdapter.Fill(dtaTable)

Return dtaTable

End Function

Then call it using this code

Dim ProductID$, ProductName$

Dim Products As DataTable = GetProducts()

Dim ProductRow As DataRow

' Cycle thru Each Row in dataTable, like an Excel SpreadSheet
For Each ProductRow In Products.Rows

ProductID$ = ProductRow.Field("ProductID")
ProductName$ = ProductRow.Field("ProductName")
.
.
.
.
.

'Add Each Value to a List, Listview etc