1 query with 2 sort-functions?

[Drygast]

I have a form where I fetch some data in an accessdatabase. I would like to
present the result as 2 sets of data (I'm a newbie so I don't really know
how to express what I'm really looking for so bear with me). One set should
be sorted by "ordernumber" and the order set should be sorted by "date".
I understand this can be done through a sql-query "...order by odernumber",
but how do I type the code to execute 2 queries?

Here's the code I'm using:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

'Rensa fältet om det finns information

lblOrder2.Text = ""

lblIn2.Text = ""

lblUt2.Text = ""

lblAtgard2.Text = ""

lblSumma2.Text = ""

lblTotalSumma.Text = ""

oOrder = ""

oDagar = Nothing

oCount = Nothing

lbldagar.Text = ""

If chkMalmo.Checked = False And chkSkarholmen.Checked = False And
chkAnge.Checked = False Then

MsgBox("Du har glömt ange labb")

GoTo avsluta

Else

If chkMalmo.Checked = True Then

oLabb = "Malmö"

End If

If chkSkarholmen.Checked = True Then

oLabb = "Skärholmen"

End If

If chkAnge.Checked = True Then

oLabb = "Ånge"

End If

End If

'Hämta data

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Program Files\Smalfilm\smalfilm.mdb"

Dim cmd As New OleDbCommand("SELECT [Order].* FROM [Order] WHERE
Order.Status = 'Färdig' AND Order.Fakturerad = 0 AND Skickat_datum <= #" &
txtTo.Text & "# AND Labb = '" & oLabb & "' ORDER BY Order.Ordernummer", New
OleDbConnection(strConn))

cmd.Connection.Open()

Dim myReader As OleDbDataReader = cmd.ExecuteReader()

Try

While myReader.Read()

Dim TS As New TimeSpan

Dim NumDays As Integer

Dim StartDate

Dim EndDate

StartDate = myReader.GetDateTime(3)

EndDate = myReader.GetDateTime(6)

TS = EndDate.Subtract(StartDate)

NumDays = CInt(TS.TotalDays.ToString) + 1

lblOrder2.Text = lblOrder2.Text & myReader.GetValue(1) & vbCrLf

If oOrder = "" Then

oOrder = "'" & myReader.GetValue(1) & "'"

Else

oOrder = oOrder & " OR Ordernummer = '" & myReader.GetValue(1) & "'"

End If

lblIn2.Text = lblIn2.Text & myReader.GetDateTime(3).ToShortDateString &
vbCrLf

lblUt2.Text = lblUt2.Text & myReader.GetDateTime(6).ToShortDateString &
vbCrLf

lblAtgard2.Text = lblAtgard2.Text & myReader.GetValue(8) & vbCrLf

lblSumma2.Text = lblSumma2.Text & myReader.GetValue(14) & vbCrLf

lblTotalSumma.Text = Val(lblTotalSumma.Text.Replace(",", ".")) +
Val(myReader.GetValue(14).Replace(",", "."))

lbldagar.Text = lbldagar.Text & NumDays.ToString & vbCrLf

oDagar = oDagar + NumDays

oCount = oCount + 1

NumDays = Nothing

End While

Finally

myReader.Close()

cmd.Connection.Close()

End Try

Dim temp

If Not oDagar = Nothing Then

temp = oDagar / oCount

lblGenomsnitt.Text = temp

Else

lblGenomsnitt.Text = ""

End If

avsluta:

End Sub



Regards

/Drygast

 

[EricJ]

I'm not rally sure i'm following so if i'm talking about something else
ignore me ;p
are you trying to give the user an option to sort on date or odernumber?
if so use optionbuttons or so to let the user choose and in the code of the
button examine them, then put your order by clouse in a string and use that
string in your command
something like
if opt1.checked = true then
strorder = "ORDER BY Order.Ordernummer"
elseif opt2.checked = true then
strorder = "ORDER BY date"
else
strorder = "ORDER BY date, Order.Ordernummer"
end if

Dim cmd As New OleDbCommand("SELECT [Order].* FROM [Order] WHERE
Order.Status = 'Färdig' AND Order.Fakturerad = 0 AND Skickat_datum <= #" &
txtTo.Text & "# AND Labb = '" & oLabb & "' " & strorder , New
OleDbConnection(strConn))

if you need a seperate dataset or so you can always repeat your code for the
different dataset

hope it helps

eric

 

[Cor]

Hi Drygast,
First do us a favor and when you past code in a message, first past it to a
notepad, copy it back and paste it than in the message.

I did not read your code totaly, that does not mean I did not read it.

I can try to understand your code but I thought why is he not using a normal
dataset

"> cmd.Connection.Open()

Dim myReader As OleDbDataReader = cmd.ExecuteReader()

Dim cmd As New OledbCommand(sqlStr, Conn)
Dim dsnew As New DataSet
da = New OledbDataAdapter(cmd)
da.Fill(dsnew)

You get a table in wich every fields you have selected is reachable with
dsnew.tables(0).rows(i)("itemFromTheSelectOrTheDatabase")

That you can do the same things witch you did for your textbox, but I would
prefer a listview when I did look in a glance to your code.

(I don't think you can bind it because you do things with it, but maybe that
you even can do).

The listview can be sorted (when you have to sort it on date, there is an
example somewhere on MSDN)

I hope this helps a little bit.

Cor

 

[Drygast]

Hi EricJ,

I was a bit unclear I think. This is I want to do:

I want to pull exactly the same data from the database and print it to paper
in 2 copies, the only difference is
the order. Do I have to request the data twice (with different "order
by.."-statements), closing the connection between the requests.
Or is it possible to do both requests in a single connection?

Regards
/Drygast

I'm not rally sure i'm following so if i'm talking about something else
ignore me ;p
are you trying to give the user an option to sort on date or odernumber?
if so use optionbuttons or so to let the user choose and in the code of the
button examine them, then put your order by clouse in a string and use that
string in your command
something like
if opt1.checked = true then
strorder = "ORDER BY Order.Ordernummer"
elseif opt2.checked = true then
strorder = "ORDER BY date"
else
strorder = "ORDER BY date, Order.Ordernummer"
end if

Dim cmd As New OleDbCommand("SELECT [Order].* FROM [Order] WHERE
Order.Status = 'Färdig' AND Order.Fakturerad = 0 AND Skickat_datum <= #" &
txtTo.Text & "# AND Labb = '" & oLabb & "' " & strorder , New
OleDbConnection(strConn))

if you need a seperate dataset or so you can always repeat your code for the
different dataset

hope it helps

eric

 

[Cor]

Hi Drygast,
You can use the same connection, but with a connection you don't get the
data.

When you want to read it once, you have to use a dataset, a datatable, a
simple array or any other collection, to save it first and sort it then

Otherwise you have to read, load, whatever it is twice.

Cor

 

[Drygast]

ok, thanks Cor and EricJ!

Hi Drygast,
You can use the same connection, but with a connection you don't get the
data.

When you want to read it once, you have to use a dataset, a datatable, a
simple array or any other collection, to save it first and sort it then

Otherwise you have to read, load, whatever it is twice.

Cor