Jump to content
Xtreme .Net Talk

Recommended Posts

Posted (edited)

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 by VBAHole22
Wanna-Be C# Superstar
Posted

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 ?

"If someone say : "Die mortal !"... don't stay to see if he isn't." - Unknown

"Learning to program is like going out with a new girl friend. There's always something that wasn't mentioned in the documentation..." - Me

"A drunk girl is like an animal... it scream at everything like a cat and roll in the grass like a dog." - Me after seeing my girlfriend drunk and some of her drunk friend.

C# TO VB TRANSLATOR

Posted

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")

Wanna-Be C# Superstar
Posted

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 ?

"If someone say : "Die mortal !"... don't stay to see if he isn't." - Unknown

"Learning to program is like going out with a new girl friend. There's always something that wasn't mentioned in the documentation..." - Me

"A drunk girl is like an animal... it scream at everything like a cat and roll in the grass like a dog." - Me after seeing my girlfriend drunk and some of her drunk friend.

C# TO VB TRANSLATOR

  • *Experts*
Posted

You should be able to use the Compute method:

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

 

-nerseus

"I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
Posted

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.

Wanna-Be C# Superstar
Posted

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

Wanna-Be C# Superstar

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...