Update SP - to show users logged in.

[Newbie81]

Hello All,
I have created a column called status, to show whether a user is logged in or not. I have created a stored procedure as follows:

CREATE Procedure SP_UpdateStatus(
@Status bit, @Email varchar(50)
)
As Begin
Update Schoolmates
Set @Status = status

Where Email = @Email
End

Once I test it it does not run. I dont know why. I am a complete beginer, which is why I am seeking help from you experts. The code is as follows:

SqlConnection1.Open()
Dim cmd As SqlCommand = SqlConnection1.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT Forename, Surname, Password,ID " & _
"FROM Schoolmates " & _
"WHERE Email ='" & UCase(Trim(txtEmail.Text)) & "'" & _
"And Password ='" & UCase(Trim(txtPassword.Text)) & "'"
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Session.Clear()
If dr.HasRows() = True Then
Application("ccDatabase") = True
Session.Add("isUserlogedin", True)
dr.Read()
'Session("Forename") = dr.GetString(0)
Session("ID") = dr.GetInt32(3)
Response.Redirect("ccDatabase.aspx")
status = 1
Dim cmdDelete As SqlCommand = SqlConnection1.CreateCommand
cmdDelete.CommandType = CommandType.StoredProcedure
cmdDelete.CommandText = "SP_UpdateStatus"
cmdDelete.Parameters.Add("@email", "@status")
cmdDelete.ExecuteNonQuery()
Else
Session.Add("isUserlogedin", False)
lblMessage.Text = "Invalid Login"
txtPassword.Text = ""

Any one have any suggestions.....I would be grateful. I thank you experts in advance.

 

[Berry-tan]

It probably runs, but most definitely will not do anything to your database.

The correct syntax is:

CREATE PROCEDURE SP_UpdateStaus
(
    @Status BIT,
    @Email VARCHAR(50)
)
AS
    UPDATE Schoolmates
        SET Status=@Status
        WHERE Email=@Email;
RETURN

 

[TechGnome]

The SP syntax is just fine.....

It's your parameters you've screwed up...

cmdDelete.Parameters.Add("@email", "@status")

What you managed to do was to add a parameter called "@Email" ... which is right, but passed in the value "@status" and the parameter value..... clearly not what you intended.

Plus they are in the wrong order.... they need to be (or should be) in the same order as they are in the SP itself.

cmdDelete.Parameters.Add("@status", 1)
cmdDelete.Parameters.Add("@email", UCase(Trim(txtEmail.Text)))

Lastly... you may want to consider changing the name of that command.... it doesn't delete.... so you may want to give it a more meaningful name...

-tg