Stored Proc ... not outputting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Proc ... not outputting

Author

Topic

JimiC
Starting Member

29 Posts

Posted - 2003-05-26 : 11:43:12

ok, im going to post the DDL and such so that people can test this, but the fact is it works perfectly in query analyzer, its only calling it from asp thats causing problems.

tables:
---------------------------------------------------------

CREATE TABLE [dbo].[data_Alumno_Cursos_Registro] (
[Curso_Estado_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Alumno_Curso_ID] [int] NOT NULL ,
[Capitulo_ID] [int] NOT NULL ,
[Tema_ID] [int] NOT NULL ,
[Tema_Completado_Fecha] [datetime] NOT NULL ,
[Tema_Includes_Exam] [bit] NOT NULL ,
[Tema_Completado_Nota] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[data_Curso_Capitulos] (
[Capitulo_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Curso_ID] [int] NOT NULL ,
[Capitulo_Nombre] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Capitulo_Numero] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[data_Curso_Control] (
[Curso_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Curso_Nombre] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Curso_Dueno] [int] NOT NULL ,
[Curso_Nota_Minima] [int] NOT NULL ,
[Categoria_ID] [int] NOT NULL ,
[Curso_Precio] [int] NOT NULL ,
[Curso_Estado] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[data_Curso_Temas] (
[Tema_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Capitulo_ID] [int] NOT NULL ,
[Tema_Nombre] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Tema_Numero] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[data_User_Cursos] (
[User_Curso_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Usuario_ID] [int] NOT NULL ,
[Curso_ID] [int] NOT NULL ,
[Curso_Fecha_Inicio] [datetime] NOT NULL ,
[Curso_Fecha_Fin] [datetime] NULL
) ON [PRIMARY]
GO

sample data:
---------------------------------------------------------

well i cant see how to generate this automatically so .. if i make a typo hopefully ill edit the post before anyone spots it ;)

INSERT INTO data_Curso_Control
(
Curso_Nombre,
Curso_Dueno,
Curso_Nota_Minima,
Categoria_ID,
Curso_Precio,
Curso_Estado
)
VALUES
(
'Meh!',
1,
60,
1,
50,
0
)

INSERT INTO data_Curso_Capitulos
(
Curso_ID,
Capitulo_Nombre,
Capitulo_Numero
)
VALUES
(
1, /* Assuming identity in previous table is 1*/
'Chapter 1',
1
)

INSERT INTO data_Curso_Temas
(
Capitulo_ID,
Tema_Nombre,
Tema_Numero
)
VALUES
(
1, /* Assuming identity in previous table is 1*/
'Tema 1',
1
)

INSERT INTO data_Curso_Temas
(
Capitulo_ID,
Tema_Nombre,
Tema_Numero
)
VALUES
(
1, /* Assuming identity in previous table is 1*/
'Tema 2',
2
)

INSERT INTO data_User_Cursos
(
Usuario_ID,
Curso_ID,
Curso_Fecha_Inicio
)
VALUES
(
1,
1, /* Assuming identity in data_Curso_Control table is 1*/
#01/01/1901#
)

INSERT INTO data_Alumno_Cursos_Registro
(
Alumno_Curso_ID,
Capitulo_ID,
Tema_ID,
Tema_Completado_Fecha,
Tema_Includes_Exam
)
VALUES
(
1, /* Assuming identity in data_User_Cursos table is 1*/
1, /* Assuming identity in data_Curso_Control table is 1*/
1, /* Assuming identity in data_Curso_Temas table is 1*/
#01/01/1902#,
0
)

stored proc:
---------------------------------------------------------

CREATE PROCEDURE sp_GetCoursePosition
(
@CursoID Int,
@UsuarioID Int,
@TemaID Int OUTPUT
)
AS
SELECT TOP 1
@TemaID = CT.Tema_ID
FROM
data_Curso_Capitulos CC
INNER JOIN
data_Curso_Temas CT
ON
CC.Capitulo_ID = CT.Capitulo_ID
AND CC.Curso_ID = @CursoID
INNER JOIN
data_Curso_Control CCon
ON
CCon.Curso_ID = CC.Curso_ID
LEFT JOIN
data_User_Cursos UC
ON
UC.Usuario_ID = @UsuarioID
LEFT JOIN
data_Alumno_Cursos_Registro ACR
ON
UC.User_Curso_ID = ACR.Alumno_Curso_ID
AND ACR.Tema_ID = CT.Tema_ID
WHERE
ACR.Tema_Completado_Fecha = NULL
OR
(
ACR.Tema_Includes_Exam = 1
AND ACR.Tema_Completado_Nota < CCon.Curso_Nota_Minima
)

ORDER BY
CC.Capitulo_Numero ASC,
CT.Tema_Numero ASC
GO

script:
---------------------------------------------------------

<%@ Language = VBScript %>
<% Option Explicit %>




<%
' temp
Session("ID") = 34
Session("Curso") = 4

Dim MostrarTemaID, MostrarTemaNumero, Found
Found = False

' #====#====#====#====#====#====#====#====#====#====#====#====#
' Preparar Procedimiento Almacenado para devolver posicion actual del alumno

Dim cnnStoredProc ' Connection object
Dim cmdStoredProc ' Command object

' Abrir conexion

Set cnnStoredProc = Server.CreateObject("ADODB.Connection")

cnnStoredProc.Open "Provider=SQLOLEDB;Data Source=" & DBPath & ";" _
& "Initial Catalog=Formacion;User Id=" & DBUser & ";Password=" & DBPass & ";" _
& "Connect Timeout=15;Network Library=dbmssocn;" _
& "Trusted_Connection=yes"

' Crear Objeto comando
Set cmdStoredProc = Server.CreateObject("ADODB.Command")

' Asignar el comando a la conexion
cmdStoredProc.ActiveConnection = cnnStoredProc

' definir el nombre y tipo de procedimiento
cmdStoredProc.CommandText = "sp_GetCoursePosition"
cmdStoredProc.CommandType = adCmdStoredProc

Dim paramUsuario, paramCurso

Set paramUsuario = cmdStoredProc.CreateParameter("@UsuarioID", adInteger, adParamInput)
paramUsuario.Value = Session("ID")
cmdStoredProc.Parameters.Append paramUsuario
Set paramCurso = cmdStoredProc.CreateParameter("@CursoID", adInteger, adParamInput)
paramCurso.Value = Session("Curso")
cmdStoredProc.Parameters.Append paramCurso
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@TemaID", adInteger, adParamOutput)

cmdStoredProc.Execute

Response.Write("id=" & cmdStoredProc.Parameters("@TemaID").Value & "<br>")

in theory the stored proc selects the first theme (Tema) that has not yet been completed, or has been completed with a score below that specified.

ive tried everything i can think of to make this work, i have a load of very similar procedures running fine, but this one just refuses to play :(

ive tried getting rid of the output variable and returning a recordset, ive tried removing the TOP 1 and returning all records, but it just doesnt return anything, despite executing fine in query analyzer.

clearly the most likely place for error is the script, but ive been over it again and again, and simply cant find anything wrong :(

many thanks for anyone that can spot where im going wrong

Making things work by dint of dinting.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-26 : 14:41:26

quote:
cmdStoredProc.Execute

Response.Write("id=" & cmdStoredProc.Parameters("@TemaID").Value & "<br>")

My guess is the OUTPUT parameter is not available until the recordset is closed.

Since you are not returning a recordset, try making the following change and maybe it'll work:

cmdStoredProc.Execute ,,adExecuteNoRecords

Response.Write("id=" & cmdStoredProc.Parameters("@TemaID").Value & "<br>")

Sam

JimiC
Starting Member

29 Posts

Posted - 2003-05-26 : 15:39:10

fairly sure ive tried it with adExecutenoRecords on ... but ill try it tomorrow to be sure ;)

Making things work by dint of dinting.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-26 : 16:00:31

Just like to add that you should add

SET NOCOUNT ON

to your stored procedure.

JimiC
Starting Member

29 Posts

Posted - 2003-05-26 : 18:02:28

thanks .. ive tried it with and without nocount on, if thats how it should be, ill put it back in.

Making things work by dint of dinting.

JimiC
Starting Member

29 Posts

Posted - 2003-05-27 : 05:06:58

ok, ive just checked to make sure...

with both SET NOCOUNT ON and adExecuteNoRecords ... this is not working :(

any ideas ?

Making things work by dint of dinting.

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-27 : 05:56:10

My ideas are -

Run the procedure from query analyzer with the parameters that you would expect to be passed to it from ASP, and see what you get. If you get zero rows, your problem is with the SELECT, or the data.

Get the ASP page to output what parameters it is sending to the procedure.

Try doing a response.flush or response.end in case something is holding your page from printing the output.

Try removing the code for the select from the procedure and replacing it with

SELECT @TemaID = 1

and seeing if you get the 1 returned. If so, the problem is with the select, or the data.

-------
Moo.

JimiC
Starting Member

29 Posts

Posted - 2003-05-27 : 06:55:31

Run the procedure from query analyzer with the parameters that you would expect to be passed to it from ASP, and see what you get. If you get zero rows, your problem is with the SELECT, or the data.

-------------------
yep, done that, and at this stage the values im passing to the sp are hard coded for testing. in query analyzer the correct result is produced
-------------------

Get the ASP page to output what parameters it is sending to the procedure.
-------------------
know what they are, and theyre ok
-------------------

Try doing a response.flush or response.end in case something is holding your page from printing the output.
-------------------
?? .. the page is executing without error, where would i put these commands ?
-------------------

Try removing the code for the select from the procedure and replacing it with

SELECT @TemaID = 1

and seeing if you get the 1 returned. If so, the problem is with the select, or the data.
-------------------
the 1 doesnt get returned
-------------------

whats next ? ;)

Making things work by dint of dinting.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-27 : 07:48:51

quote:
fairly sure ive tried it with adExecutenoRecords on ... but ill try it tomorrow to be sure ;)

Did you try it?

Sam

JimiC
Starting Member

29 Posts

Posted - 2003-05-27 : 08:11:18

yeah sam,

running it like that now, still doesnt work :(

----------------------
Making things work by dint of dinting.

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-27 : 08:55:28

quote:

Try removing the code for the select from the procedure and replacing it with

SELECT @TemaID = 1

and seeing if you get the 1 returned. If so, the problem is with the select, or the data.
-------------------
the 1 doesnt get returned
-------------------

In that case, I would be inclined to suggest that the SQL is fine and that the problem is with the ASP page, in which case you may get a more helpful answer at an ASP forum.

If you want to try response.flush and response.end, put them after this line

Response.Write("id=" & cmdStoredProc.Parameters("@TemaID").Value & "<br>")

I'm presuming that the bit of code you have shown is only part of the page, as there is no closing %>

Also, search the source of the page for id=, just in case it is outputting, but you don't see it in the HTML.

-------
Moo.

JimiC
Starting Member

29 Posts

Posted - 2003-05-27 : 09:40:05

thanks mist, but theres nothing getting lost in the html, im just outputting plain text that will in turn be read by flash.

so yeah there is more to the page, but no missing tags.

i agree that the problem is most likely in the asp, but, this is the same code im using to run all my stored procs, im just copying, pasting and redifining the paramters to suit.

oh well, back to waiting for the asp gurus it seems :(

----------------------
Making things work by dint of dinting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources