Query a Dataset after it is returned?

[VBAHole22]

I have the code to query my db and fill a dataset. What I would like to do now is query that returned dataset. In this instance I want to get the max and min dates for the two date fields. How can I do this?

 

I don't want to run 4 separate queries to get this done. Once I fill the dataset I should be able to query it to get the answers. I would also like to avoid the 'sort and take the top record' approach. It's sloppy and there has to be a better way. I should be able to write a SQL command against the dataset, right?

 

 

 

Dim cmd As New OracleCommand("SELECT DPATH, MDATE, CDATE FROM MapTable", OracleConn)

Dim da As New OracleDataAdapter(cmdListFill)

Dim ds As New DataSet

Dim dt As DataTable

Dim DCount As Integer

 

Try

'Fill the DPATH list

OracleConn.Open()

DCount = da.Fill(ds, "RECS")

dt = ds.Tables(0)

Me.lstN.DataSource = dt

Me.lstN.DataTextField = "DPATH"

Me.lstN.DataBind()

Edited June 25, 2004 by VBAHole22

[Arch4ngel]

Well... you might use a dt.Select(...) (I don't know the parameters).

But you might create an SQL command from your connection and ask the question directly from the DB.

 

Or... make a loop an search all records.

 

Other solution buddy's from XtremeDotNet ?

[VBAHole22]

One of the .NET example provided for Select is:

 

 

strExpr = "id > 5"

' Sort descending by CompanyName column.

strSort = "name DESC"

' Use the Select method to find all rows matching the filter.

Dim foundRows As DataRow() = _

customerTable.Select( strExpr, strSort, DataViewRowState.Added )

PrintRows( foundRows, "filtered rows")

 

foundRows = customerTable.Select()

PrintRows( foundRows, "all rows")

 

 

They also stress the fact that you must sort to get the right answer.

How would I do this with a sort on my date field and just pull one row?

Still seems like a hack to me.

I would like to do something like

 

ds.Select("Top 1 MDATE sort by MDATE Desc)

 

or even better

 

ds.Select("MIN(MDATE) sort by MDATE Desc")

[Arch4ngel]

dt.Select( filterExpression, sortExpression)

 

filterExpression = ???

sortExpression = "MDATE Desc"

 

And you pick the only one left (or the first one).

I didn't manage to make the filter work with a Min or Max but... you'll have to compare your date to a date that will at least return 1 result.

 

Unless someone have a better idea ?

[Nerseus]

You should be able to use the Compute method:

object maxDate = ds.Tables[0].Compute("MAX(colname)");

 

-nerseus

[VBAHole22]

Sweet

 

That is exactly what I was looking for! Sometimes you can't figure out the way to do something but you know there must be a way.

 

Now that I can get my min and max dates back from the same dataset I can eliminate 2 additional queries back to Oracle. Speeds things up quite a bit.

 

There is one last query I would like to make against this dataset but I can't figure out how to word the thing.

The SQL is as follows:

 

strSQL = "SELECT COUNT(*) FROM Table WHERE MOD_DATE > (SELECT MAX(CONV_DATE) FROM Table)"

 

In a nutshell, what I am trying to do is get a count of the number of records that have been modified since the last conversion. Can this kind of SQL be compressed into the Compute method?

 

BTW, this is the code I wound up going with:

 

maxModDate = ds.Tables(0).Compute("MAX(MOD_DATE)", "")

maxConvDate = ds.Tables(0).Compute("MAX(CONV_DATE)", "")

 

The second parameter for Compute is a filter. But I have filtered already in my command statement.

[VBAHole22]

It took some futzing but I think I finally got it to work

 

 

'Compute some values from the returned set

maxModDate = ds.Tables(0).Compute("MAX(MOD_DATE)", "")

maxConvDate = ds.Tables(0).Compute("MAX(CONV_DATE)", "")

Me.txtRecentMod.Text = CType(maxModDate, String)

Me.txtRecentTran.Text = CType(maxConvDate, String)

 

'Number of drawings modified since last conversion

strExpr = "Count(MOD_DATE)"

strSort = "MOD_DATE > #" & CType(maxConvDate, Date) & "#"

modDrawings = ds.Tables(0).Compute(strExpr, strSort)

 

 

Thanks for the help