How can I Return a selected row without the Frist column (ID autonumber column) ??

[David Eadie]

G'Day all,
Heres my code: (watch for wrapping)


'MDB Connection and open
Dim MDBConnection
Dim MDBConnString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=example.mdb"
MDBConnection = New OleDbConnection(MDBConnString)
MDBConnection.Open()

'Open table from database
Dim MDBRead As New OleDb.OleDbCommand("SELECT * FROM
Accounting WHERE UserName = '" + TextBox1.Text + "'", MDBConnection)
Dim MDBReader As OleDbDataReader

'While reading MDB put the required data into label or textbox
MDBReader = MDBRead.ExecuteReader
While MDBReader.Read()
array

End While

'Close MDB Connection
MDBReader.Close()
MDBConnection.Close()


My database is an Access Database and has a table called "Accounting"
in it.
In that table there exists 5 columns in order called: ID, Username,
Firstname, Lastname, Password. ID is set to autonumber and the rest
are just plain text.

Now where the >>>>>> line is above in the code it will return the "ID"
field from my database. If I change the 0 to a 1 it will return the
username field and if I change it to a 2 it will return the firstname
field ect...

What I want to return is the Username, Firstname, Lastname and
Password fields together without the ID field.
Iv frigged around with the .getvalues method but it appears Im barking
up the wrong tree and I have searched msdn and google groups but I
just cant seem to figure out something so simple..
Please help I'll keep you posted on my progress.

Cheers

Dave.

 

[W.G. Ryan eMVP]

Dave you can concatenate the GetValue statements together or preferably, you
can just specify it in the select statement.. SELECT FIRSTNAME + LASTNAME +
....

Or you can use an Expression column and use a DataTable
http://www.knowdotnet.com/articles/dataviews1.html - this way is probably
better in most cases particularly when the data is going to change. If you
read it concatenated or concatenate the reads, if one of the values changes
then the data will remain stale. If you use an expression column and bind
to it, it will always reflect any changes that have been made after the
qeuery.

 

[David Eadie]

G'Day William and thanks for replying.
So far I am using the following code:

Dim MDBConnection As New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=example.mdb")
Dim MDBGetRow As New OleDbCommand("SELECT * FROM Accounting
WHERE UserName = '" + TextBox1.Text + "'", MDBConnection)
Dim MDBDataAdapter As New OleDbDataAdapter(MDBGetRow)
Dim MDBDataSet As New DataSet

MDBConnection.Open()
MDBDataAdapter.Fill(MDBDataSet, "Accounting")
MessageBox.Show(MDBDataSet.Tables("Accounting").Rows(0).Item(1)
& " " & MDBDataSet.Tables("Accounting").Rows(0).Item(2))

MDBConnection.Close()

It gets me outa trouble but I am reading up on the link you posted
(thanks )

Cheers

Dave.