November 24, 2007 at 11:18 pm
Hai,
Can you help me in returning a boolean as output parameter in sql server stored procedure ..
I tried it this way but does'nt work..
//stored Procedure//
Create PROCEDURE [UsernameExists]
@pUsername nvarchar(50),
@Exists bit output
AS
BEGIN
SET NOCOUNT ON;
If Exists(Select Username from Table1 Where Username=@pUsername)
Select @Exists=1
Else
Select @Exists=0
END
//asp.net vb code//
Public Shared Function UserExists(ByVal Username As String) As Boolean
Dim RetValue As Boolean
Try
Data.ClsDBMain.EnsureConnectionOpen()'my helper class
Data.ClsDBMain.CreateCommand("UsernameExists", CommandType.StoredProcedure)
With Data.ClsDBMain.Command
Helper.AddParam(.Parameters, "@pUsername", Username)
Helper.AddParam(.Parameters, "@Exists", RetValue)
.ExecuteNonQuery()
RetValue = .Parameters("@Exists").Value
End With
Catch Exp As Exception
Throw New ApplicationException(Exp.ToString & "Stored procedure fail to execute")
RetValue = False
Finally
Data.ClsDBMain.EnsureConnectionClosed()
End Try
Return RetValue
End Function
Thanxs in Advance
Regards
Abu
November 25, 2007 at 4:56 am
http://www.sqlservercentral.com/scripts/Miscellaneous/31639/
The order of adding the parameters to the cmdobject is important ! (= order of parameters as used with the create procedure)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 25, 2007 at 10:14 pm
Hai
Yes i have followed the order, and no any exception or error raises the the only prob is , boolean value doest not returns
Thankx
November 25, 2007 at 11:30 pm
I think you need to specify the type of parameter as both input and output in the .net code when adding parameters.
--Ramesh
November 26, 2007 at 3:32 am
There seems to be a problem with your SP. You did not state what value you want to return.
Try this...
Create PROCEDURE [UsernameExists]
@pUsername nvarchar(50),
@Exists bit output
AS
BEGIN
SET NOCOUNT ON;
If Exists(Select Username from Table1 Where Username=@pUsername)
Set @Exists=1
Else
Set @Exists=0
Return @Exists
END
I have given this is quick test and seems to work. Unfortunately I can't comment on your application code, you will need to test this.
Hope this helps 😉
November 26, 2007 at 3:37 am
Sorry I forgot to mention something. In your stored procedure you did not state what value to return, thus the SP always returned a false value (zero). This is the reason why the SP does not work and may also be the reason why your app does not seem to work.
November 26, 2007 at 5:35 am
there was no problem with the proc.
using Return @Exists just means that you use the value of var @Exists as procedure-return-code.
DECLARE @rc int
DECLARE @pUsername nvarchar(50)
DECLARE @Exists bit
-- TODO: Set parameter values here.
EXECUTE @rc = [PPM].[dbo].[UsernameExists]
@pUsername
,@Exists OUTPUT
PRINT @rc -- procedure return code
PRINT @Exists -- output var content
.net declareations that should work
declare your commandobject ...
.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.int, , ReturnValue))
.Parameters.Add(New SqlParameter("@pUsername", SqlDbType.nvarchar, 50, Input)).value = rij.Item("pUsername")
.Parameters.Add(New SqlParameter("@Exists", SqlDbType.bit, , InputOutput)).value = rij.Item("Exists")
----
Try
myDAL.DAL.ExecCmd(sqlcmd, True)
If sqlcmd.Parameters("@RETURN_VALUE").Value.ToString = "0" Then
if sqlcmd.parameters(@Exists).value.tostring = "1" then .... else ... end
Else
rc = CType(sqlcmd.Parameters("@RETURN_VALUE").Value.ToString, Integer)
Throw New Exception("Invalid RETURN_VALUE")
End If
Catch ex As Exception
rc = -1
Throw New Exception(ex.Message, ex)
End Try
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 27, 2007 at 3:05 am
Thankx Ramesh
As you mentioned i forget to specify the Direction 'Output' in .net code
now it works fine...
Thanks to everybody for your reply.
Regards
Abu.
November 27, 2007 at 3:09 am
I'm glad, i could help...
--Ramesh
November 27, 2007 at 3:33 am
thanks for the feedback.
I'm glad you got it working.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply