Accessing a certain field in a data table

[redgar]

I have an access database. In the database I have a table that contains an autogenerated number (hospitalNumber). This number is my primary key. I need to be able to access the last item in the column to send that value to another table as I create a new row in it.

The only way I know how access these values is by binding them to my controls and looking through them. How would I access this value directly?

Hope this isn't too vague. I simply don't see anything in my under my dataset.datatable class that would allow me to access individual values nor do I see anything under my bindingsource class.

 

[jshultz]

dataset.tables(0).rows(0) for the first row of the first table (you will probably just need to use tables(0) since you probably only have one table in your dataset).

 

[JuggaloBrotha]

or:

Dim dr as datarow = dataset.tables("TableName").Rows(index as integer)

then:
dr(FieldName as string) is how you would grab the data from a field of that row

 

[jshultz]

I was going to put that as well but I am in the habit of using indexes because it is significantly faster than doing a lookup on the table name ;-)

 

[redgar]

Thanks a ton guys for your swift and accurate response. That solved a ton of my problems.

 

[redgar]

Ok, Im back with another question regarding my last.

When updating another table I have to pull data (a primary key) from another table's row based on a name that the user will have access to.

I know how to get the data now if I have the rows index but I'm not sure how to get the index of the row based on a certain datafield. If I have a name field, how do I get the index of that row in my table to retrieve that persons number?

redgar

 

[jshultz]

If I understand you right (and I am not sure that I do), you want to get a row from your datatable based on a certain value? Why don't you just step through the colleciton until you find the value that you want and then get that datarow and access the column like explained before? Or you could re-run the query and use a where clause.

 

[redgar]

Well before I accessed the data based on the column name and the row index. I actually happened to have the row index because I was getting the last row's data.

Dim length As Integer = dataset.Hospital_Table.Rows.Count
Dim dr As DataRow = dataset.Hospital_Table.Rows(length - 1)

dataRow.hospnum = CInt(dr("hospnum")) + 1

Thats what I did with it.

Now I dont have the row index, it will be determined by a value in the row which is selected by the user. I need to access other fields in that row. How would I get the corresponding "number" (the primary key) of from that row if I only have the name field.

Sorry if I should know this based on what you already said. I had my mind set on how I was going to do my previous task so I haven't been able to further explore the possiblities.

 

[redgar]

Found a way using a datatable.select. For sake of others...

Dim dt As DataTable = dataset.Hospital_Table
Dim row() As DataRow = dt.Select("hospname LIKE '" + cboHospitalName.Text + "%'", "hospname")
dataRow.hospnum = row(0)("hospnum")

So I've accessed my number based on the name.

redgar

 

[cjard]

The only issue I have with the advice you've been given, is that the other guys have given you very old, and somewhat lame advice..

You have a typed dataset here, so you can just say:

myDataSet.HospitalTable
myDataSet.HospitalTable(0).PatientName


you dont have to say:
myDataSet.Tables("HospitalTable")
myDataSet.Tables("HospitalTable").Rows(0).Item("PatientName")


Hopefully you can see why the first form is preferred, in the same way that we dont use Reflection for everything