asked on

select certain rows froma a dataset

I have dataset1 with 2 columns, fname, lname. I want to load a grid with it so I would have

MyGrid.DataSource = DataSet1.Tables(0)

How would I write this to only load rows that have a lname of 'jones' ?

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bergertime

ASKER

I can't seem to get this to work, this is an untyped dataset. Does that make a difference?

Bob Learned

No, that is code for an untyped DataSet.

I used table names, rather than ordinal index (DataSet1.Tables("Table1") and not DataSet1.Tables(0)). If you don't have the table alias correct, you will get an exception.

Also, if you have a DataSet with only a single DataTable, then I would think about just using a DataTable, because of the extra overhead for the DataSet.

Bob

bergertime

ASKER

Here is my code: It does not error, just does not return anything

Dim cn As iDB2Connection = New iDB2Connection("User ID=me;Password=me;Data Source=10.1.1.253;Connection Timeout = 0")
Dim cmd As iDB2Command
Dim sqlstring As String

sqlstring = "select substr(vaactdat,5 ,2) as MONTH, substr(vaactdat,7 ,2) as DAY, substr(vaactdat,3 , 2) as YEAR, vaseq#, vauser, vadesc from lib.table"

Dim da As New iDB2DataAdapter(sqlstring, cn)

'Open connection
cn.Open()
cmd = New iDB2Command(sqlstring, cn)

Try
da.Fill(DataSet1)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

Me.DataSet1.Tables(0).Columns.Add("DT").Expression = "Month + '/' + Day + '/' + Year"

Me.DataSet1.Tables(0).Rows.Add("1", "1", "2008")

Debug.WriteLine(Me.DataSet1.Tables(0).Rows(0).Item("DT"))

Dim dv As New DataView(DataSet1.Tables("0"))
dv.RowFilter = String.Format("vauser = '{0}' ", "COLTB")
Grid1.DataSource = dv

cn.Close()
cn.Dispose()

Bob Learned

Go back to ordinal index:

Dim dv As New DataView(DataSet1.Tables(0))

Is this an ASP.NET or WinForms grid?

Bob

bergertime

ASKER

WinForm

Bob Learned

After you apply the RowFilter, what is the value for dv.Count?

Bob

bergertime

ASKER

Bob Learned

That means that your filter condition is invalid for the data in the DataTable, or there aren't any rows that meet that condition.

Bob

bergertime

ASKER

Returns no rows not specifing a filter

Dim dv As New DataView(DataSet1.Tables("0"))
UltraGrid1.DataSource = dv
Label1.Text = dv.Count

Returns 400 rows below

UltraGrid1.DataSource = dataset1

Shouldn't I get the same on both of these?

Bob Learned

Yes, if your DataSet only has 1 DataTable, then dv.Count should be 400.

Bob

bergertime

ASKER

The only other thing I can think of is I drop my DataSet on my form from the toolbox, would that make a difference?

Bob Learned

No, but it would allow you to use Intellisense with DataSet development.

You would still need to filter the rows in some manner, which is outside of the scope of the typed DataSet.

Bob

bergertime

ASKER

Thanks, sorry to drag it out.