Help - Update Field in SQL Server

I am trying to write a password change page for my website. I want logged in users to be able to change their password which is stored in a SQL table. I was able to get the following code to work

Dim myCommand As New SqlCommand( _
"cmdChangePassword @.Email='"+request("email")+"', @.OldPassword='"+TextBox1.text+"',@.NewPassword='"+TextBox3.text+"'", myConnection)

This information is passed to a stored procedure which updates the table. However, I want to have a return value that shows that the password was changed. I changed the code as follows:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure

myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50).Value = request("email")

Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = "+TextBox1.text+"

Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = "+TextBox3.text+"

With this new code the password is not being changed. However, I am not receiving any errors.

BDyou can check your db if the pwd is being changed, to see if the sp is working properly.

(1) in your SP, you can return an integer 0/1 for success or failure and appropriately throw a msgbox saying pswd has been changed.
and modify your code slightly and have an output parameter.

or
(2) query the db again with username=@.username and pwd=@.newpswd and see if you get any records. to make sure you get the xact record you can add more conditions. so if you do get a record, then the db has been updated with the new pswd.

apparently, this one requires an xtra trip to the db.

HTH|||The SP is set to return the integer. I haven't added that code yet. I have tested the SP and know that it works. However, the password is not being changed. The email address, old password and new password need to be passed to the SP. As long as the email address and old password match, the password will be changed. I am assuming there is something wrong with my code and that those items are not being passed successfully.|||heres an example of the code that i am using

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim myParameter As SqlParameter
Dim myDataReader As SqlDataReader
myConnection = New SqlConnection("server=local;database=Northwind;Integrated Security=SSPI ")
myCommand = New SqlCommand()
myCommand.Connection = myConnection
myCommand.CommandText = "finalize_movein"
myCommand.CommandType = CommandType.StoredProcedure
'input parameter
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.userid"
myParameter.Direction = ParameterDirection.Input
myParameter.SqlDbType = SqlDbType.int
myParameter.Value = id
myCommand.Parameters.Add(myParameter)
'output parameter:
myParameter = myCommand.CreateParameter()
myParameter.ParameterName = "@.finalcnum"
myParameter.Direction = ParameterDirection.Output
myParameter.SqlDbType = SqlDbType.int
myCommand.Parameters.Add(myParameter)
' Open the connection to the SQL Server
myConnection.Open()
myCommand.ExecuteNonQuery()
return convert.toint32((myCommand.Parameters("@.finalcnum").Value))
myconnection.close

and have your SP defined like this :

CREATE PROCEDURE finalize_movein (@.userid int ,@.finalcnum bigint OUTPUT) as
begin...

HTH|||BD - please wrap your code in CODE tags to make it distinguishable from your comments.

It looks to me that your problem is in the way you are setting your parameter values. For some reason you have surrounded them with double quotes(") and plus signs(+). Remove those and you should have better luck. I changed the way you were setting up your @.email parameter to make it more consistent with the rest of your code, plus I added a ReturnValue parameter:

Dim myCommand As SqlCommand = New SqlCommand("cmdChangePassword", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim myParm0 As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Integer)
myParm0.Direction = ParameterDirection.ReturnValue
Dim myParm1 As SqlParameter = myCommand.Parameters.Add("@.email", SqlDbType.VarChar, 50)
myParm1.Direction = ParameterDirection.Input
myParm1.Value = request("email")
Dim myParm2 As SqlParameter = myCommand.Parameters.Add("@.OldPassword", SqlDbType.VarChar, 20)
myParm2.Direction = ParameterDirection.Input
myParm2.Value = TextBox1.text
Dim myParm3 As SqlParameter = myCommand.Parameters.Add("@.NewPassword", SqlDbType.VarChar, 20)
myParm3.Direction = ParameterDirection.Input
myParm3.Value = TextBox3.text

|||That worked! Thanks for your help!

Brian|||Sorry, I have one more question. I am now able to change the password. However, I want to display a message to the user that the password has been changed. I have the following variable defined.

Code

Dim Result As SqlParameter = myCommand.Parameters.Add("@.ReturnValue", SqlDbType.Int)

Result.Direction = ParameterDirection.ReturnValue

myCommand.Connection.Open()
myCommand.ExecuteNonQuery()

if Result.Value>0 then MSG.text= "Your password has been changed"

myCommand.Connection.Close()

End Code

In my html I have the following:

<asp:Label id="MSG" runat="server"></asp:Label
Any ideas what I am missing?

Brian|||Normally, the ReturnValue will be 0 if there are no errors. I don't know what your stored procedure looks like, but assuming it follows normal practice, this line:

if Result.Value>0 then MSG.text= "Your password has been changed"

should be this:

if myCommand.Parameters("@.ReturnValue").Value=0 then MSG.text= "Your password has been changed"

Terri|||That's perfect. Thanks again for everybody's help.

Brian