odd error 2

[devasp]

i just added the 2 following sql commands to my coding as i need to get some data from a different database.
but it keeps throwing this error when i try to display the data from this new command:

here's the code i've used to display it

if objrs6.eof or isNull(objrs6("DisMonth")) = true or objrs6("DisMonth") = "" then
              DisMonth = "0"
              else
              DisMonth = objrs6("DisMonth")
              end if

error '80020009'
Exception occurred.

/Internal Programs/Discount/breakdown_view.asp, line 139


this line points to the sql command i named SnapshotDiscounts( ). It gets the discount value for the 1st 2 suppliers but when it tries to get it for the 3rd the error comes up.
i checked on the net and microsoft are saying it hapens when you point to a text or memo field in the table. but all my fields are set as numbers.

the supplier it points to, 'B83M', doesnt exist in the table so it should just skip that supplier and move onto the next one but isnt working for some reason.

SQL:

sub GetSuppliers( )
    SET objRs5 = SERVER.createObject( "ADODB.recordSet" )
    
    dim mysql
    
    mysql = mysql + ""
    mysql = mysql + "SELECT Supplier, [net-turnover@"& myNewMonth &"] AS MonthTO "
    mysql = mysql + "FROM usalanalysis "
    mysql = mysql + "WHERE Customer = '"& objrs("Customer") &"' "
    mysql = mysql + "AND [year-number] = '" & myYear & "' "
    mysql = mysql + "ORDER BY Supplier"
        
    'response.write(mysql)
    'response.end
        
    objRs5.OPEN mysql,objConn,1,3
end sub


sub SnapshotDiscounts( )
    SET objRs6 = SERVER.createObject( "ADODB.recordSet" )
    
    dim mysql
    
    mysql = mysql + ""
    mysql = mysql + "SELECT Supplier, [Sett@"& myNewMonth &"] AS DisMonth FROM Settlement_Out "
    mysql = mysql + "WHERE [Year-Number] = "& myYear &" "
    mysql = mysql + "AND Supplier = '"& objrs5("Supplier") &"' "    
    mysql = mysql + "ORDER BY Supplier"
        
    response.write(mysql)
    'response.end

    objRs6.OPEN mysql,objConn3,1,3
end sub

 

[monksnake]

What are the data types of the columns you are pulling in SnapshotDiscounts?



<.

 

[monksnake]

More specific, what is the data type for Supplier in the table Settlement_Out?

<.

 

[devasp]

the supplier field is set as text and the rest are all numbers.

 

[monksnake]

Hmm, ok. Put the supplier field last in your query.

mysql = mysql + ""
    mysql = mysql + "SELECT [Sett@"& myNewMonth &"] AS DisMonth[!], Supplier[/!] FROM Settlement_Out "
    mysql = mysql + "WHERE [Year-Number] = "& myYear &" "
    mysql = mysql + "AND Supplier = '"& objrs5("Supplier") &"' "    
    mysql = mysql + "ORDER BY Supplier

That should be all you need. I read that here:

classicasp.aspfaq.com/general/why-do-i-get-80020009-errors.html

<.

 

[devasp]

i've read the same thing as well but it doesn't work unfortunately.
I tried taking the supplier out of the query string as I'm not displaying it anywhere but that didn't work either.

I've been on many sites but they all say the same thing...that the problem is with the db but that's all fine as i use it on other reports i produce and they all work fine.

i'm stumped as to why i get this error??

 

[monksnake]

I think I know what the problem is. You are doing an ORDER BY on a text field, that is not allowed.

You can convert the Supplier field into a char or varchar and order it by that.

ORDER BY convert(varchar, Supplier)


<.

 

[devasp]

tried that doesn't work either :s

if i do a response.write on my sql it shows all suppliers but the problem is still happening when i display the data. if the supplier isn't found in the db it doesn't throw an error but instead moves to the next record.

 

[Sheco]

One problem is you need to separate it into 2 different conditional IF/THEN tests.

if objrs6.eof then
  'no match in database
  DisMonth = "0"
else
  if (objrs6("DisMonth") & "" = "") then
    'we have null or empty field value    
    DisMonth = "0"
  else
    'valid field value
    DisMonth = objrs6("DisMonth")
  end if
end if

The reason for this is that, unlike C and related languages, VB will attempt to evaluate the entire line of logic. You can't combine conditional tests with [tt]OR[/tt] or [tt]AND[/tt] if the state of one test would cause an error in the other.

 

[Sheco]

This is OK:[tt]if objrs6("DisMonth") = 0 OR objrs6("DisMonth") = 1 then[/tt]

This is NOT:[tt]if objrs6.EoF OR objrs6("DisMonth") = 1 then[/tt]

The reason the second one is not OK is that in the case that your recordset is actually EoF, there is no way it can evaluate the value of the current record, because there is no current record.

 

[devasp]

the if statement you gave me works wonders!!!!
thanks a lot you were great help!!!

 

[monksnake]

Good call Sheco, that's a difference between VBScript and Javascript.



<.

 

[Sheco]

Yes it is a difference in the languages... VB always evaluates the entire condiational statement.

I consider it a bug but MicroSoft says it is working as intended.

 

[devasp]

the statement i was using works fine in all my reports so far it's weird it had to cause a problem now.