Solved

Manipulating SQL results in VB 2005

Posted on 2006-11-15
7
264 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Share codes 68 115
Problem to delete range 4 55
bit defender blocks good applications 2 55
Add a task in Outlook from access 11 32
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now