Return Value Executenonquery

Question

I want to search the table to see if a record exists. I do not want to perform an insert or update after. I have already done this, but for some reason I cannot get this to work. On my asp.net page, I cannot get the return value. The error is "the input string is not in the correct format." I'm sure this is obvious, but I can’t see her now!

here is my code:

Dim con As New SqlConnection("connstring")
Dim cmd As New SqlCommand("checkname", con)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@d", SqlDbType.Int))
cmd.Parameters("@id").Value = TextBox1.Text

Dim para As New SqlParameter
para.Direction = ParameterDirection.ReturnValue
para.ParameterName = "returnvalue"
cmd.Parameters.Add(para)

con.Open()


cmd.ExecuteNonQuery()
Dim exists As Integer
exists = Convert.ToInt32(cmd.Parameters("returnvalue").Value)
If exists = 1 Then
    Label1.Text = "You......"
         ElseIf exists = 0 Then
    Label1.Text = "You....."

End If
con.Close()

stored procedure:

CREATE PROCEDURE checkname 
    -- Add the parameters for the stored procedure here
    @id int
AS
  --This means it exists, return it to ASP and tell us
 -- SELECT 'already exists'

IF EXISTS(SELECT * FROM attendees WHERE id = @id)
BEGIN
RETURN 1
END
ELSE
BEGIN
   RETURN 0
END

Answer 1

You need to make sure that you pass an integer.

int intValue;
if(!int.TryParse(TextBox1.Text, out intValue))
{
     // Update your page to indicate an error

     return;

}

cmd.Parameters.Add(New SqlParameter("id", SqlDbType.Int));
cmd.Parameters("id").Value = intValue; 

(Technically, you do not need the @ symbol when defining parameters in .NET. Code).

Answer 2

@d @id. . . , , 1 .

: , . , . .

Answer 3

ExecuteNonQuery . , , ExecuteNonQuery.

Answer 4

ExecuteNonQuery is used for Insert / Delete / Update operations. Not for SELECT, you need the ExecuteScalar or ExecuteReader methods. This link will help you learn how to use the output parameters: http://aspdotnet-suresh.blogspot.com/2010/10/introduction-here-i-will-explain-how-to.html