Tricky DataSet Question

  • Thread starter Thread starter Prince
  • Start date Start date
P

Prince

I've filled a DataSet,ds, with a table, State. The
schema is similar to the following:

State
============
ID
CountryID
StateAbrev
StateName


I've loaded the State table into the dataset, ds. The
StateAbrev has all the state abreviations. How can I get
the record number of a state abreviation? Example. Lets
say the following 2 records are inside the table.

State
=======
1 1000 NY New York
2 1000 RI Rhode Island

Given the string, str = "RI", how can I get the record
number within the state table.

int index = GetRecordNumber(); //should return the
number 1

thanks,
Prince
 
I do have this function which works. The only problem I
have is that I have to loop through every record. I had
thought there might have been a better method.

//s = state abreviation
protected int GetStateIndex(string s){
int index = 0;
foreach( DataRow row in ds.Tables["States"].Rows){
if( row["StateAbrev"].Equals(s) )
break;
index++;
}
return index;
}
 
Prince

I would write a stored procedure for that

Create a temp table with same structure as the Stat
Create extra column call RowNumber as AutoNumber (If you are using SQL Server
Using Select Query Populate the record into Tem
Query on temp

HTH
Sachy
 
Hi Prince,

It's really quite simple:

create a dataview and sort it on the abbrev col; then .find the
abbreviation, thus:

Dim dahistory As New SqlDataAdapter("select imcacct, reshipcd, bipad from
history order by bipad, imcacct", oconn)

Dim dshistory As New DataSet("history")

dahistory.Fill(dshistory, "history")

Dim arrayseekh(0) As Object

Dim ifindh as integer

Dim vueh As New DataView(dshistory.Tables(0))

vueh.Sort = "abbrev"

arrayseekh(0) = "NJ" ' or the col name of a datarow loop - eg,
irow("abbrev")

ifindh = vueh.Find(arrayseekh)

If ifindh <> -1 Then ' ie, found it

m_id = vueh(ifindh)("id")

End If

HTH,

Bernie Yaeger
 
Hi Prince,

Sachy gave you an good idea.
You don't need a temp table though.
Just add another column to your table and populate it with order.
Then create a DataView on this table and sort it by the field you want to
search
Then you might use DataView.Find method.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Prince said:
I do have this function which works. The only problem I
have is that I have to loop through every record. I had
thought there might have been a better method.

//s = state abreviation
protected int GetStateIndex(string s){
int index = 0;
foreach( DataRow row in ds.Tables["States"].Rows){
if( row["StateAbrev"].Equals(s) )
break;
index++;
}
return index;
}
-----Original Message-----
I've filled a DataSet,ds, with a table, State. The
schema is similar to the following:

State
============
ID
CountryID
StateAbrev
StateName


I've loaded the State table into the dataset, ds. The
StateAbrev has all the state abreviations. How can I get
the record number of a state abreviation? Example. Lets
say the following 2 records are inside the table.

State
=======
1 1000 NY New York
2 1000 RI Rhode Island

Given the string, str = "RI", how can I get the record
number within the state table.

int index = GetRecordNumber(); //should return the
number 1

thanks,
Prince



.
Click to expand...
Click to expand...
 
Back
Top