dataset sort

  • Thread starter Thread starter thomasp
  • Start date Start date
T

thomasp

I want to use two SQL statements:

Dim strSQLLCMR As String = "SELECT DTG, POO FROM LCMR WHERE (Confirmed =
true and Friendly = False) ORDER BY DTG"
Dim strSQLQ36 As String = "SELECT DTG, POO FROM Q36 WHERE (Confirmed = true
and Friendly = False) ORDER BY DTG"

to fill one table in a dataset. Then I want to sort that dataset by the
field DTG. Is this possible?

The reason for this is that I then want to loop through the table and
determine records that have a Date/Time group (DTG) within 5 minutes of each
other.

Any suggestions welcome.


Thanks,

Thomas
 
Hi Thomas,

Use DataView on DataTable or DataTable.Select if you need only to loop
through rows.
 
Can I sort either of these other than with the SQL statement. I need them
sorted after the two tables are combined. I need the DTG's in order, so I
can test the current one against the previous one in the loop. Thanks for
such a quick response.

Thomas
 
Both DataView and DataTable.Select are executed on the data in memory and
not in database, so you load your data in the DataTable and then sort them
in memory.
 
Try using one sql statement that uses both of the ones below combined with a
"union/union all" and then use an "order by" on the combined result. This
will give one resultset that you can loop through with a datareader.
 
Thomas,
Can I sort either of these other than with the SQL statement. I need them
sorted after the two tables are combined. I need the DTG's in order, so I
can test the current one against the previous one in the loop. Thanks for
such a quick response.
Click to expand...

Is there any reason that you don't want to use the dataview or the
defaultview as Miha Suggest?

Cor
 
I am sure the dataview or defaultveiw will do just what I need. The biggest
thing that I needed from this post was the Union suggestion. That is what
got both my tables loaded up and sorted in the correct order. Now I will go
back and look at loading this in a dataview instead of a dataset.

Dim strSQLState As String = "SELECT DTG, POO FROM Q36 WHERE (Confirmed =
true and Friendly = False) " & _
"UNION SELECT DTG, POO FROM LCMR WHERE
(Confirmed = true and Friendly = False) " & _
"UNION SELECT DTG, POO FROM UTAMS WHERE
(Confirmed = true and Friendly = False) " & _
"ORDER BY DTG"

If anyone wondered what I was trying to do.....

Once I have these in a dataset or dataview sorted by Date/Time group, then I
loop through each row comparing the DTG to the previous row. If the DTG is
within 5 minutes, then I check the Point of Origin (POO) if they are within
1000 meters I consider them to be the same attack. By doing this I hope to
have a function that will loop through 1000's of radar acquisitions from 3
different radar systems and give me a count of separate attacks.
 
Thomas,

I get the idea that you don't know what is a dataset and what a datatable.

A dataset is an object that holds by instance datatables and datarelations.

Datatables in a datases have a property "Defaultview".
This DefaultView has again two important properties
Sort and Rowfilter.

By setting the Sort from the DefaultView you can Sort the tables in a
dataset.
(And show them by using that defaultview, you have than not datarows,
however datarowviews,althought they can be directly be referenced to the
datarowview using the property datarow in that ).

You can as well create more views by using dataviews, which are extra
defaultviews.

Beside that you can as well for your purpose create DataRelations, with what
you can connect the datatables using dataitems (columns) .

Have a look on MSDN for what I wrote.

I hope this helps,

Cor
 
Cor,

I must not be doing a bad job of explaining what I am doing or I am
just having trouble understanding all the help I am being given. This
particular problem concerns 3 tables in the database: LCMR, Q36, and
UTAMS. There are only two columns that I am concerned with in each of
these tables: DTG and POO. Each table is around 4000 records and grows
daily.

I am loading what is needed from each table into the dataSet using 3
SQL statements and 3 dataAdapters.

Dim strSQLLCMR As String = "SELECT DTG, POO FROM LCMR WHERE (Confirmed =
true and Friendly = False) ORDER BY DTG"
Dim strSQLQ36 As String = "SELECT DTG, POO FROM Q36 WHERE (Confirmed = true
and Friendly = False) ORDER BY DTG"
Dim strSQLUTAMS As String = "SELECT DTG, POO FROM UTAMS WHERE (Confirmed =
true and Friendly = False) ORDER BY DTG"

daLCMR = New OleDb.OleDbDataAdapter(strSQLLCMR, myConnection)
daQ36 = New OleDb.OleDbDataAdapter(strSQLQ36, myConnection)
daUTAMS = New OleDb.OleDbDataAdapter(strSQLUTAMS, myConnection)

When either of the radars track an attack they may track multiple
projectiles, usually 6 to 8 acquisitions. This adds 6 to 8 entries to
the databasae table. An individual attack is over in less than a minute
most times. Ok, so now I have a dataSet with 3 tables and only the rows
of confirmed acquisitions that were not friendly fire in date time order.
I setup a for next loop to go through each table and compare the current
record to the previous record. If the date/time is within 5 minutes or
the Point of Origin (POO) is within 1000 meters the code considers the
acquisition to be the same. So I have a loop like this for each table in
the dataset.

intLCMRIDF = 0
For Each drLCMR In ds.Tables("LCMR").Rows

If intLCMRIDF = 0 Then
intLCMRIDF = 1
datCurDate = CDate(drLCMR.Item(0).ToString)
intCurEast =
CInt(Microsoft.VisualBasic.Left(drLCMR.Item(1).ToString, 5))
intCurNorth =
CInt(Microsoft.VisualBasic.Right(drLCMR.Item(1).ToString, 5))
End If

If datCurDate > DateAdd(DateInterval.Minute, 5,
CDate(drLCMR.Item(0).ToString)) Or _
datCurDate < DateAdd(DateInterval.Minute, -5,
CDate(drLCMR.Item(0).ToString)) Then

intLCMRIDF += 1
datCurDate = CDate(drLCMR.Item(0).ToString)
intCurEast =
CInt(Microsoft.VisualBasic.Left(drLCMR.Item(1).ToString, 5))
intCurNorth =
CInt(Microsoft.VisualBasic.Right(drLCMR.Item(1).ToString, 5))

ElseIf intCurEast >
CInt(Microsoft.VisualBasic.Left(drLCMR.Item(1).ToString, 5)) + 1000 Or
_
intCurEast <
CInt(Microsoft.VisualBasic.Left(drLCMR.Item(1).ToString, 5)) - 1000
Or _
intCurNorth >
CInt(Microsoft.VisualBasic.Right(drLCMR.Item(1).ToString, 5)) + 1000
Or _
intCurNorth <
CInt(Microsoft.VisualBasic.Right(drLCMR.Item(1).ToString, 5)) - 1000
Then

intLCMRIDF += 1
datCurDate = CDate(drLCMR.Item(0).ToString)
intCurEast =
CInt(Microsoft.VisualBasic.Left(drLCMR.Item(1).ToString, 5))
intCurNorth =
CInt(Microsoft.VisualBasic.Right(drLCMR.Item(1).ToString, 5))

End If

Next

Now I have a count of how may attacks each radar has tracked.

intLCMRIDF = 4
intQ36IDF = 46
intUTAMSIDF = 2

I do not know how many total attacks have occured. Adding these counts
together would not give that because the two tracked by the UTAMS radar was
probably tracked by the Q36 radar also. The same case with the 4 tracked by
the LCMR. So I needed a way to have all three tables combined and sorted by
DTG so I could go through all of them at once and get a total count. Which
wound up being 47. To do this I loaded created another SQL statement and
dataAapter:

Dim strSQLState As String = "SELECT DTG, POO FROM Q36 WHERE (Confirmed =
true and Friendly = False) " & _
"UNION SELECT DTG, POO FROM LCMR WHERE
(Confirmed = true and Friendly = False) " & _
"UNION SELECT DTG, POO FROM UTAMS WHERE
(Confirmed = true and Friendly = False) " & _
"ORDER BY DTG"

daCombine = New OleDb.OleDbDataAdapter(strSQLState, myConnection)


And loaded it into the dataSet in yet another table:

Dim intTotalIDF As Integer = daCombine.Fill(ds, "IDF")

Now I can compare all the tables at once with another For Each / Next loop.
Seems to me like alot of code to do a simple task, but this is where I am at
and it works. I am learning as I go and will gladly take advice and try it
in my situation. It has not taken my long to find that there are 100's of
ways to do different tasks in code. My coding usually strays toward making
it work and then trying to make in more efficent. Probably not a good way,
but a way. This group has been very helpful in my learning and I hope that
you continue.

Thanks,

Thomas
 
Thomas,

There are probably more posibilities first this one, why do you not first
select a certain datepart?

(Although I am in doubt if what you are doing is quick enough).

http://www.windowsformsdatagridhelp.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071

And otherwise use the rowfilter to select a part of your tables.
http://msdn.microsoft.com/library/d...rlrfsystemdatadataviewclassrowfiltertopic.asp

The expression information is very flaw in this page especially for dates.
http://msdn.microsoft.com/library/d...fsystemdatadatacolumnclassexpressiontopic.asp

Be aware that it uses completly non globalized USA DateTime standards.

And than a litteral string for that.

I hope this helps something to find your solution.

Cor
 
I just can't see the examples you sent doing what I need done, but thanks.
As for speed, it seem to process the code I have instantly.


Thanks,

Thomas
 
Back
Top