CODEWITHSUNDEEP
sqlasp-classicvbscriptsql-server-2000
Leroy Mikenzi
Leroy Mikenzi

Reputation: 810

how to store query result in a variable and dispaly it using classic ASP

I'm using SQL server 2000 and classic ASP to display result using table i.e in HTML. I'm trying to get the sum of value of a column records present in SQL table . For that I tried this.

Balance Leave =<%
                        sql= "select SUM('no_of_leave_taken') from emp_leave_details where emp_no='"&request.QueryString("id")&"'"  
                        rs.open sql,con,1,2
                        response.Write(con.execute sql)
                        rs.close
                        %>

the error shown in the browser is

The sum or average aggregate operation cannot take a varchar data type as an argument.

Upvotes: 0

Views: 2206

Answers (2)

Leroy Mikenzi
Leroy Mikenzi

Reputation: 810

I tried this instead, figured it out myself <% sql= "select SUM(no_of_leave_taken) as total from emp_leave_details where emp_name='"&request.QueryString("name")&"'" rs.open sql,con,1,2 response.Write(31- rs("total")) %> It was something new for me to kow ...I was confused as the expression "total" in the above query worked for me and calling as a new column field virtually in the recordset using rs("total") thanks @glenatron for your help

Upvotes: 1

glenatron
glenatron

Reputation: 11362

That looks like an SQL error - what happens if you run that query in the SQL Server Enterprise Manager? Is emp_details.no_of_leave_taken a numeric field?

One other thing I will add- PLEASE DON'T EVER USE UNVERIFIED QUERYSTRING VALUES - read this ASP-friendly explanation of SQL Injection attacks for a bit of explanation on why.

Upvotes: 2

Related Questions