SQL Multicolumn Totals With VB6
Hello,
I have an Excel Spreadsheet I am querying with VB6 and SQL. I am trying to run a report on how many individual products have sold, and what those total sales figures are by product. There are five columns dedicated to product price, so I need to be able to scan all five columns for the price, the multiply that price by the quantity sold. Then, add them all together and display them in a datagrid. How would I go about
scanning all five columns, then adding their totals up?
This code works great for summing up the product1 and multiplying it by the price for that product.
Private Sub Command4_Click()
Adodc1.RecordSource = "SELECT SUM(Price1* Quantity1)FROM [Sheet1$] WHERE Product1='Widget 1'"
Adodc1.Refresh
End Sub
This code does not work, however.
Private Sub Command4_Click()
Adodc1.RecordSource = "SELECT SUM(Price1* Quantity1)FROM [Sheet1$] WHERE Product1='Widget 1'"
Adodc1.RecordSource = "SELECT SUM(Price2* Quantity2)FROM [Sheet1$] WHERE Product2='Widget 1'"
Adodc1.RecordSource = "SELECT SUM(Price3* Quantity3)FROM [Sheet1$] WHERE Product3='Widget 1'"
Adodc1.RecordSource = "SELECT SUM(Price4* Quantity4)FROM [Sheet1$] WHERE Product4='Widget 1'"
Adodc1.RecordSource = "SELECT SUM(Price5* Quantity5)FROM [Sheet1$] WHERE Product5='Widget 1'"
Adodc1.Refresh
End Sub
Is there a way to tell the datagrid to display the result for
Adodc1.RecordSource = "SELECT SUM(Price1* Quantity1)FROM [Sheet1$] WHERE Product1='Widget 1'"
on cell 1,1, then display the result for
Adodc1.RecordSource = "SELECT SUM(Price2* Quantity2)FROM [Sheet1$] WHERE Product2='Widget 1'"
on cell 1,2, and so on like that? I could easily do totals based on that arrangement.
Thanks for any input you can provide.
[1953 byte] By [
starscrea2] at [2007-12-5 11:58:43]
# 1 Re: SQL Multicolumn Totals With VB6
I tried putting in a Listbox, setting its datasource to Adodc1, and the following code
If Adodc1.Recordset.EOF Then
MsgBox "No record found matching " & search & "!!!"
Else
Adodc1.Recordset.MoveFirst
End If
List1.Clear
Do Until Adodc1.Recordset.EOF
List1.AddItem Adodc1.Recordset.Fields(1)
Adodc1.Recordset.MoveNext
Loop
If Adodc1.Recordset.EOF And Adodc1.Recordset.RecordCount > 1 Then
MsgBox "Records Found"
End If
End Sub
I get a runtime error 3265 saying "Item cannot be found in the collection corresponding to the requested name or ordinal".
Any ideas?
Thanks!
# 2 Re: SQL Multicolumn Totals With VB6
Using Union All doesn't appear to be working either: Here is the code I am trying.
Private Sub Command4_Click()
Adodc1.RecordSource = "Select SUM(Price1* Quantity1) From [Sheet1$] Where [Product1] = 'Widget 1' Group By Product1 Union " & _
"Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' Group By Product2 Union All " & _
"Select SUM(Price3* Quantity3) From [Sheet1$] Where [Product3] = 'Widget 1' Group By Product3 Union All " & _
"Select SUM(Price4* Quantity4) From [Sheet1$] Where [Product4] = 'Widget 1' Group By Product4 Union All " & _
"Select SUM(Price5* Quantity5) From [Sheet1$] Where [Product5] = 'Widget 1' Group By Product5 "
Adodc1.Refresh
End Sub
# 3 Re: SQL Multicolumn Totals With VB6
Is there a reason you are using a dreaded Data Control?
For an explanation of why it is a bad idea, see the article Why is using bound controls a bad thing? from our Database Development FAQs/Tutorials (at the top of this forum)
List1.AddItem Adodc1.Recordset.Fields(1)
I get a runtime error 3265 saying "Item cannot be found in the collection corresponding to the requested name or ordinal". Like with most arrays, Fields starts at 0, not 1.
Using Union All doesn't appear to be working either.Why not? What is it doing?
We aren't mind readers, so things like "doesn't work" wont tell us anything - and very few of us use data controls, we are unlikely to spot likely issues (unlike with ADO code, where we can often make fairly accurate educated guesses).
Note that any kind of Union will create multiple Records of data, whereas it seems as if you want multiple Fields instead... but that is not easy to do with the data structure that you have.
# 4 Re: SQL Multicolumn Totals With VB6
Hi,
Thanks for responding. I am using the data control just because I am most familiar with as opposed to coding it by hand. If you have some code you could post to do it another way, I would definitely try that, too.
Using Union All doesn't appear to be working either: Here is the code I am trying.
Private Sub Command4_Click()
Adodc1.RecordSource = "Select SUM(Price1* Quantity1) From [Sheet1$] Where [Product1] = 'Widget 1' Group By Product1 Union " & _
"Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' Group By Product2 Union All " & _
"Select SUM(Price3* Quantity3) From [Sheet1$] Where [Product3] = 'Widget 1' Group By Product3 Union All " & _
"Select SUM(Price4* Quantity4) From [Sheet1$] Where [Product4] = 'Widget 1' Group By Product4 Union All " & _
"Select SUM(Price5* Quantity5) From [Sheet1$] Where [Product5] = 'Widget 1' Group By Product5 "
Adodc1.Refresh
End Sub
The Union code I was using is giving me an ADODC error message saying
No value given for one or more required parameters
I can try a listbox with the starting point as 0, instead of 1, like you noted.
That would still only give me the result for product 1, though. How does one loop through and get 5 different results, then put them in the Listbox, so I can total them up in the grid? Or even better, how does one loop through and get 5 different results, then put them in the grid directly. Since the total for product1 automatically shows up in cell (1,1) when the one line of code for finding product1 is used, it shouldn't be that hard to tell the Adodc control to look at the next line of code, find the result, then plug that result into cell (2,1), and then look at the third line of code, find the result, then plug that result into cell (3,1). I am open to doing this in code and axing the ADO control if someone has a suggestion there, I just need to get this done so I can tell the boss how much he has sold of a certain product.
Thanks a lot!
# 5 Re: SQL Multicolumn Totals With VB6
Thanks for responding. I am using the data control just because I am most familiar with as opposed to coding it by hand. If you have some code you could post to do it another way, I would definitely try that, too.The code-only method is often similar, but generally a bit simpler/clearer - apart from setting the initial connection, which you do with code instead of setting a property.
Apart from that, the main difference is that to get data you use recordset.Open instead of using things like .RecordSource and .Refresh
See the ADO Tutorial link in my signature for an example of the code method. The example is similar to 'normal' data control usage, but there are more articles in our FAQs which help you change it to do many other things too.
The Union code I was using is giving me an ADODC error message saying
No value given for one or more required parametersThat basically means the SQL syntax is incorrect.. not as helpful as it could be!
Try removing the "Group By" clauses (as they aren't actually used), making sure you are using the same type of Union in each case (at one point you just have "union").
Note that it is possible that Union All (and perhaps even Union) is not supported by the Excel provider, so you may need to find an alternative method.
I can try a listbox with the starting point as 0, instead of 1, like you noted.
That would still only give me the result for product 1, though. How does one loop through and get 5 different results, then put them in the Listbox, so I can total them up in the grid?Erm.. you use a Loop with .MoveNext, as in the code you posted.
# 6 Re: SQL Multicolumn Totals With VB6
Here is what I would like to do, precisely
Private Sub Command4_Click()
Adodc1.RecordSource = "Select SUM(Price1* Quantity1) From [Sheet1$] Where [Product1] = 'Widget 1' "
Adodc1.Refresh
fg.TextMatrix(0, 1) = (Adodc1.Recordset.Fields(0))
'put the result of the first query into the MSFlexGrid cell located at (0,1)
Adodc1.RecordSource = "Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' "
Adodc1.Refresh
fg.TextMatrix(0, 2) = (Adodc1.Recordset.Fields(0))
'put the result of the second query into the MSFlexGrid cell located at (0,2)
Adodc1.RecordSource = "Select SUM(Price3* Quantity3) From [Sheet1$] Where [Product3] = 'Widget 1' "
Adodc1.Refresh
fg.TextMatrix(0, 3) = (Adodc1.Recordset.Fields(0))
'put the result of the third query into the MSFlexGrid cell located at (0,3)
Adodc1.RecordSource = "Select SUM(Price4* Quantity4) From [Sheet1$] Where [Product4] = 'Widget 1' "
Adodc1.Refresh
fg.TextMatrix(0, 4) = (Adodc1.Recordset.Fields(0))
'put the result of the third query into the MSFlexGrid cell located at (0,4)
Adodc1.RecordSource = "Select SUM(Price5* Quantity5) From [Sheet1$] Where [Product5] = 'Widget 1'"
Adodc1.Refresh
fg.TextMatrix(0, 5) = (Adodc1.Recordset.Fields(0))
'put the result of the fifth query into the MSFlexGrid cell located at (0,5)
It can't be that hard to trap results from individual SQL queries and place them into individual grid cells. I don't have the knowledge on how to do it though. The FlexGrid control I am using does have a textmatrix property built in, it's just a matter of trapping the results of the SQL, then moving on to the next query. Is there a simple solution I am missing here? Thanks!
# 7 Re: SQL Multicolumn Totals With VB6
First off, when you post code please put it inside vbcode/code tags so it is displayed in a more readable way - either using the VBCode button in the post editor screen (or at the top of the Quick Reply box), or by putting them in manually, like this: ahighlight=vba code here a/highlighta
What you have got looks like it is OK, but then as I don't use data controls there may be something 'obvious' that is missing or incorrect.
# 8 Re: SQL Multicolumn Totals With VB6
Hi,
Sorry about that, I will try to format the code better in the future.
Do you have an idea of how to trap query results using code and post them to a grid (no ado control)? If I knew how to do that I could probably muddle my way though some vb samples with recordset connections and piece together something workable.
Thanks for all your help!
# 9 Re: SQL Multicolumn Totals With VB6
Once you have a Connection, something like this:
'declare and initialise variables
Dim strSQL as String
Dim objRS as ADODB.Recordset
Set objRS = New ADODB.Recordset
'open the recordset
strSQL = "Select SUM(Price1* Quantity1) From [Sheet1$] Where [Product1] = 'Widget 1' "
objRS.Open strSQL, objCN, adOpenForwardOnly, adLockReadOnly, adCmdText
'use the recordset
fg.TextMatrix(0, 1) = objRS.Fields(0).Value
'close the recordset
objRS.Close
'terminate variables
Set objRS = NothingNote that if you are re-opening the recordset (like in your code above), you can simply repeat the Open/Use/Close sections - but need to have the Initialise before it all, and the Terminate after it all.
# 10 Re: SQL Multicolumn Totals With VB6
Hi,
OK this code is working fine for product1 (the first query). I run into a problem when I try to run subsequent queries for product2 and product3 and so forth, because (I think) I don't know what to put in place of
fg.TextMatrix(0, 1) = rs.Fields(0).Value
for the second query to make the total from the second query load.
I tried calling it fg.textmatrix(0,2)=rs.fields(1).value
but I can't call it rs.fields(1), because I get an error saying "Item cannot be found in the collection corresponding to the requested name or ordinal". Earlier, you said that this is intended, because fields begin with 0. If I call the second query rs.fields(0), I get an error message saying "invalid use of null"
How do I tell the query "this is the second query, put this result in the cell specified by the TextMatrix"? I tried inserting adodc1.recordset.movenext in between the closing of the first connection and the opening of the second connection, thinking that would work, but it has no effect that i can see.
Here's the code
Private Sub Command4_click()
Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\nm1.xls;Extended Properties=excel 8.0;Persist Security Info=False"
'this is the connection string explained in the notes section.
cn.Open
Set rs = New ADODB.Recordset 'as we did with the connection
strSQL = "Select SUM(Price1* Quantity1) From [Sheet1$] Where [Product1] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
fg.TextMatrix(0, 1) = rs.Fields(0).Value
'close the recordset
rs.Close
'terminate variables
Set rs = Nothing
Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\nm1.xls;Extended Properties=excel 8.0;Persist Security Info=False"
'this is the connection string explained in the notes section.
cn.Open
Set rs = New ADODB.Recordset 'as we did with the connection
strSQL = "Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
fg.TextMatrix(0, 2) = rs.Fields(1).Value
'close the recordset
rs.Close
'terminate variables
Set rs = Nothing
End Sub
Thanks for bearing with me, these are probably dumb questions. I appreciate it.
# 11 Re: SQL Multicolumn Totals With VB6
It should again be rs.fields(0).value , as the recordset has changed (it will always point to the last query you executed).
Note that you shouldn't Set/Open the connection twice (as that wastes memory etc, and can lead to database corruption), unless you fully close it before re-opening (but that is just wasting time and code).
When you have finished with the recordset, you need to terminate it in the same way as a Recordset, eg:
cn.Close
Set cn = NothingIf you are repeatedly using the connection in various parts of your program, you can Set/Open it in Form_Load, and terminate it in Form_Unload - that way you don't need to repeat that code in each routine.
# 12 Re: SQL Multicolumn Totals With VB6
Hi,
OK, I tried this code:
Private Sub Command1_Click()
Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\nm1.xls;Extended Properties=excel 8.0;Persist Security Info=False"
'this is the connection string explained in the notes section.
cn.Open
Set rs = New ADODB.Recordset 'as we did with the connection
strSQL = "Select SUM(Price1* Quantity1) From [Sheet1$] Where [Product1] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
fg.TextMatrix(0, 1) = rs.Fields(0).Value
strSQL = "Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
fg.TextMatrix(0, 2) = rs.Fields(0).Value
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
And I get an error message saying "operation is not allowed when the object is open".
It points to the following line in my code:
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
So I delete that line, since the connection is already open.
Then I get a duplication of the value for the first query on cell (0,2)
It looks like the program is still running the query for product1, when I need it to run the query for product2. How do I tell it "stop the first query. Now, move on to the next query"?
Thanks a lot, it is really appreciated.
# 13 Re: SQL Multicolumn Totals With VB6
rs.Open doesn't open the connection, it opens the recordset (using the new query, thus getting the other data).
The problem is that you removed rs.Close from before that line, which means that the recordset is still open from your previous code (and you can't open it again while it is already open).
# 14 Re: SQL Multicolumn Totals With VB6
Hi,
OK, if i am understanding you correctly, I just need to put
rs.close
in between
Private Sub Command1_Click()
Set cn = New ADODB.Connection 'we've declared it as a ADODB connection lets set it.
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\nm1.xls;Extended Properties=excel 8.0;Persist Security Info=False"
'this is the connection string explained in the notes section.
cn.Open
Set rs = New ADODB.Recordset 'as we did with the connection
strSQL = "Select SUM(Price1* Quantity1) From [Sheet1$] Where [Product1] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
fg.TextMatrix(1, 1) = rs.Fields(0).Value
and
strSQL = "Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' "
fg.TextMatrix(2, 1) = rs.Fields(0).Value
When I do that, It says "operation is not allowed when the object is closed".
so I retype rs.open, thinking that it's a new connection, there should be a new command to open it, and i get the same duplication of data.
What part of the code should I be putting rs.open in?
Thanks!
# 15 Re: SQL Multicolumn Totals With VB6
The problem is that you've removed the rs.Open
Here's a corrected version of your previous post (with comments that may help clarify):
Private Sub Command1_Click()
'Set up the connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= C:\nm1.xls;Extended Properties=excel 8.0;Persist Security Info=False"
cn.Open
'Initialise the recordset
Set rs = New ADODB.Recordset
'Open/Use/Close the recordset
strSQL = "Select SUM(Price1* Quantity1) From [Sheet1$] Where [Product1] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
fg.TextMatrix(0, 1) = rs.Fields(0).Value
rs.Close
'Open/Use/Close the recordset
strSQL = "Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
fg.TextMatrix(0, 2) = rs.Fields(0).Value
rs.Close
'Terminate the recordset, and close/terminate the connection
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
# 16 Re: SQL Multicolumn Totals With VB6
Hi,
Thanks for posting this. When I run it, I still get a runtime error that says "invalid use of null"
and points to the following line:
fg.TextMatrix(0, 2) = rs.Fields(0).Value
Is this maybe a limitation on Excel automation? It doesn't make sense that if a recordset value is always 0, that a 0 value would cause that error.
Thanks again.
# 17 Re: SQL Multicolumn Totals With VB6
It's not a limitation of Excel (and you aren't using Excel Automation, that is very different), it is a limitation of various items (particularly controls) in VB - they can't handle Null (which means there is no value at all, not even an empty string).
For information on how to deal with it, see the article Why do I get the error "Invalid use of Null" when using field values? from our Database Development FAQs/Tutorials
# 18 Re: SQL Multicolumn Totals With VB6
Hi again,
I looked at the code you referred me to. In the sample, you posted the following:
Text1.Text = myRecordset.Fields("FieldName").Value & ""
as being the workaround to solve this issue if the column could have a null value.
So, I replaced this line in my code as follows:
'Open/Use/Close the recordset
strSQL = "Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
fg.TextMatrix(2, 1) = rs.Fields(0).Value & ""
And I no longer get the error message about the null value, but the values for sales of widget 1 are still the only ones being sent to the grid.
Product2 has about 50 sales of widget 1 with values for each sale in Price2, so they should theoretically be pulled in and sent to cell (2,1) of the grid.
Did I 'translate' this code accurately? Or is there another step I need to do? Thanks.
# 19 Re: SQL Multicolumn Totals With VB6
You've done it properly.
Unfortunately I expected this to happen, as the cause of the error is the calculation ( SUM(Price2* Quantity2) ) is for some reason returning Null.
The most likely cause of that is that for one or more rows of data (that has [Product2] = 'Widget 1') either Price2 or Quantity2 is Null (a blank cell in Excel).
# 20 Re: SQL Multicolumn Totals With VB6
Hmm... I actually have a 3rd party tool that will do search and replace, so I could append every cell in the grid with the "" characters. It's a temporary Excel file that gets killed upon unload anyway, so there wouldn't be any harm to the database.
# 21 Re: SQL Multicolumn Totals With VB6
That didn't work, I still get cell 1,1 with a total for product 1, and now I get a blank cell for cell 2,1. I guess thats a LITTLE progress, since before I only got the one cell.
One more question, if possible. The other workaround you posted in that tutorial had an if then statement. Am I using the statement you posted correctly when I do the following?
strSQL = "Select SUM(Price2* Quantity2) From [Sheet1$] Where [Product2] = 'Widget 1' "
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
If IsNull(rs.Fields(0).Value) Then
fg.TextMatrix(2, 1) = ""
Else
fg.TextMatrix(2, 1) = rs.Fields(0).Value
End If
Thanks!
# 22 Re: SQL Multicolumn Totals With VB6
You are indeed - but note that it has the same effect as the other version. Of course it does have the bonus of allowing you to specify text to show if it is Null.
To change the data you are getting, you either need to correct the data, or modify your query - but that will be simply ignoring some of the data, so is probably not a good idea.
