VB6 with too many SQL Select Statements

225 pts.
Tags:
Microsoft Access 1997
Visual Basic 6
Hi Team, hope you can help me out here.. I have a VB6 form that is populated using VB6 and Access being the Repository. The problem is that I am opening and closing the Database for every query I have.. Approx 87 queries. In short I have 12 Rooms that I need stats for.. Ie Grades & Size Volumes.. I need to combine these into less queries as it takes about 2 minutes to populate each field on the VB form. Extract of Query:

Call dbConnect     SQL = "SELECT Sum(Currentweight) as GradePR1 FROM Weighroom WHERE (currentdate = DateValue(Now) and Roomno = '01' And Grade = 'PR')"     rs.Open SQL, Conn     If IsNull(rs(0)) Then         TxtGradePRoom(0) = 0         Else          TxtGradePRoom(0) = rs(0)         End If     Conn.Close Call dbConnect     SQL = "SELECT Sum(Currentweight) as GradePR1 FROM Weighroom WHERE (currentdate = DateValue(Now) and Roomno = '01' And Grade = 'AP')"     rs.Open SQL, Conn     If IsNull(rs(0)) Then         TxtGradePRoom(0) = 0         Else          TxtGradePRoom(0) = rs(0)         End If     Conn.Close 


If I simply do 1 select statement with a group by Grade, How do I extract each value? This is the Group by... BUT

Call dbConnect     SQL = "SELECT Grade, Sum(Currentweight) as GradePR1 FROM Weighroom WHERE (currentdate = DateValue(Now) and Roomno = '01') Group by Grade"     rs.Open SQL, Conn     If IsNull(rs(0)) Then         TxtGradePRoom(0) = 0         Else          TxtGradePRoom(0) = rs(0)          TxtGradePRoom(1) = rs(1)         End If     Conn.Close 


The values in rs(0) is AA The Values in rs(1) is 436.38 These values are related and correct.. but I'm missing all the other values for AP, PR, CA. If I put a rs(2) or rs(-1) it fails.. I know they should be retrieved but I don't know how to access them. My DB call is:-

Global Conn As New adodb.Connection, rs As New adodb.Recordset Global onTop As New clsOnTop, i As Integer, SQL As String, Cancel As Boolean Public Sub dbConnect()   Set Conn = New adodb.Connection   Conn.ConnectionString = strConn   Conn.Open End Sub Public Function strConn() As String      strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "Greenhillmushrooms.mdb" & ";Persist Security Info=False" End Function 


Regards Witsend

Software/Hardware used:
VB6 & Access97

Answer Wiki

Thanks. We'll let you know when a new response is added.

I don’t currently have a VB6 installation to test, but if I remember well, you can use the recordset’s EOF property to control a loop to read all the rows in the recordset.

Something like this:

<pre>Call dbConnect
SQL = “SELECT Grade, Sum(Currentweight) as GradePR1 FROM Weighroom WHERE (currentdate = DateValue(Now) and Roomno = ’01′) Group by Grade”
rs.Open SQL, Conn
<b>if rs.BOF and rs.EOF then
‘do something, the recordset is empty
else
rs.MoveFirst
Do Until rs.EOF
‘do something with the current record, the same way you are currently doing it
rs.MoveNext
Loop</b>
End If</pre>

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • witsendestate
    Carlosdl.. huge thankyou.. such a simpy thing to add and it works a treat..
    225 pointsBadges:
    report
  • carlosdl
    Great. I'm glad it was helpful.
    69,510 pointsBadges:
    report
  • witsendestate
    [...] 10. Carlosdl gives Witsendestate the right answer about combining stats into less queries using Visual Basic 6. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following