Problem executing Stored Procedure from ASP

Hi all,
I must be overlooking the obvious (apologies) but can't seem to figure out
why I'm unable to execute the following (where both parameters are 'int'
datatype:
#######
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#######
The error that I receive is: "Microsoft OLE DB Provider for SQL Server
error '80040e14'. Syntax error or access violation."
The procedure (which simply calls a UDF, below) executes successfully in QA
when I set the parameter values; maybe I need to 'unlearn' the quotes
syntax from Access, or...?
#######
ALTER FUNCTION dbo.udf_User_MessagesFunction
(@.UID int, @.Target int)
RETURNS TABLE
AS
RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
FROM dbo.vw_User_MessagesView
WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND (MessageID
NOT IN
(SELECT DISTINCT MessageID
FROM tblArchivedMessages
WHERE UserID IN
(SELECT DISTINCT
UserID
FROM
tblArchivedMessages
WHERE UserID
= @.UID))) )
#######
Suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.comIf the values are ints this should work for you, i think one of the session
parameters is NULL, try to print or Response.write the Commandtext which is
concatenated. If you cant do it, run the profiler to see what kind of
values are sent to the server. There must be an error in the commandtext
like "SP_proc ,1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b8688508a3d4e9a947810f1e57f0ecc@.SQ
droptable.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com|||Also, adCmdStoredProc is for designating stored procedures as the source of
the CommandText. If you are adding parameters after the name of your SP,
this is no longer true and you must use adCmdText instead.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:1b8688508a3d4e9a947810f1e57f0ecc@.SQ
droptable.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks for the input. I'm finding that trying to execute the SP and pass
the parameters from ASP like this simply doesn't work (with any SPs):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#####
So for further testing I re-wrote it like this and it works without any
problem (using 'Parameters.Refresh' here for the sake of simplicity):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Session("UserID")
objCommand.Parameters(2).Value = Session("UserGroupID")
set objRS = objCommand.Execute
set objCommand = Nothing
#####
What has me really puzzled is that the original code worked when using an
Access '02 back end; but problems since the change to SQL Server 2000 SP3.
And it's not just this one, but with all that pass a parameter.
Is there something I've done wrong with the syntax in some way, or with
quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
quite the same between Access and SQL Server?
All thoughts welcome. Thanks.
Message posted via http://www.droptable.com|||This link describes using parameterized queries.
1191519.html" target="_blank">http://www.experts-exchange.com/Pro...>
1191519.html
You're not actually using adCmdStoredProc format as specified for SQL
Server, since you're appending the parameter values to the end of the
CommandText. As Sylvain pointed out you're using adCmdText format.
adCmdStoredProc format with Parameters is safer in general. For instance,
imagine the following scenario:
Session("UserID") = "10"
Session("UserGroupID") = "1; SELECT * FROM master.dbo.syscomments;"
This is SQL Injection, and is particularly an issue when user input is
passed to a SQL command.
Access Jet and SQL Server have some differences in the way in which they
handle commands; it looks like Jet is more 'forgiving' in this instance.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:ad4750a2b6684595b0e6bdf10483b7f3@.SQ
droptable.com...
> Thanks for the input. I'm finding that trying to execute the SP and pass
> the parameters from ASP like this simply doesn't work (with any SPs):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> So for further testing I re-wrote it like this and it works without any
> problem (using 'Parameters.Refresh' here for the sake of simplicity):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Session("UserID")
> objCommand.Parameters(2).Value = Session("UserGroupID")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> What has me really puzzled is that the original code worked when using an
> Access '02 back end; but problems since the change to SQL Server 2000 SP3.
> And it's not just this one, but with all that pass a parameter.
> Is there something I've done wrong with the syntax in some way, or with
> quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
> quite the same between Access and SQL Server?
> All thoughts welcome. Thanks.
> --
> Message posted via http://www.droptable.com