VB6 RDO - could not find object SQL Server

Posted on 2011-10-17
Medium Priority
Last Modified: 2012-06-27
In my quest to convert an old app to connect to a SQL database instead of an Oracle database, I have hit another snag. I have a list that is supposed to populate with items the user needs to ship. However, the list doesn't fill. It worked for a connection to Oracle 9i database, but not for SQL. Here is a code sniplet I found that might be the culprit:
    Set Q2Set = DBCon.OpenResultset(Query, rdOpenDynamic, rdConcurRowVer)
    Do While Not Q2Set.EOF
        CurrentSKU = Q2Set.rdoColumns(Shipping_PickItems_SKU)
        If IsSerializable(CurrentSKU) Then
Say "Adding to needing"
            AddToNeeding Q2Set.rdoColumns("SUM(" & Shipping_PickItems_Quantity & ")"), CurrentSKU, PickListNumber

********************AddtoNeeding Sub**************************************
Public Sub AddToNeeding(ByVal Quantity As Integer, ByVal SKU As String, ByVal PNum)

'procedure to expand order quanty into individual records in the TempPickItemsTable
    Dim N As Integer
    'add additional row as needed
        'loop here for adding fields to table
        For N = 1 To Quantity
            Say "Adding " & Str$(N)
            ProcessNeeding N, SKU, "", PNum
        Next N
End Sub
I put a break point in and when I hover in the line
AddToNeeding Q2Set.rdoColumns("SUM(" & Shipping_PickItems_Quantity & ")"), CurrentSKU, PickListNumber

I get "Object Collection: could not find item".

The table and column the program is trying to reference is there, but is not picking it up. Does anyone have any suggestions?

I am using VB6 with RDO to connect to a SQL Server 2008 DB (formally an Oracle 9i).  
Question by:mossmis
  • 2
  • 2

Expert Comment

ID: 36982913
take a look at the contents of "query" when this line is run:
 Set Q2Set = DBCon.OpenResultset(Query, rdOpenDynamic, rdConcurRowVer)
you may also be looking at a small table name/referencing variance, not knowing the db layout it's hard to tell.

Wait I may be seeing it, your trying to reference a column named:
SUM(" & Shipping_PickItems_Quantity & ")"
if we assume that Shipping_PickItems_Quantity is a string with a number in it, like "5" then your looking for the q2set query to have a column named sum(5), in SQL server if your performing a sum operation unless you specifically name the resukting column it won't have a name, ie:
Select Sum(qty) from mytable
will return a resultset with no column names to get that to return a column name like your looking for it would have to be
Select Sum(qty) as [sum(5)] from mytable

if this dosen't guide to your answer I would need to know the contents of "query" and the value of "Shipping_PickItems_Quantity" to further assist.


Author Comment

ID: 36986759
I tried adding the "as" clause without any luck.  Here is the full funtion that is running:
Public Sub NewInit()
        'open file to save data
        On Error Resume Next
        Close #1
        Open App.Path & "\Latest.dat" For Output As #1
        'Pick List Number
        Print #1, PickListNumber
    lblCustomer(0).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_SoldTo)
        'Sold to
        Print #1, lblCustomer(0).Caption
    lblCustomer(1).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_ShipAddress1)
        'Address 1
        Print #1, lblCustomer(1).Caption
    lblCustomer(2).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_ShipAddress2)
        'address 2
        Print #1, lblCustomer(2).Caption
    lblCustomer(3).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_ShipCity)
        Print #1, lblCustomer(3).Caption
    lblCustomer(4).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_ShipState)
        Print #1, lblCustomer(4).Caption
    lblCustomer(5).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_ShipZip)
        Print #1, lblCustomer(5).Caption
    lblFFLNumber(1).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_SoldFFL)
        Print #1, lblFFLNumber(1).Caption
        If IsNull(CustQSet.rdoColumns(Shipping_Customers_FFL_Expiration)) Then
            lblFFLDate(1).Caption = ""
            FFLExDate = CustQSet.rdoColumns(Shipping_Customers_FFL_Expiration)
            'If (FFLExDate + 46 < Now()) Then
             ' lblFFLDate(1).ForeColor = "&H00FF0000&"
            'End If
            lblFFLDate(1).Caption = Format(FFLExDate, "mmmm d, yyyy")
        End If
        Print #1, lblFFLDate(1).Caption
    'write data to title
    lblTitle.Caption = "Sales Order for Pick List Number " & PickListNumber
    'turn title blink off & assign value to title
    'get Pick List Items from PickListItems table
    Query$ = "SELECT "
    Query$ = Query$ & Shipping_PickItems_SKU & ", "
    Query$ = Query$ & " SUM(" & Shipping_PickItems_Quantity & ") "
    Query$ = Query$ & " FROM " & ShippingPickItemsTableName$
    Query$ = Query$ & " WHERE "
    Query$ = Query$ & Shipping_PickItems_PickList & " = '" & PickListNumber & "' "
    Query$ = Query$ & " GROUP BY " & Shipping_PickItems_SKU
Say "Getting Pick Items"
    Set Q2Set = DBCon.OpenResultset(Query, rdOpenDynamic, rdConcurRowVer)
    Do While Not Q2Set.EOF
        'determine SKU of item in pick list
        CurrentSKU = Q2Set.rdoColumns(Shipping_PickItems_SKU)
        'determine if item is serializable......
        If IsSerializable(CurrentSKU) Then
            'assign to grid Needing
Say "Adding to needing"

            AddToNeeding Q2Set.rdoColumns("SUM(" & Shipping_PickItems_Quantity & ") as [sum(2)]"), CurrentSKU, PickListNumber
Say "Back from Adding to needing"
            pnlNeeding.Visible = True
        Else  'NOT a firearm & NO serial number needed
            'assign to right grid
Say "Adding to NOT needing"
            AddToNotNeeding Q2Set.rdoColumns("SUM(" & Shipping_PickItems_Quantity & ")"), Q2Set.rdoColumns(Shipping_PickItems_SKU)
            pnlNotNeeding.Visible = True
'#### add write of non-serializable QTY & SKU to file ....
            Print #1, "" & Q2Set.rdoColumns("SUM(" & Shipping_PickItems_Quantity & ")")  'quantity
            Print #1, "" & Q2Set.rdoColumns(Shipping_PickItems_SKU)   'SKU
Say "Back from Adding to NOT needing"
        End If
    Close #1
End Sub
The table the data is being picked from is customer_pick_items
here is the test data I put in it:

pick_list_number       SKU       quantity
       P0001                50120               2
       P0001                50120               1
       P0001                43000               1
       P0002                50278               1

The query picks up the other data in columns: pick_list_number, sku, but not quantity
Please let me know if you need more info.

Accepted Solution

mensoid earned 2000 total points
ID: 36986934
I think I'm close, I'm going to give you two possible answers, I'm still missing a few details but we'll get there. First you mis-understood where to put the "as" clause.

what I was suggesting was:
Query$ = "SELECT "
    Query$ = Query$ & Shipping_PickItems_SKU & ", "
    Query$ = Query$ & " SUM(" & Shipping_PickItems_Quantity & ")  as [sum(" & Shipping_PickItems_Quantity & ")]"
    Query$ = Query$ & " FROM " & ShippingPickItemsTableName$
    Query$ = Query$ & " WHERE "
    Query$ = Query$ & Shipping_PickItems_PickList & " = '" & PickListNumber & "' "
    Query$ = Query$ & " GROUP BY " & Shipping_PickItems_SKU

I'm not sure where this variable is getting it's value: Shipping_PickItems_Quantity
Are the column names you listed in the sample data correct? If so I see the issue, and we're on the wrong track.
Looking at teh overal snippet I think you may have two issues, I think your getting lost in the strings in code, vs the SQL result. try this as an asnwer overall:
Query$ = "SELECT "
    Query$ = Query$ & Shipping_PickItems_SKU & ", "
    Query$ = Query$ & " SUM(quantity) as  sum_Shipping_PickItems_Quantity "
    Query$ = Query$ & " FROM " & ShippingPickItemsTableName$
    Query$ = Query$ & " WHERE "
    Query$ = Query$ & Shipping_PickItems_PickList & " = '" & PickListNumber & "' "
    Query$ = Query$ & " GROUP BY " & Shipping_PickItems_SKU

and then also:
AddToNeeding Q2Set.rdoColumns("sum_Shipping_PickItems_Quantity"), CurrentSKU, PickListNumber

Author Closing Comment

ID: 36988886
The solution worked, I had the the as clause in the wrong spot. The second option worked. I got pass that spot and came across another spot that is causing an issue. I will tinker with the other issue for a while then I might open another case. Thanks again!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question