Ok first off, have you tested your Stored Procedure?
Here is what it should look like :
CREATE PROCEDURE userUpdate
(
@id bigint,
@name varchar(50),
@address varchar(50),
@city varchar(50),
@st varchar(2),
@zip varchar(5),
@phone varchar(10),
@email varchar(75)
)
AS
UPDATE users
SET name=@name,address=@address,city=@city,st=@st,zip=@zip,phone=@phone,email=@email
WHERE id = @id
Return
And with your ASP.Net Codebehind I found some issues:1. No Input direction set for your parameters going into the stored procedure.
2. No parameter created to store those values in the parameters collection.
3. AddWithValue is not a member of the parameters collection
4. You are requesting a return value called ReturnValue which you haven't declared in your Stored Procedure.
Here is what it should be like:
Protected Sub butUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sqlConn As SqlConnection = New SqlConnection("server=localhost;uid=###;pwd=###;database=###;")
Dim sqlSel As New SqlCommand
sqlSel.Connection = sqlConn
sqlSel.CommandText = "userUpdate"
sqlSel.CommandType = Data.CommandType.StoredProcedure
Dim objParam1, objParam2, objParam3, objParam4, objParam5, objParam6, objParam7, objParam8 As SqlParameter
objParam1 = sqlSel.Parameters.Add("@id", lblID.Text)
objParam2 = sqlSel.Parameters.Add("@name", txtName.Text)
objParam3 = sqlSel.Parameters.Add("@address", txtAdd.Text)
objParam4 = sqlSel.Parameters.Add("@city", txtCity.Text)
objParam5 = sqlSel.Parameters.Add("@st", txtSt.Text)
objParam6 = sqlSel.Parameters.Add("@zip", txtZIP.Text)
objParam7 = sqlSel.Parameters.Add("@phone", txtPhone.Text)
objParam8 = sqlSel.Parameters.Add("@email", txtEmail.Text)
objParam1.Direction = ParameterDirection.Input
objParam2.Direction = ParameterDirection.Input
objParam3.Direction = ParameterDirection.Input
objParam4.Direction = ParameterDirection.Input
objParam5.Direction = ParameterDirection.Input
objParam6.Direction = ParameterDirection.Input
objParam7.Direction = ParameterDirection.Input
objParam8.Direction = ParameterDirection.Input
'Dim selParm As SqlParameter = sqlSel.Parameters.Add("ReturnValue", SqlDbType.Int, 1)
'selParm.Direction = ParameterDirection.ReturnValue
Dim intNumAff As Integer
sqlConn.Open()
intNumAff = sqlSel.ExecuteNonQuery()
If intNumAff > 0 Then
lblstatus.Text = "Update Sucessful!"
Panel1.Visible = False
Else
lblstatus.Text = "Update Failed!"
End If
sqlConn.Close()
Again I can't stress this enough in the world of programming / information technology. There are standards set, and they should be followed.Standards like prefixing stored procedures with sp_ i.e. sp_updateUser<
Naming conventions in code : int , obj , str to prefix an INTEGER, and OBJECT, and a STRING as examples. <
Hope this helps.
:cool:
