[RESOLVED] Selecting field from database and showing it on a button (MS Access)

[Darkstorm]

I got table1 with the columns ID, Name and IP. Can I fetch a name from the database (depending on ID) and show it as the text on a button when the form loads?

Something like

Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source= " & System.AppDomain.CurrentDomain.BaseDirectory & "\db1.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
 
        Dim queryString As String = "SELECT Name FROM table WHERE ID = '2'"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection
 
        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        buttonConf1.Text = dataSet.ToString

which sets the text on the button to System.Data.DataSet...

so how should it all actually look like?

if the code is completely wrong could you tell me how it should be please

[jmcilhinney]

A DataSet contains DataTables,a DataTable contains DataRows and a DataRow contains fields in which the actual data is stored. You've got three issues.

1. You haven't executed the SQL statement so you haven't retrieved any data.
2. You want to display a single field value, so you have to get it from within the DataSet using the relationships I mentioned before.
3. You shouldn't use a DataAdapter to get a single value. You should just create a Command and call ExecuteScalar.

Dim command As New OleDbCommand(query, connection)
 
connection.Open()
myButton.Text = CStr(command.ExecuteScalar())
connection.Close()

[Darkstorm]

Ok, thanks

Now I got this code

Dim myQuery As String
        Dim myConnection As New OleDb.OleDbConnection
        myQuery = "SELECT Name FROM table WHERE ID = 2"
myConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source= " & System.AppDomain.CurrentDomain.BaseDirectory & "\db1.mdb" 
'^-- doesnt work
        Dim myCommand As New OleDb.OleDbCommand(myQuery, myConnection)
        myCommand.Connection.Open()
        button1.Text = CStr(myCommand.ExecuteScalar())
        myConnection.Close()

doesn't work though How should I make the connection?


Edit: heh should ofcourse be
myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source= " & System.AppDomain.CurrentDomain.BaseDirectory & "\db1.mdb"

Rating up for you

[jmcilhinney]

Do you realise that many of these variables you are declaring have constructors that take arguments, and you can declare and initialise a variable on a single line, e.g.

Dim myQuery As String
myQuery = "SELECT Name FROM table WHERE ID = 2"

becomes

Dim myQuery As String = "SELECT Name FROM table WHERE ID = 2"

and

Dim myConnection As New OleDb.OleDbConnection
myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source= " & System.AppDomain.CurrentDomain.BaseDirectory & "\db1.mdb"

becomes

Dim myConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source= " & System.AppDomain.CurrentDomain.BaseDirectory & "\db1.mdb")

There is nothing technically wrong with what you are doing but, in my opinion, such long-winded code is actually harder to read.

[Darkstorm]

That'll shorten up my code a bit Thanks

[jcavard]

if you refer more than once to the database, you should save the connection string into a STRING variable like __CONNECTION_STRING and call this instead of long text "Provider=Microsoft.Jet.OLED..... " each time.

[jmcilhinney]

if you refer more than once to the database, you should save the connection string into a STRING variable like __CONNECTION_STRING and call this instead of long text "Provider=Microsoft.Jet.OLED..... " each time.

Actually, you should really only have a single Connection object. Declare the Connection object as a class-level variable. Create it in the Load event handler of the form, or wherever else is convenient if it's not in a form, which is the one place you would need the ConnectionString, then simply Open and Close it as needed.