Re: Get info from database
put it in datagrid.
datagrid1.datasource=dataset.tables("Products")
Re: Get info from database
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.
Re: Get info from database
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))
Re: Get info from database
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.
Re: Get info from database
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 :).
Re: Get info from database
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.
Re: Get info from database
Humm, OK. How would I use a select command with a oledbdataadapter?
Re: Get info from database
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.
Re: Get info from database
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
Re: Get info from database
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:
VB Code:
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)
Re: Get info from database
Ok, thanks. I got it worked out. Thanks alot for your help. Post rated.