• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 815
  • Last Modified:

VB6 RDO - could not find object SQL Server

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
    LocalCon.BeginTrans
        'loop here for adding fields to table
        For N = 1 To Quantity
            Say "Adding " & Str$(N)
            ProcessNeeding N, SKU, "", PNum
        Next N
    LocalCon.CommitTrans
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).  
0
mossmis
Asked:
mossmis
  • 2
  • 2
1 Solution
 
mensoidCommented:
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.

0
 
mossmisAuthor Commented:
I tried adding the "as" clause without any luck.  Here is the full funtion that is running:
*******************************************************************************************
Public Sub NewInit()
    CleanUpTempTable
        '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)
        'city
        Print #1, lblCustomer(3).Caption
    lblCustomer(4).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_ShipState)
        'state
        Print #1, lblCustomer(4).Caption
    lblCustomer(5).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_ShipZip)
        'zip
        Print #1, lblCustomer(5).Caption
    lblFFLNumber(1).Caption = "" & CustQSet.rdoColumns(Shipping_Customers_SoldFFL)
        'FFL
        Print #1, lblFFLNumber(1).Caption
        If IsNull(CustQSet.rdoColumns(Shipping_Customers_FFL_Expiration)) Then
            lblFFLDate(1).Caption = ""
        Else
            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
    SetTitle
    '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
        Q2Set.MoveNext
    Loop
    Q2Set.Close  
    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.
0
 
mensoidCommented:
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
0
 
mossmisAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now