Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Manipulating SQL results in VB 2005

Posted on 2006-11-15
7
Medium Priority
?
277 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

604 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