ASP.NET a SQL Server stored procedure with return

Question

So I want to erase the guid @value from the database and retrieve the return value from my stored procedure

This is my stored procedure

ALTER PROCEDURE dbo.verifGuids
@value char(36),
@message char(1)Output
AS
Begin

DECLARE @SelectValue char(36)

select @SelectValue = value FROM guids WHERE @value=value; 

If @value=@SelectValue 
BEGIN
DELETE value FROM guids WHERE @value=value ;
SET @message = 0
END
Else 
BEGIN
Set @message = 1
END

Return @message
END

This is my vb.code in asp.net

 Dim guids As String = (Request.QueryString("ID"))
        'stored procedure get Guid
        Dim intRowsAff As Integer
        Dim connectionString As String = WebConfigurationManager.ConnectionStrings("BecsEtMuseauxSQL").ConnectionString
        Dim con As SqlConnection = New SqlConnection(connectionString)
        Dim cmd As New SqlCommand("verifGuids", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@value", SqlDbType.Char, 36).Value = guids
        Dim returnvalue As New SqlParameter("@message", SqlDbType.Char, 1)
        returnvalue.Direction = ParameterDirection.Output
        cmd.Parameters.Add(returnvalue)

        Try
            cmd.Connection.Open()
            intRowsAff = cmd.ExecuteNonQuery()
            Dim retour As String = (cmd.Parameters("@message").Value.ToString())
            If retour = 0 Then

                InformationLabel.Text = "Votre compte à été vérifier vous pouver vous connecter sur le site"
            Else

                InformationLabel.Text = "Votre compte n'est pas aprouvé veuiller vérifier dans vos mail le message de confirmation de votre compte "
            End If
        Catch ex As Exception
            InformationLabel.Text = ex.Message & ex.Source & intRowsAff '& " record(s) inserted"
        End Try


        cmd.Connection.Close()

    End If

I catch an error

object name 'value' not valid..Net SqlClient Data Provider0

I don't understand what I doing wrong...

This is in Vb.net...

Thanks

On which line do you get that error?? Maybe your query string parameter doesn't exist / is empty?? The code looks fine as far as I can tell..... — marc_s
– marc_s, Commented Jan 8, 2012 at 17:05

marc_s · Accepted Answer · 2012-01-08 20:53:16Z

2

I think you have an error in your stored procedure code:

select @SelectValue = value FROM guids WHERE @value=value;

This is wrong - it should be

SELECT @SelectValue = value FROM guids WHERE value = @value

The same here:

DELETE value FROM guids WHERE @value=value ;

You must compare the column (value) to the parameter passed in (@value) and not the other way around! So change this to:

DELETE FROM guids WHERE value = @value

and you should be fine

Your stored proc is much too complicated.... you could simplify it to be:

ALTER PROCEDURE dbo.verifGuids
    @value char(36),
    @message char(1) OUTPUT
AS
BEGIN
    -- this will *already* delete only if the "value" column matches your "@value"
    -- parameter - there's really no need to first do a SELECT on that table to 
    -- verify that!
    DELETE FROM dbo.guids WHERE value = @value;

    IF @@ROWCOUNT > 0
       SET @message = 0  -- if you deleted anything - the set @message to "0"
    ELSE
       SET @message = 1

    RETURN @message
END

edited Jan 8, 2012 at 20:53

answered Jan 8, 2012 at 17:08

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FrankSharp Over a year ago

I change it to the column named value to valueGuid and it still object name 'valueGuid ' not valid..Net SqlClient Data Provider0 on execute cmd.ExecuteNonQuery() ...

Ravi Gadag · Accepted Answer · 2012-01-08 17:01:25Z

0

you have to use as Out Parameter for your return value. you can refer this site Parameter Directions, and also you check the MSDN Documentaion

answered Jan 8, 2012 at 17:01

Ravi Gadag

15.9k5 gold badges60 silver badges85 bronze badges

1 Comment

marc_s Over a year ago

The OP does have an OUTPUT parameter in his stored proc, and he also sets it up correctly (as far as I can see) in his VB.NET code....

Dan · Accepted Answer · 2012-01-08 20:05:49Z

0

You need to change your delete statement in the stored proc to:

DELETE FROM test WHERE code=@valueGuid

edited Jan 8, 2012 at 20:05

Dan

10.8k2 gold badges41 silver badges58 bronze badges

answered Jan 8, 2012 at 19:30

FrankSharp

2,63211 gold badges40 silver badges49 bronze badges

Comments

Marcus · Accepted Answer · 2012-01-08 22:06:20Z

0

Change your ParameterDirection.Output to ParameterDirection.ReturnValue

edited Jan 8, 2012 at 22:06

Marcus

12.6k5 gold badges50 silver badges66 bronze badges

answered Jan 8, 2012 at 20:31

Andrey Ashurko

1091 silver badge8 bronze badges

1 Comment

marc_s Over a year ago

That's not really going to change anything and certainly not going to solve the issue the OP is having..... and he does have an OUTPUT parameter which he can read out. This doesn't help at all, sorry.

Collectives™ on Stack Overflow

ASP.NET a SQL Server stored procedure with return

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related