LinuxGold
Posts: 2
|
| Posted: 06/06/2005, 5:09 AM |
|
Here are my code:
<%
cmd.CommandText="DECLARE " & _
" @min_date smalldatetime, " & _
" @max_date smalldatetime " & _
"SET " & _
" @min_date='2005-05-01' " & _
"SET " & _
" @max_date='2005-06-04' " & _
"SELECT " & _
" Associates.LastName + ', ' + Associates.FirstName as Associate_Name, " & _
" Associates.shift as shift, " & _
" QA.Batch, " & _
" QAErrors.ErrorTypeID, " & _
" count(ErrorTypeID) as total " & _
"INTO " & _
" ##assoc_score_tmp " & _
"FROM " & _
" Associates,QA " & _
"JOIN " & _
" Batch ON (Batch.QAID=QA.ID) " & _
"JOIN " & _
" QAErrors ON (QAErrors.ID=Batch.QEID) " & _
"WHERE " & _
" KeyDate " & _
" BETWEEN " & _
" @min_date " & _
" AND " & _
" @max_date " & _
"AND " & _
" Associates.ID=QA.OperatorID " & _
"GROUP BY " & _
" Associates.Shift,Associates.LastName,Associates.FirstName,QA.Batch,QAErrors.ErrorTypeID " & _
"ORDER BY " & _
" Associates.LastName,Associates.FirstName,QA.Batch,QAErrors.ErrorTypeID; " & _
"SELECT " & _
" Associate_Name as Associate_Name, " & _
" Shift, " & _
" Batch, " & _
" SUM(CASE ErrorTypeID WHEN 5 THEN total ELSE null END) as BD, " & _
" SUM(CASE ErrorTypeID WHEN 6 THEN total ELSE null END) as CR, " & _
" SUM(CASE ErrorTypeID WHEN 15 THEN total ELSE null END) as DOB, " & _
" SUM(CASE ErrorTypeID WHEN 3 THEN total ELSE null END) as EIA, " & _
" SUM(CASE ErrorTypeID WHEN 2 THEN total ELSE null END) as EIN, " & _
" SUM(CASE ErrorTypeID WHEN 14 THEN total ELSE null END) as GID_ADD, " & _
" SUM(CASE ErrorTypeID WHEN 12 THEN total ELSE null END) as GID_ELIG, " & _
" SUM(CASE ErrorTypeID WHEN 13 THEN total ELSE null END) as GID_Name, " & _
" SUM(CASE ErrorTypeID WHEN 16 THEN total ELSE null END) as GID_SIG, " & _
" SUM(CASE ErrorTypeID WHEN 8 THEN total ELSE null END) as IM, " & _
" SUM(CASE ErrorTypeID WHEN 11 THEN total ELSE null END) as M_UPC, " & _
" SUM(CASE ErrorTypeID WHEN 17 THEN total ELSE null END) as OO_DUP, " & _
" SUM(CASE ErrorTypeID WHEN 10 THEN total ELSE null END) as PA_CCE, " & _
" SUM(CASE ErrorTypeID WHEN 7 THEN total ELSE null END) as PH, " & _
" SUM(CASE ErrorTypeID WHEN 9 THEN total ELSE null END) as PT, " & _
" SUM(CASE ErrorTypeID WHEN 1 THEN total ELSE null END) as SC, " & _
" SUM(CASE ErrorTypeID WHEN 4 THEN total ELSE null END) as Scert, " & _
" SUM(CASE ErrorTypeID WHEN 18 THEN total ELSE null END) as SV " & _
"FROM " & _
" ##assoc_score_tmp " & _
"GROUP BY " & _
" Shift,Associate_Name,Batch " & _
"ORDER BY " & _
" shift,Associate_Name,Batch; " & _
"DROP TABLE ##assoc_score_tmp; "
set rs=cmd.execute
objArry=rs.GetRows 'Line 76 here....
set rs=nothing
%>
SQL statement worked in T-SQL query analyzer, but couldn't show up in ASP with error as following:
Error Type:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
/Ind_by_batch_report_beta_6.asp, line 76
Anyone know what I'm doing wrong?
_________________
Power to people, Linux is here. |
 |
 |
tecolotemx
|
| Posted: 06/06/2005, 7:53 AM |
|
Where is you ADODB.Recordset ????
You need to create an object, that should have a explicit connection to a DataBase, like this (for SQL Server):
Set objConn = Server.CreateObject( "ADODB.Connection" )
'your connection string
objConn.Open "PROVIDER=SQLOLEDB;DATASOURCE=yourdbODBC;UID=youruser;PWD=yoursecretpass"
Then, you need to create a RecordSet.
'making recordset
set objrs=server.createObject("adodb.recordset")
And last you will need to create your SQL query.
'making query for seraching the email address from database
sql="select * from dbo.users where email= '"&eid&"' "
objrs.open sql,objConn,1,2
But man, I think that you need to read a little more, and try to do some exercices in DreamWeaver and CodeCharge to understand your own specific config.
|
|
|
|
LinuxGold
Posts: 2
|
| Posted: 06/06/2005, 8:09 AM |
|
I just found the solution. I missed this line in SQL statement:
"SET NOCOUNT ON " & _
to be inserted after "SET" group, it now works.
I'm trying to figure out why that line affects the way ASP handles SQL queries.
_________________
Power to people, Linux is here. |
|
|
|
|
