Recordset Syntax Error

[heprox]

I have this application that uses ODBC to retrieve a recordset from an Oracle database. Whenever I fire the Enter command I get an error with:

txtSOH.Text = rsmain_4("sum(avail_qty)")

...if I'm using a sum statement via SQL, what is the correct syntax for referencing that part of the recordset to display it in a label or textbox? Here is the larger portion of code:

strsql_soh = "select a.itm_cd, sum(c.avail_qty) from gm_itm a, gm_sku b, gm_inv_loc c" & _
        " Where a.itm_cd = b.itm_cd And b.sku_num = c.sku_num" & _
        " and a.itm_cd = '" & mskItem.Text & "' group by a.itm_cd"
    Set rsmain_4 = New ADODB.Recordset
    rsmain_4.Open strsql_soh, cn, adOpenKeyset, adLockReadOnly
    If rsmain_4.EOF Then
        MsgBox "This item does not have any current by size inventory information.", vbCritical, "Error"
        Exit Sub
    Else
        txtSOH.Text = rsmain_4("sum(avail_qty)")
    End If

...I've tried:

txtSOH.Text = rsmain_4("sum(c.avail_qty)")

...and:

txtSOH.Text = rsmain_4("avail_qty")

...and:

txtSOH.Text = rsmain_4("sum(avail_qty)")

...but I still get the sytax error?

 

[gmmastros]

I'm not sure about Oracle, but in SQL Server, you would use a Field/Column Alias. For example....

strsql_soh = "select a.itm_cd, sum(c.avail_qty) As SumOfQuantity from gm_itm a, gm_sku b, gm_inv_loc c" & _
        " Where a.itm_cd = b.itm_cd And b.sku_num = c.sku_num" & _
        " and a.itm_cd = '" & mskItem.Text & "' group by a.itm_cd"

Then, on the client (VB) side.....

txtSOH.Text = rsmain_4("SumOfQuantity")

You could also access the field by it's ordinal value...

txtSOH.Text = rsmain_4(2)

The 2 in the previous line represents the position of the field, so the 2nd field in the recordset. In my opinion, this is bad because if you change the order of the field in the select, you must change the VB code.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[heprox]

Thanks, that was exactly the problem, I was unsure if VB would take an alias like that.

 

[Golom]

It's not VB ... it's SQL. When you create a new field either as an aggregate function or as a combination of other fields, but you don't give it an alias, SQL creates one for you but you don't necessarily know what SQL called the field. That makes refering to it by name problematical.

You could still get at it without creating an alias using the field number as in

txtSOH.Text = rsmain_4.Fields(1).Value