Failed sql SP from EnterpriseLibrary

[Guest]

Hi,

I'm attempting to run a SQL SP from ASP.NET 1.1 using the
EnterpriseLibrary. I have successfully creted a connection and am
attempting to call the ExecuteScalar method. The problem is that my SP
is always returning a value of zero when it should (under the right
conditions) return a one. When called from the QA or EM the SP does
return the correct results, it's just from the code it doesn't.

Here's my VB:

<Start Code>
Public Function Login(ByVal strUserName As String, _
ByVal strPassword As String) As Integer

Dim nID As Integer = 1
Dim sqlCommand As String
Dim dbCommandWrapper As DBCommandWrapper

sqlCommand = "procUserValidate"
dbCommandWrapper =
db.GetStoredProcCommandWrapper(sqlCommand)

Call dbCommandWrapper.AddInParameter("@vchUser",
DbType.String, strUserName)
Call dbCommandWrapper.AddInParameter("@vchPword",
DbType.String, strPassword)
Call dbCommandWrapper.AddOutParameter("@bValid",
DbType.Int16, 1)

Try
nID = CType(db.ExecuteScalar(dbCommandWrapper),
Integer)
Catch e As Exception
Throw e
End Try

Return nID
End Function
</End Code>

.... and here's the SP it's calling...

<Start SQL>
CREATE PROCEDURE procUserValidate
@vchUser typUserUsername,
@vchPword typUserPassword,
@bValid BIT OUTPUT

AS

IF EXISTS(
SELECT GUID
FROM dbo.tblUsers (NOLOCK)
WHERE Username = @vchUser
AND [Password] = @vchPword
)
SET @bValid = 1
ELSE
SET @bValid = 0
</End SQL>

I think It's something to do with the DataTypes defined in the code,
but any pointers would be appreciated.

<M>

 

[sloan]

Here is a sample stored procedure ... doing a executescalar ....

But I think you simply need to do a

select @bValid

Here is a sample. The DotNet code would be (because I am returning a bit
which is a boolean.. you'll alter of course):


public function GetIt() as boolean
Dim returnValue As Boolean = False
''Your EnterpriseLIbrary Code here
Dim o As Object = db.ExecuteScalar(dbc)
returnValue = Convert.ToBoolean(o)
Return returnValue
end function


--start tsql


USE Northwind
GO




if exists (select * from sysobjects
where id = object_id('dbo.uspProductIsDiscontinued') and sysstat & 0xf = 4)
drop procedure dbo.uspProductIsDiscontinued
GO


/*

DECLARE @return_value int

EXEC @return_value = [dbo].uspProductIsDiscontinued
@ProductID = 3

SELECT 'Return Value' = @return_value


EXEC @return_value = [dbo].uspProductIsDiscontinued
@ProductID = 5

SELECT 'Return Value' = @return_value




*/


CREATE PROCEDURE [dbo].[uspProductIsDiscontinued]

( @ProductID int )

AS
BEGIN
SET NOCOUNT ON

declare @returnValue bit




SELECT
@returnValue = Discontinued
FROM
dbo.Products p
WHERE
p.ProductID = @ProductID


select @returnValue

END

GO




GRANT EXECUTE ON dbo.uspProductIsDiscontinued TO northwinduser


GO

Hi,

I'm attempting to run a SQL SP from ASP.NET 1.1 using the
EnterpriseLibrary. I have successfully creted a connection and am
attempting to call the ExecuteScalar method. The problem is that my SP
is always returning a value of zero when it should (under the right
conditions) return a one. When called from the QA or EM the SP does
return the correct results, it's just from the code it doesn't.

Here's my VB:

<Start Code>
Public Function Login(ByVal strUserName As String, _
ByVal strPassword As String) As Integer

Dim nID As Integer = 1
Dim sqlCommand As String
Dim dbCommandWrapper As DBCommandWrapper

sqlCommand = "procUserValidate"
dbCommandWrapper =
db.GetStoredProcCommandWrapper(sqlCommand)

Call dbCommandWrapper.AddInParameter("@vchUser",
DbType.String, strUserName)
Call dbCommandWrapper.AddInParameter("@vchPword",
DbType.String, strPassword)
Call dbCommandWrapper.AddOutParameter("@bValid",
DbType.Int16, 1)

Try
nID = CType(db.ExecuteScalar(dbCommandWrapper),
Integer)
Catch e As Exception
Throw e
End Try

Return nID
End Function
</End Code>

... and here's the SP it's calling...

<Start SQL>
CREATE PROCEDURE procUserValidate
@vchUser typUserUsername,
@vchPword typUserPassword,
@bValid BIT OUTPUT

AS

IF EXISTS(
SELECT GUID
FROM dbo.tblUsers (NOLOCK)
WHERE Username = @vchUser
AND [Password] = @vchPword
)
SET @bValid = 1
ELSE
SET @bValid = 0
</End SQL>

I think It's something to do with the DataTypes defined in the code,
but any pointers would be appreciated.

<M>

 

[Guest]

sloan,

I've attempted the modification to the DotNet code you suggested, but
the result of running Dim o As Object =
db.ExecuteScalar(dbCommandWrapper) is that o = Nothing, which when
converted to an INT = 0. This is the same result regardless of hte
parameters I pass in.

The one part of your sample code you left out was the
EnterpriseLibrary code. I suspect that the parameters I have created
(as noted in the original post) through this code are incorrect and
would appreciate any feedback on the way that i'm creating these.

Regards,

<M>

 

[Guest]

sloan,

Once again you've helped by getting me to think about what I was
doing. I was so tied up with thinking that my syntax must be wrong
that I overlooked the obvious.

To double check my results I ran SQL Profiler and that showed me that
the output paramter was being set to 0 or1 correctly, even though my
app told me that it was always failing. That lead me to look deeper at
the output parameter definition, which by all accounts was fine. So
what was wrong? I was using the result of running the SP as my
function return value rather than the output parameter value (Doh!).
Including the stament nID =
dbCommandWrapper.GetParameterValue("@bValid") to the try section of my
original code was all that I needed.

Thanks for being a sounding board. You helped me again!

<M>

 

[sloan]

Yeah, that's why I like that sample I posted, with the TSQL calls to it as
well.

It forces a "think about it" attitude.

Glad you got it running.

 

[sloan]

Yeah..

@bValid BIT OUTPUT

Is a GetParameterValue thing....now its so clear !!