Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Convert This CrossTab Stored Procedure to A Function (MS SQL Server 2000)

Hi..
Please help me convert this CrossTab Stored Procedure to A Function of MSSQL Server 2000.

Objective:
I want to display these results in my ASP webpage. I tried to execute the stored procedure in my ASP page, but I am not getting any results.

Code:
http://www.paladn.com/dynamic-crosstab-improved.htm

Regs
Jawahar
0
jawahar_prasad
Asked:
jawahar_prasad
  • 4
  • 3
1 Solution
 
nmcdermaidCommented:
Is the problem that you want this re written as a function, or that you want it to work in your ASP page? Becasue we could rewrite it as a function but if the proble is with your data or your calling code then you'll still get no data.
0
 
jawahar_prasadAuthor Commented:
I want to call it from asp page and display the results! But learnt from sources that, if it is a stored procedure returning a table then it is not possible to fetch data from ASP. So, I thought I will convert this to a function...

It tried:
mysql=" exec up_crossTab 'select * from tbl', ....... "

But it returns no result ! But, If i execute the same in Query Analyzer, then I get the results in the results window :-(
0
 
nmcdermaidCommented:
Thats untrue. You can call a stored procedure from ASP and see its returning recordset.
Here's one example:
http://authors.aspalliance.com/stevesmith/articles/sprocs.asp
 
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jawahar_prasadAuthor Commented:
It doesnt return any rows when executed thru asp! But returns when executed through Query analyzer! The database is the same.

I have attached my code snippet!
<table width="100%"  border="0" cellpadding="5" cellspacing="1" class="tbl" id="tbl_filter">
<%
Dim objConn
Dim objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
objConn.Open ConnString
mysql="exec iprompt..up_CrossTab 'SELECT * from rmon_ctab','comp_name','sum(results)[]','chk_item'"
objRs.Open mysql, objConn, adOpenStatic
if objRs.eof=false then
	iRecFirst=0
	iRecLast=objRs.recordcount
%>
  <tr>
    <td align="center" class="heading1">Checklist Item </td>
	<%for j=1 to objRs.Fields.count-2%>
    <td align="center" class="heading1"><%=objRs(j).Name%></td>
	<%next%>
  </tr>
<%
	For I = iRecFirst To iRecLast-2
%>
    <tr>
    <td width="50" align="center" class="heading"><%=objRs(0)%></td>
		<%for j=1 to objRs.Fields.count-2%>
		<td width="50" align="center" class="heading"><%if trim(objRs(j))="1" then
			response.Write "Yes"
		elseif trim(objRs(j))="0" then
			response.Write "No"
		elseif trim(objRs(j))="-1" then
			response.Write "NA"
		end if%></td>
		<%next%>
  </tr>
<%
	objRs.movenext
	next
%>
<%
end if
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
%>
</table>

Open in new window

0
 
nmcdermaidCommented:
It doesnt return any rows when executed thru asp! But returns when executed through Query analyzer! The database is the same.
That means there is an error in your ASP code. There may be a number of reasons that its not working. Here's two that I can think of:
1. objRs.recordcount may return 0 even though there are records in the recorset
2. You need to use a command and parameter object to call the SP
I have altered and attached code which will work out whether 1 is the issue. It doesn't rely on RecordCount to extract the records.
Really the best way to troubleshoot this is to use debugging in Visual Studio to inspect the recordset object and see if it really does or doesn't have data.
 
 

<table width="100%"  border="0" cellpadding="5" cellspacing="1" class="tbl" id="tbl_filter">
<%
Dim objConn
Dim objRs
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
objConn.Open ConnString
mysql="exec iprompt..up_CrossTab 'SELECT * from rmon_ctab','comp_name','sum(results)[]','chk_item'"
objRs.Open mysql, objConn, adOpenStatic
if objRs.eof=false then
	iRecFirst=0
	iRecLast=objRs.recordcount
%>
  <tr>
    <td align="center" class="heading1">Checklist Item </td>
	<%for j=1 to objRs.Fields.count-2%>
    <td align="center" class="heading1"><%=objRs(j).Name%></td>
	<%next%>
  </tr>
<%
	Do Until objRs.eof
%>
    <tr>
    <td width="50" align="center" class="heading"><%=objRs(0)%></td>
		<%for j=1 to objRs.Fields.count-2%>
		<td width="50" align="center" class="heading"><%if trim(objRs(j))="1" then
			response.Write "Yes"
		elseif trim(objRs(j))="0" then
			response.Write "No"
		elseif trim(objRs(j))="-1" then
			response.Write "NA"
		end if%></td>
		<%next%>
  </tr>
<%
	objRs.movenext
	loop
%>
<%
end if
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
%>
</table>

Open in new window

0
 
jawahar_prasadAuthor Commented:
its still not returning any rows...
0
 
nmcdermaidCommented:
OK next thing to try is to use a command object to run the SP. Try the attached code... we're basically debugging your ASP code here.
There still may actually be records in the objRS recordset - ASP and VBScript is not my first calling, there might be bugs in my code. Really the best thing to do is try and inspect objRS from Visual Studio if you can work out how.

<table width="100%"  border="0" cellpadding="5" cellspacing="1" class="tbl" id="tbl_filter">
<%
Dim objConn
Dim objRs
Dom oCom
 
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRs = Server.CreateObject("ADODB.Recordset")
Set oCom = CreateObject("ADODB.Command")
 
objConn.Open ConnString
objConn.DefaultDatabase = "iprompt"
Set oCom.ActiveConnection = objConn
oCom.CommandText = "up_CrossTab"
oCom.CommandType = 4
oCom.Parameters.Refresh
oCom.Parameters(1) = "'SELECT * from rmon_ctab'"
oCom.Parameters(2) = "'comp_name'"
oCom.Parameters(3) = "'sum(results)[]'"
oCom.Parameters(4) = "'chk_item'"
 
Set objRs. = oCom.Execute
 
objRs.Open mysql, objConn, adOpenStatic
if objRs.eof=false then
	iRecFirst=0
	iRecLast=objRs.recordcount
%>
  <tr>
    <td align="center" class="heading1">Checklist Item </td>
	<%for j=1 to objRs.Fields.count-2%>
    <td align="center" class="heading1"><%=objRs(j).Name%></td>
	<%next%>
  </tr>
<%
	For I = iRecFirst To iRecLast-2
%>
    <tr>
    <td width="50" align="center" class="heading"><%=objRs(0)%></td>
		<%for j=1 to objRs.Fields.count-2%>
		<td width="50" align="center" class="heading"><%if trim(objRs(j))="1" then
			response.Write "Yes"
		elseif trim(objRs(j))="0" then
			response.Write "No"
		elseif trim(objRs(j))="-1" then
			response.Write "NA"
		end if%></td>
		<%next%>
  </tr>
<%
	objRs.movenext
	next
%>
<%
end if
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing
%>
</table>

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now