sql avg() returns null

BTMExpert
BTMExpert used Ask the Experts™
on
What do you do or how can you determine if the ADODB.Recordset returns null or nothing.  I have a querystring that works fine if a column has a value, but if it doesn't i get an error.  I want to do something if the querystring doesn't work instead of the page not working at all.  

SELECT AVG(ColumnA) AS Expr1 FROM datatable WHERE columnB = 'this' and columnC = 'that'

If i take "and columnC = 'that'" out it works or if i do "and columnC = 'acertainthat'" it works fine but if there's no value for ColumnA where columnC = 'that' i get an error.  I've tried RecordCount > 0 and Field.Count > 0 and they don't work
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
SELECT ISNULL(AVG(ColumnA), 0) AS Expr1 FROM datatable WHERE columnB = 'this' and columnC = 'that'

this should return 0 if nothing is returned as an average.
HainKurtSr. System Analyst

Commented:
how do you use this Expr1  in your code?
the code suggested by brad2575 should do the job, but if you want to do it on your code, post the code

you should use

if isnull(rs("Expr1")) then ...

or you can use

dim avg
avg = iif(isnull(rs("Expr1")),0, rs("Expr1"))
 

Author

Commented:
The problem is the value in the db has a ' in them and it's messing things up.  What can i do about that?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

HainKurtSr. System Analyst

Commented:
delete that record, update that record make it null or 0
if you are using avg(col) then it means col is numeric not string/varchar
fix your invalid data, change datatype to numeric (int, currency, money, float etc.) then you will not have an issue... if you store numeric values here, use the right datatype

Author

Commented:
i'm sorry, i meant the columnC has a value in it with a '.  so when i run my querystring, it has that ' in it which messes everything up.  I tried making it a string and tried to decode the value but it doesn't work.  Thanks for the help

Author

Commented:
Pulling my hair out
HainKurtSr. System Analyst

Commented:
:) how do you create the query...
post asp code

basically you should replace ' with '' (double ' not a single ")

src1 = replace(src1,"'","''")
src2 = replace(src2,"'","''")
sql = "SELECT AVG(ColumnA) AS Expr1 FROM datatable WHERE columnB = '" & src1 & "' and columnC = "' & src2 & "'"
HainKurtSr. System Analyst

Commented:
and use isnull

src1 = replace(src1,"'","''")
src2 = replace(src2,"'","''")
sql = "SELECT isNull(AVG(ColumnA),0) AS Expr1 FROM datatable WHERE columnB = '" & src1 & "' and columnC = "' & src2 & "'"

Author

Commented:
SQL2 = "SELECT ISNULL(AVG(PricePointQty1), 0) AS Expr1 FROM Gallery WHERE ColumnA = 'A') AND (ColumnB = 'B') AND (ColumnC = '" & C & "')"

C = person's name

have to use " around the string since i'm using asp

Author

Commented:
I thought about replacing the ' but will it still search the same if the column i'm searching for is exact?
HainKurtSr. System Analyst

Commented:
this is the code that you should post first :)

C= replace(C, "'","''")
SQL2 = "SELECT ISNULL(AVG(PricePointQty1), 0) AS Expr1 FROM Gallery WHERE ColumnA = 'A') AND (ColumnB = 'B') AND (ColumnC = '" & C & "')"

give it a try...
Sr. System Analyst
Commented:
we are not replacing anything, just writing the query so sql understand there is ' inside text...

select * from mytable where col = 'Hain''Kurt'

it will look for "Hain'Kurt". ' --> '' other wise sql give syntax error...

Author

Commented:
Yea sorry, i didn't know that was the problem til now.  Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial