Question Value on type Integer not found

[lkrterp]

Hi All!

I am receiving the following error message: Public member 'Value' on type 'Integer' not found. Getting the error on -- custNum = CInt(dt.Rows(i).Item("cust_customer").Value) -- line

Can anyone help?

My code is :

 Dim custNum As Integer
           Dim svcResult As String
sSQL = "SELECT DISTINCT cust_customer, an_group "
            sSQL += "FROM ar_customer, ar_altname, fo_site "
            sSQL += "WHERE an_cust = cust_customer "
            sSQL += "AND fosi_customer = an_customer "
            sSQL += "AND fosi_sub_type IN (SELECT fosi_sub_type FROM         twgi_wiz_acct_type WHERE is_commercial = 'Y') "
            sSQL += "AND fosi_sales_type <> 'CL' "

            dt = fDB.GetResultRows(sSQL)

            For i = 0 To dt.Rows.Count - 1
            custNum = CInt(dt.Rows(i).Item("cust_customer").Value)
            svcResult = svc.GetAging(custNum)

Thanks

 

[cjard]

I do believe that dt.Rows(i).Item("cust_customer") is already an integer.

This is what happens when you don't use typed datasets, and also rely on VB's automagical boxing/unboxing - makes things a little confusing


Your SQL there also contains some fairly horrendous newbie errors.
Try to avoid use of DISTINCT; it gradually breeds a laziness of "Oh, my query is returning multiple repeated rows, I'll just DISTINCT them out rather than go and look for the place where I made a cartesian join error"
Never, use IN in the way you do here unless you are absolutely sure that the subquery will not return a list of rows longer than you would be prepared to type by hand

This query will likely perform better:

SELECT 
  cust_customer, an_group
FROM 
  [B](SELECT cust_customer FROM ar_customer GROUP BY cust_customer)[/B]
  INNER JOIN
  ar_altname
  ON 
    an_cust = cust_customer 

  INNER JOIN
  fo_site fs
  ON
    fosi_customer = an_customer 

  INNER JOIN
  twgi_wiz_acct_type w
  ON
    w.fosi_sub_type = fs.fosi_sub_type

WHERE
  w.is_commercial = 'Y' AND 
  fosi_sales_type <> 'CL'

Note the line in bold is where I have ASSUMED that the table contains repeated rows. If this is not the table that causes multiple repeated rows that youre DISTINCTing out, use the same logic on the table that IS

i.e. do a nested subselect first, to reduce the number of rows

If a table contained a million repeated rows, it is daft to have it join all million rows to other tables, then toss away all the duplicates because it just did 999,999 joins needlessly. Better to select the smallest number of rows FIRST, then join them to others.

Your DB may be smart enough to rewrite your query, but in a case like this, where DISTINCT is just used to blast all rows, I do doubt it. Better that you put the intelligence into your query first off

 

[cjard]

Similarly, where you want to write NOT IN

SELECT * FROM my_table WHERE my_col NOT IN (SELECT some_col FROM other_table)


Do:

SELECT my_table.* FROM my_table LEFT OUTER JOIN other_table ON my_col = some_col WHERE some_col IS NULL


(Which means "join the two tables, inserting blanks wherever the join fails, then return me all rows where the join failed because the left side was not in the right side)

The problem is rooted in SQL constructs that function how we would expect a result when talking about it. It's natural to say "give me every item in this list that is NOT IN this list" vs "connect these two lists together using these columns, then tell me everything from this list that doesn't have a matching item in the other list" -> Don't code like you speak