Multiple Returns, but one Row.. how do I read them -> Resolved

[RudyL]

I am using a single SQL to get several values: Count, Average, stdDev, Min, and MAX.

I am using the command and reader style. Example:

Command.Commandtext = SQLstring
Reader = Command.ExecuteReader

While Reader.Read = True
     debug.writeline(reader.getvalue(0))
end while

The problem is that I am only able to read the Count result.. How do I see them all?

Rudy

[techgnome]

That's because .Read reads the next record. And all you've printed out is just the first col of each row....

While Reader.Read = True
     debug.writeline(reader.getvalue(0)) ' Get Col 0 - Count
     debug.writeline(reader.getvalue(1)) ' Get Col 1 - Average
     debug.writeline(reader.getvalue(2)) ' Get Col 2 - stdDev
     debug.writeline(reader.getvalue(3)) ' Get Col 3 - Min
     debug.writeline(reader.getvalue(4)) ' Get Col 4 - MAX
end while

Tg

[Tool]

i don't think this is the best or most proficent way to do it but i use an oledbdatareader. i compared it with a dataadapter and the reader is faster so i use it.

but an example of that would be

Dim Conn as new OleDbConnection(connstring)
Dim Cmd as new OleDbCommand(query, Conn)
Dim Rdr as OleDbDataReader

Conn.Open()
Rdr = Cmd.ExecuteReader
While Rdr.Read()
     FirstValue = Rdr.GetValue(0)
     SecondValue = Rdr.GetValue(1)
     'so on and so on...
     'if a field might be NULL then you need to trap for it or you get an
     'error so use
     If Not IsDbNull(Rdr.GetValue(2)) Then ThirdValue = Rdr.GetValue(3)
End While
Rdr.Close
If Conn.State = ConnectionState.Open Then Conn.Close

hope that helps

[RudyL]

I tried that and it did not work.. But it might have been after it "moved" onto what would have been the next row.. I am going to try again but it will take some time to run.. I'll be back in a few..

[techgnome]

Well, all I can say is that I've been doing that for a while now (using readers) and the code I posted (and tool's too) should work. I don't have .NET in front of me at this moment for me to verify it, but that looks right.

Tg

[RudyL]

ok, it is working that way .. kinda..

My first problem was exactly what I thought, I move on before trying it.. So that is fine.. Now my problem is that the AVG, and stdDev return numbers as long as 24 places after the decimal.. (.339677419354838709677419) and that causes an over flow error.. I tried a few things but couldn't get it to work.. Ay thoughts?

[Tool]

the only thing I can think of is that its a wierd division in the end? I am not sure I guess it all depends on how many numbers it is averaging and the final number.

The only thing I can suggest is you try the Math.Round function...

Math.Round(CrazyNumber, 10)

substitute the 10 for how many decimal places you want to go over. That should fix that problem.

[RudyL]

the only thing I can think of is that its a wierd division in the end? I am not sure I guess it all depends on how many numbers it is averaging and the final number.

The only thing I can suggest is you try the Math.Round function...

Math.Round(CrazyNumber, 10)

substitute the 10 for how many decimal places you want to go over. That should fix that problem.

Nope... Not that.. It's the .GetValue(X) proerty.. Oracle is unable to place the value into it..

Is it possible to tell the SQL to round the numer before returning it?

SELECT AVG(X) from X

[Tool]

There is a round function in oracle. I am not at work so I cannot test it right now, but

http://www.techonthenet.com/oracle/f.../round_nbr.htm

that website seems to show ya how to do it. Seems like the same thing in vb.

I am guessing you would do

SELECT count, round(AVG, 10), whatever
FROM table

[RudyL]

There is a round function in oracle. I am not at work so I cannot test it right now, but

http://www.techonthenet.com/oracle/f.../round_nbr.htm

that website seems to show ya how to do it. Seems like the same thing in vb.

I am guessing you would do

SELECT count, round(AVG, 10), whatever
FROM table

Excelent! That is what it took!

Thanks for the great find