Solved

Manipulating SQL results in VB 2005

Posted on 2006-11-15
7
274 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MsgBox 2 64
Need Nag Screen for Reboot if system up time is over 14 days 12 96
Send outlook email from VBS Script 2 66
Modify Text File with Excel Macro 13 46
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 …
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…
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…
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…

737 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