Solved

Manipulating SQL results in VB 2005

Posted on 2006-11-15
7
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

728 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