Get info from database

[Silhouette]

Ok, so I have my code written to update, delete, and insert new records into my MS Access DB, using the oledbadapter and a dataset. The problem is, I cannot find out how to get the info that is returned by a select statement. E.G. "Select * from Products"

Heres the code im using now, but I dont know how to get what it returns. All help would be greatly appreciated.

Public Function SelectFrom(ByVal what As String, ByVal table As String, ByVal where As String) As DataSet ' This function does a custom select statement
 
        Try
            Dim query As String = "Select " & what & " from " & table & " where " & where & ";"
            Dim db As New OleDbDataAdapter(query, ConnectionString) ' Runs
            db.Fill(DataSet, "Products")
            Return DataSet
            db.Dispose()
        Catch e As Exception
            MsgBox(e.Message)
        End Try

[mar_zim]

put it in datagrid.
datagrid1.datasource=dataset.tables("Products")

[jmcilhinney]

It depends on how you want to display the data. A DataGrid will display your data pretty much as Access would. It provides you with various formatting options and makes editing data easy. You may want to display your data in other ways, using other types of controls. Generally, any control that displays a list, e.g. ListBox or ComboBox, have a DataSource property, as mar_zim suggests. I'd suggest checking the help topics for DataSource, DataMember and ValueMember to get more info, if you're interested. You can also get at individual values within the DataTable itself using the Rows property, which is of type DataRowCollection, and its properties and so on.

Two less important points:
It's not generally considered a good idea to name an instance of a class after the class itself. If you've named your DataSet "DataSet" just for this example then it's obviously no biggie.
I know Access won't allow multiple SQL statements in one command. Does it still allow you to add that semicolon, though? It's certainly not necessary, but if it doesn't throw an exception then, again, no biggie.

[Silhouette]

Ok, thanks for the tips.

Yea, access dosent care about the semi-colon, im just used to adding it.

Heres the code I found, and it works good. (Im not trying to display it to the user, just in my code.)

Dim ocmdAboutVBCommand As OleDbCommand
Dim odtrAboutVBDataReader As OleDbDataReader

ocmdAboutVBCommand = New OleDbCommand
With ocmdAboutVBCommand
.Connection = New OleDbConnection(ConnectionString)
.Connection.Open()
.CommandText = "Select * From Products"
odtrAboutVBDataReader = .ExecuteReader()
End With
Dim objects(100) As Object
odtrAboutVBDataReader.Read()
MsgBox(odtrAboutVBDataReader.GetString(1))

[jmcilhinney]

Statistics prove that the OleDbDataReader is more efficient (read: faster) than the OleDbDataAdapter at retrieving data. If all you want to do is display, then the data reader is the right choice. The data adapter does have other advantages, though. There is plenty of literature around that compares the two, so choose the one that is right for each individual situation.

[Silhouette]

Yea, for the select statements im going to use the data reader, and for all the others im going to use oledbdatareader, not for any piticular reason, ive just written all the others .

[jmcilhinney]

If you intend to retrieve, edit and re-save data, that is one of the individual situations where you should use an OleDbDataAdapter. It allows you to relate your query and non-query commands and even provides mechanisms to create the non-query commands automatically. You simply call Fill(myTable) to get, make your edits, then call Update(myTable) to set.

[Silhouette]

Humm, OK. How would I use a select command with a oledbdataadapter?

[jmcilhinney]

The OleDbDataAdapter has SelectCommand, DeleteCommand, InsertCommand and UpdateCommand properties, each of which is an OleDbCommand object with the appropriate SQL statement in its CommandText. The easiest way to create an OleDbDataAdapter is to add one to your form in the designer and set the appropriate values in the Properties window. Once you have the appropriate SQL select command in place, just call the Fill method on the data adapter. It is an overloaded method, so I'd suggest checking the help for a description of each. If you don't know how to create a parameterised command, I'd suggest you look up the OleDbParameter class in the help as well. That's more for the updating part but useful for retrieving as well. Also, this forum has a sticky thread that links to the 101 VB Samles from Microsoft. It includes a data access sample that I'm sure would demonstrate the concepts.

[Silhouette]

Ok ill look into the sample, but here was I have so far: Try
Dim query As String = "Select " & what & " from " & table & " where " & where & ";"
Dim da As OleDbDataAdapter = New OleDbDataAdapter
Dim cmd As OleDbCommand

' Create the SelectCommand.
' If the connection string is null, use a default.
Dim conn As New OleDbConnection(ConnectionString)

cmd = New OleDbCommand(query, conn)

cmd.Parameters.Add("@Country", OleDbType.VarChar, 15)
cmd.Parameters.Add("@City", OleDbType.VarChar, 15)

da.SelectCommand = cmd

da.Fill(DS, "Products")

Return da

Catch e As Exception
MsgBox(e.Message)
End Try

End Function

[jmcilhinney]

As I said, parameters get more of a workout when updating. With a select command, they are normally given a value that then gets substituted into the SQL when Fill is called. Here's a quick example:

Dim myConnection As New OleDbConnection
        Dim myAdapter As New OleDbDataAdapter("SELECT * FROM Table1 WHERE ID > @ID", myConnection)
 
        'This can be done many ways as Add has several overloads.
        myAdapter.SelectCommand.Parameters.Add("@ID", 100)
 
        Dim myTable As New DataTable
 
        myAdapter.Fill(myTable)

[Silhouette]

Ok, thanks. I got it worked out. Thanks alot for your help. Post rated.