I don't get the correct result

I don't get the correct result

Postby Zero-G. » Sat 09 Dec 2006 12:37

Hey
Since today, I do have the professional Version with the latest build.
In VB 2005 I do use the following code:
Code: Select all
        Dim myDataReader As CoreLab.MySql.MySqlDataReader

        'Die Daten in die Info Boxen einarbeiten
        mySQLConnection.Open()

        'Alle Aufträge zeigen

        'HANDELSWARE
        Dim HWOff As Integer = 0
        Dim HWFak As Integer = 0
        'Brille
        Dim BrilleOff As Integer = 0
        Dim BrilleFak As Integer = 0
        'Kontaktlinse
        Dim CLOff As Integer = 0
        Dim CLFak As Integer = 0


        mySQLCommand.CommandText = "Select @HWOffen:=Count(KundenID) From " & _
            "AuftragHandelsware where KundenID=" & CDbl(lblKundenNummer.Text) & _
            " and Bewegung=0;" & _
            "Select @HWFakturiert:=Count(KundenID) From " & _
            "AuftragHandelsware where KundenID=" & CDbl(lblKundenNummer.Text) & _
            " and Bewegung=1;" & _
            "Select @BROffen:=Count(KundenID) From " & _
            "AuftragBrille Where KundenID=" & CDbl(lblKundenNummer.Text) & _
            " and Bewegung=0 and left(BrillenArt, 2) <> 'CL';" & _
            "Select @BRFakturiert:=Count(KundenID) From " & _
            "AuftragBrille Where KundenID=" & CDbl(lblKundenNummer.Text) & _
            " and Bewegung=1 and left(BrillenArt, 2) <> 'CL';" & _
            "Select @CLOffen:=Count(KundenID) From " & _
            "AuftragBrille Where KundenID=" & CDbl(lblKundenNummer.Text) & _
            " and Bewegung=0 and left(BrillenArt, 2)='CL';" & _
            "Select @CLFakturiert:=Count(KundenID) From " & _
            "AuftragBrille Where KundenID=" & CDbl(lblKundenNummer.Text) & _
            " and Bewegung=1 and left(BrillenArt, 2)='CL';" & _
            "Select @HWOffen as HWOffen, @HWFakturiert as HWFakturiert,  " & _
            "@BROffen as BROffen, @BRFakturiert as BRFakturiert, " & _
            "@CLOffen as CLOffen, @CLFakturiert as CLFakturiert;"

        mySQLCommand.Connection = mySQLConnection
        myDataReader = mySQLCommand.ExecuteReader

        If myDataReader.Read = True Then
           
            HWOff = myDataReader.Item(0)
            HWFak = myDataReader.Item(1)
            BrilleOff = myDataReader.Item(2)
            BrilleFak = myDataReader.Item(3)
            CLOff = myDataReader.Item(4)
            CLFak = myDataReader.Item(5)
        End If
        myDataReader.Close()
        mySQLConnection.Close()

The Problem is, that the DataReader only contains one Filed!
When I try the command in the MySQL Control Center it works correctly.

What can I do?

Yours faithfully[/code]
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey » Mon 11 Dec 2006 09:16

This is a designed behaviour.
There are few resultsets in your statement.
Use NextResult() method like this:
Code: Select all
            myDataReader.NextResult()
            HWFak = myDataReader.Item(0)
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Zero-G. » Mon 11 Dec 2006 09:39

Hey

First, thanks to your reply.
But it won't work. - If I use the NextReuslt()methode, I get the following error: No data exists for the row/column.

So, I tried to change the SQL Statement to:
Code: Select all
Select Concat(@HWOffen, '/' , @HWFakturiert ,'/',  " & _
            "@BROffen, '/', @BRFakturiert, '/', " & _
            "@CLOffen,  '/', @CLFakturiert) as Auftrag

And then, I tried to split the result. - But this is also not working. - I only get the Result: 1 without any "/" - The 1 is the correct response for @HWOffen.

PLEASE HELP.... - THX
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey » Mon 11 Dec 2006 10:23

You should perform Read() as well after calling NextResult() method.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Zero-G. » Mon 11 Dec 2006 10:43

OK!

THX - That's it! - Works great!
Zero-G.
 
Posts: 390
Joined: Sat 09 Dec 2006 12:20

Postby Alexey » Mon 11 Dec 2006 10:49

You're welcome:)
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL