Hello everyone,



I'm trying to execute a stored proc from ASP to MS SQL 2000 that accepts 

two input params. I don't get an error, instead I get incorrect results.



The stored proc is:

-------------------------------------------------------------------------

CREATE PROCEDURE CountBlogComments

@comment_item int,

@location char

AS

SELECT Count(Comment_ID) AS Comments

FROM Comments

WHERE comment_location=@location AND comment_item=@comment_item;

GO

-------------------------------------------------------------------------



The ASP is:

-------------------------------------------------------------------------

set conCM = Server.CreateObject("ADODB.Command")

set conCM.ActiveConnection = conDB

conCM.CommandText = "CountBlogComments"

conCM.CommandType = adCmdStoredProc



conCM.Parameters.Append conCM.CreateParameter("comment_item", adInteger, 

adParamInput, 4, 5607075)

conCM.Parameters.Append conCM.CreateParameter("location", adChar, 

adParamInput, 20, "within")



set conRS = conCM.execute

response.write conRS("Comments")

-------------------------------------------------------------------------



When I execute this, comments = 0 even though I have 1 record in the 

database with the correct criteria.



To prove that, I used Query Analyzer to execute



SELECT Count(Comment_ID) AS Comments

FROM Comments

WHERE comment_location='within' AND comment_item=5607075;



and I got comments = 1. So the error is either in the stored proc or the 

ASP, but I just can't find it.



I hope someone can help me on this one.

Thanks.

What you are doing in your stored procedure is returning a recordset that

contains one row and one column which is the count of the number of records

in your Comments table.  Either treat the return from the stored proc as a

recordset (perhaps overly expensive/heavy for your needs) or add an output

parameter to your SP Parameter list, and store the record count in that

variable within your stored proc (ie. SELECT @n_reccnt 

COUNT(Comment_ID)....).  Then you can directly access the parameter back in

your ASP code..



The reference to Comments in your code returned 0 because ASP is treating it

as a variant/local variable that has not been initialized...



Good Luck,



Pedro Cardoso

PCDev Solutions Inc.

http://www.pcdevsolutions.com



Coming soon.. www.sqlprof.com





-----Original Message-----

From: DeltaVega [mailto:Delta@D...]

Sent: September 12, 2001 9:39 AM

To: ASP Databases

Subject: [asp_databases] Problem with multiple parameters





Hello everyone,



I'm trying to execute a stored proc from ASP to MS SQL 2000 that accepts

two input params. I don't get an error, instead I get incorrect results.



The stored proc is:

-------------------------------------------------------------------------

CREATE PROCEDURE CountBlogComments

@comment_item int,

@location char

AS

SELECT Count(Comment_ID) AS Comments

FROM Comments

WHERE comment_location=@location AND comment_item=@comment_item;

GO

-------------------------------------------------------------------------



The ASP is:

-------------------------------------------------------------------------

set conCM = Server.CreateObject("ADODB.Command")

set conCM.ActiveConnection = conDB

conCM.CommandText = "CountBlogComments"

conCM.CommandType = adCmdStoredProc



conCM.Parameters.Append conCM.CreateParameter("comment_item", adInteger,

adParamInput, 4, 5607075)

conCM.Parameters.Append conCM.CreateParameter("location", adChar,

adParamInput, 20, "within")



set conRS = conCM.execute

response.write conRS("Comments")

-------------------------------------------------------------------------



When I execute this, comments = 0 even though I have 1 record in the

database with the correct criteria.



To prove that, I used Query Analyzer to execute



SELECT Count(Comment_ID) AS Comments

FROM Comments

WHERE comment_location='within' AND comment_item=5607075;



and I got comments = 1. So the error is either in the stored proc or the

ASP, but I just can't find it.



I hope someone can help me on this one.

Thanks.

Try these suggestions:



1. Add SET NOCOUNT ON at the top of the proc and then SET NOCOUNT OFF at the

bottom.

2. Remove the trailing ";" on the SELECT Statement

3. Remove the "GO" keyword.



HTH



-- Tomm



> -----Original Message-----

> From: DeltaVega [mailto:Delta@D...]

> Sent: Wednesday, September 12, 2001 9:39 AM

> To: ASP Databases

> Subject: [asp_databases] Problem with multiple parameters

>

>

> Hello everyone,

>

> I'm trying to execute a stored proc from ASP to MS SQL 2000 that accepts

> two input params. I don't get an error, instead I get incorrect results.

>

> The stored proc is:

> -------------------------------------------------------------------------

> CREATE PROCEDURE CountBlogComments

> @comment_item int,

> @location char

> AS

> SELECT Count(Comment_ID) AS Comments

> FROM Comments

> WHERE comment_location=@location AND comment_item=@comment_item;

> GO

> -------------------------------------------------------------------------

>

> The ASP is:

> -------------------------------------------------------------------------

> set conCM = Server.CreateObject("ADODB.Command")

> set conCM.ActiveConnection = conDB

> conCM.CommandText = "CountBlogComments"

> conCM.CommandType = adCmdStoredProc

>

> conCM.Parameters.Append conCM.CreateParameter("comment_item", adInteger,

> adParamInput, 4, 5607075)

> conCM.Parameters.Append conCM.CreateParameter("location", adChar,

> adParamInput, 20, "within")

>

> set conRS = conCM.execute

> response.write conRS("Comments")

> -------------------------------------------------------------------------

>

> When I execute this, comments = 0 even though I have 1 record in the

> database with the correct criteria.

>

> To prove that, I used Query Analyzer to execute

>

> SELECT Count(Comment_ID) AS Comments

> FROM Comments

> WHERE comment_location='within' AND comment_item=5607075;

>

> and I got comments = 1. So the error is either in the stored proc or the

> ASP, but I just can't find it.

>

> I hope someone can help me on this one.

> Thanks.

I'm not sure if this is true for SQL2K, I use SQL7.



I think your problem is with the data definition of @location. The problem

is that you have defined the parameter in your command obj as Char(20) which

I will mean that "within" gets padded to "within" + 14 spaces. This would

explain the success in getting it to run in qry analyser, but not in ASP. As

I understand it char data type is used when you know that the number of

characters will be the same or very similar.



From BOL:

"The char data type is a fixed-length data type when the NOT NULL clause is

specified. If a value shorter than the length of the column is inserted into

a char NOT NULL column, the value is right-padded with blanks to the size of

the column. For example, if a column is defined as char(10) and the data to

be stored is "music", SQL Server stores this data as "music_____", where "_"

indicates a blank."



A better option is to change to data type to VarChar, which doesn't pad the

data with spaces. Also it is a good practice to give the size of the data,

which you did for the command object, but not in the sp.



Finally Pedro's suggestion that you return the value as an output parameter

is a good one.



HTH



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Tomm Matthis [mailto:matthis@b...]

------_=_NextPart_001_01C13BDF.1B55FB10--

Sorry David, I still get conRS("Comments") = 0 even when I query for

@location alone. But I do think that the problem is here somewhere. I tried

quering for @comment_item alone and it worked fine. So here are the changes

I've made:



In SQL:

@location varchar



In ASP:

conCM.Parameters.Append conCM.CreateParameter("location", adVarChar,

adParamInput, 6, "within")



The DataType for the location field in the DB is already VarChar, and the

data in these records have no spaces (Trim() takes care of that.)



I've tried everything I can think of and I'm stuck.



----- Original Message -----

From: "David Cameron" <dcameron@i...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, September 13, 2001 2:03 AM

Subject: [asp_databases] RE: Problem with multiple parameters





> I'm not sure if this is true for SQL2K, I use SQL7.

>

> I think your problem is with the data definition of @location. The problem

> is that you have defined the parameter in your command obj as Char(20)

which

> I will mean that "within" gets padded to "within" + 14 spaces. This would

> explain the success in getting it to run in qry analyser, but not in ASP.

As

> I understand it char data type is used when you know that the number of

> characters will be the same or very similar.

>

> From BOL:

> "The char data type is a fixed-length data type when the NOT NULL clause

is

> specified. If a value shorter than the length of the column is inserted

into

> a char NOT NULL column, the value is right-padded with blanks to the size

of

> the column. For example, if a column is defined as char(10) and the data

to

> be stored is "music", SQL Server stores this data as "music_____", where

"_"

> indicates a blank."

>

> A better option is to change to data type to VarChar, which doesn't pad

the

> data with spaces. Also it is a good practice to give the size of the data,

> which you did for the command object, but not in the sp.

>

> Finally Pedro's suggestion that you return the value as an output

parameter

> is a good one.

>

> HTH

>

> regards

> David Cameron

> nOw.b2b

> dcameron@i...

>

>

One more change should be made: @location should be set to VarChar(n) where

n is length of the field in the database. The length of the parameter passed

through by the command object should also be the same. This could be part of

the cause of your problem. Also have you made all the changes suggested by

Tomm?



If it is working in query analyser, but not in ASP then the problem has to

be with ADO. Add the value of @location with a delimiter at each end to the

results returned by the recordset so that you can check exactly what SQL

Server is getting. This would make you stored proc look like this:



-------------------------------------------------------------------------

CREATE PROCEDURE CountBlogComments

@comment_item int,

@location VarChar(6)

AS



SET NOCOUNT ON



SELECT Count(Comment_ID) AS Comments, '-' + @location + '-' AS Loc

FROM Comments

WHERE comment_location=@location AND comment_item=@comment_item

SET NOCOUNT OFF

-------------------------------------------------------------------------



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: DeltaVega [mailto:Delta@D...]

Sent: Thursday, 13 September 2001 3:29 PM

To: ASP Databases

Subject: [asp_databases] RE: Problem with multiple parameters





Sorry David, I still get conRS("Comments") = 0 even when I query for

@location alone. But I do think that the problem is here somewhere. I tried

quering for @comment_item alone and it worked fine. So here are the changes

I've made:



In SQL:

@location varchar



In ASP:

conCM.Parameters.Append conCM.CreateParameter("location", adVarChar,

adParamInput, 6, "within")



The DataType for the location field in the DB is already VarChar, and the

data in these records have no spaces (Trim() takes care of that.)



I've tried everything I can think of and I'm stuck.



----- Original Message -----

From: "David Cameron" <dcameron@i...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, September 13, 2001 2:03 AM

Subject: [asp_databases] RE: Problem with multiple parameters





> I'm not sure if this is true for SQL2K, I use SQL7.

>

> I think your problem is with the data definition of @location. The problem

> is that you have defined the parameter in your command obj as Char(20)

which

> I will mean that "within" gets padded to "within" + 14 spaces. This would

> explain the success in getting it to run in qry analyser, but not in ASP.

As

> I understand it char data type is used when you know that the number of

> characters will be the same or very similar.

>

> From BOL:

> "The char data type is a fixed-length data type when the NOT NULL clause

is

> specified. If a value shorter than the length of the column is inserted

into

> a char NOT NULL column, the value is right-padded with blanks to the size

of

> the column. For example, if a column is defined as char(10) and the data

to

> be stored is "music", SQL Server stores this data as "music_____", where

"_"

> indicates a blank."

>

> A better option is to change to data type to VarChar, which doesn't pad

the

> data with spaces. Also it is a good practice to give the size of the data,

> which you did for the command object, but not in the sp.

>

> Finally Pedro's suggestion that you return the value as an output

parameter

> is a good one.

>

> HTH

>

> regards

> David Cameron

Thanks everyone its working now. And I'm using an output stored proc in

stead of a record set.



Thanks again, you guys are great.