Solved

Manipulating SQL results in VB 2005

Posted on 2006-11-15
7
269 Views
Last Modified: 2010-04-30
Hi again

Expect to see alot of me over the next week or so (coding a new app for work)

I have the following string passed as an sql query to our server

  Dim flightdatetime1 As String = "SELECT flight, DepDate, deptime, arrdate, arrtime FROM dbo.Leg  WHERE Aircraft = '" + lblaircraft1.Text + "' AND DepDate >='" + System.DateTime.Now.AddDays(-1) + "' AND DepDate <= '" + System.DateTime.Now.AddDays(14) + "'order by flight, depdate, deptime"
   
The results returned would be for example....

Flight         Depdate       deptime        arrdate          arrtime
1021         15/11/2006   15:36           15/11/2006    18:40
1021         15/11/2006   18:50           15/11/2006    21:40
1021         15/11/2006   21:50           15/11/2006    23:40
1027         16/11/2006   12:30           16/11/2006    14:00
1027         16/11/2006   15:36           16/11/2006    18:40
1027         16/11/2006   18:50           17/11/2006    21:40
1027         16/11/2006   21:50           19/11/2006    23:40
1058         17/11/2006   15:36           19/11/2006    18:40
1058         17/11/2006   18:50           19/11/2006    21:40
1058         17/11/2006   21:50           19/11/2006    23:40

What i need to do is get my VB app to count the number of rows starting at the top until the flight changes (pass this number to another sub form as an integer) then work out the hours (as an integer)and then minutes(as an integer) from the depdate/deptime of the first row to the arrdate/arrtime of the last row for that flight. Then move on to the next flight and do the same.

Any help is much appreciated.
0
Comment
Question by:dr_dudd
  • 4
  • 3
7 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 17946461
Hi dr_dudd,

Are you returning this info to a datareader or a datatable?

Tim Cottee
0
 
LVL 2

Author Comment

by:dr_dudd
ID: 17946611
The info is passed to a data reader i believe this is what i have so far in the way of code

Dim flightdatetime1 As String = "SELECT flight, DepDate, deptime, arrdate, arrtime FROM dbo.Leg  WHERE Aircraft = '" + lblaircraft1.Text + "' AND DepDate >='" + System.DateTime.Now.AddDays(-1) + "' AND DepDate <= '" + System.DateTime.Now.AddDays(14) + "'order by flight, depdate, deptime"
        Dim getflight1 As New SqlCommand(flightdatetime1, cnafm)
        Try
            cnafm.Open()
            Dim flight1 As SqlDataReader = getflight1.ExecuteReader
            With flight1
                If .HasRows Then

i am going to be passing the info as a set of coordinates to draw a rectange on screen
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 17946931
dr_dudd,

You could actually do all this in a single query you know.

"Select flight,convert(varchar(5), DateAdd(s,Sum(datediff(s,depdt,arrdt)),0),114) As FlyingTime, Substring(convert(varchar,dateadd(s,datediff(s,min(depdt),Max(arrdt)),0),120),9,20) As ElapsedTime From
(Select
    flight,depdate + deptime As DepDT, arrdate+arrtime As ArrDT from leg) leg
Group By flight
Where Aircraft = '" + lblaircraft1.Text + "' AND DepDate >='" + System.DateTime.Now.AddDays(-1) + "' AND DepDate <= '" + System.DateTime.Now.AddDays(14) + "'"

This gives you the flight number, flying time (difference between each departure and arrival) and elapsed time(difference between first departure and last arrival)


Tim
0
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
LVL 2

Author Comment

by:dr_dudd
ID: 17947649
Thanks Tim help is much appreciated.

although if i leave the query as is i get an unable to parse string error message.

If i move the group by flight to the end of the statement it works but ignores my <= and >= depdate options.
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 500 total points
ID: 17947692
dr_dudd,

Had the where clause in the wrong place:

"Select flight,convert(varchar(5), DateAdd(s,Sum(datediff(s,depdt,arrdt)),0),114) As FlyingTime, Substring(convert(varchar,dateadd(s,datediff(s,min(depdt),Max(arrdt)),0),120),9,20) As ElapsedTime From
(Select
    flight,depdate + deptime As DepDT, arrdate+arrtime As ArrDT from leg Where Aircraft = '" + lblaircraft1.Text + "' AND DepDate >='" + System.DateTime.Now.AddDays(-1) + "' AND DepDate <= '" + System.DateTime.Now.AddDays(14) + "') leg
Group By flight"

Tim
0
 
LVL 2

Author Comment

by:dr_dudd
ID: 17947733
Thanks Tim dont think i could have done this one without your help. SQL strings are not my strong point ;-)
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 17947826
dr_dudd,

No problem, you could have done it the other way as well of course but this seems like a simpler way overall.

Tim
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel object stays open 19 73
Convert VB6 MSXML2.ServerXMLHTTP process to C# 2 46
VB6 - Convert HH:MM into Decimal 8 54
How to read File Date Created using VB6 8 39
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question