ASP.Net VB Page to update a users profile not updating.

[jturlington]

My page displays the info in the text boxes, but when we run the click sub for the update button it returns the info that it has been updated sucessfully. Here is the page code and below that is the stored procedure it uses, but not update in the database.

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="The Car Masseuse - Profile Update" %>
<%@Import Namespace="System.Web.Security" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim myConnection As New SqlConnection("server=localhost;uid=###;pwd=###;database=###;")
        Dim myCommand As New SqlDataAdapter("select * from users where username='" & Request.Cookies("username").Value & "'", myConnection)
        Dim ds As New DataSet()
        myCommand.Fill(ds, "users")
        lblID.Text = ds.Tables(0).Rows(0)("id").ToString()
        txtName.Text = ds.Tables(0).Rows(0)("name").ToString()
        txtAdd.Text = ds.Tables(0).Rows(0)("address").ToString()
        txtCity.Text = ds.Tables(0).Rows(0)("City").ToString()
        txtSt.Text = ds.Tables(0).Rows(0)("st").ToString()
        txtZIP.Text = ds.Tables(0).Rows(0)("zip").ToString()
        txtPhone.Text = ds.Tables(0).Rows(0)("phone").ToString()
        txtEmail.Text = ds.Tables(0).Rows(0)("email").ToString()
    End Sub
    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 SqlCommand
        sqlSel = New SqlCommand
        sqlSel.Connection = sqlConn
        sqlSel.CommandText = "userUpdate"
        sqlSel.CommandType = Data.CommandType.StoredProcedure
        sqlSel.Parameters.AddWithValue("@id", lblID.Text)
        sqlSel.Parameters.AddWithValue("@name", txtName.Text)
        sqlSel.Parameters.AddWithValue("@address", txtAdd.Text)
        sqlSel.Parameters.AddWithValue("@city", txtCity.Text)
        sqlSel.Parameters.AddWithValue("@st", txtSt.Text)
        sqlSel.Parameters.AddWithValue("@zip", txtZIP.Text)
        sqlSel.Parameters.AddWithValue("@phone", txtPhone.Text)
        sqlSel.Parameters.AddWithValue("@email", txtEmail.Text)
        Dim selParm As SqlParameter = sqlSel.Parameters.Add("ReturnValue", SqlDbType.Int, 1)
        selParm.Direction = ParameterDirection.ReturnValue
        sqlConn.Open()
        sqlSel.ExecuteNonQuery()
        If selParm.Value = 0 Then
            lblstatus.Text = "Update Sucessful!"
            Panel1.Visible = False
        End If
        sqlConn.Close()
    End Sub
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table align="center" width="545px"><tr><td align="right" class="texttitle1">Update Profile</td></tr><tr><td><hr color="#F53167" /></td></tr>
<tr><td>
    <asp:Label ID="lblstatus" runat="server">Here you can update your contact information.</asp:Label>
<asp:Panel ID="Panel1" runat="server">

    <asp:Label runat="server" ID="lblUserId">User ID #:</asp:Label><asp:Label runat="server" ID="lblID"></asp:Label><br />
    <asp:Label runat="server" ID="lblName">Name: </asp:Label><asp:TextBox ID="txtName" runat="server" cssclass="text"></asp:TextBox><br />
    <asp:Label runat="server" ID="lblAddress">Address: </asp:Label><asp:TextBox ID="txtAdd" runat="server" cssclass="text"></asp:TextBox><br />
    <asp:Label runat="server" ID="lblCity">City: </asp:Label><asp:TextBox ID="txtCity" runat="server" cssclass="text"></asp:TextBox><br />
    <asp:Label runat="server" ID="lblState">State: </asp:Label><asp:TextBox ID="txtSt" runat="server" cssclass="text"></asp:TextBox><br />
    <asp:Label runat="server" ID="lblZip">Zip Code: </asp:Label><asp:TextBox ID="txtZIP" runat="server" cssclass="text"></asp:TextBox><br />
    <asp:Label runat="server" ID="lblPhone">Phone Number: </asp:Label><asp:TextBox ID="txtPhone" runat="server" cssclass="text"></asp:TextBox><br />
    <asp:Label runat="server" ID="lblEmail">Email: </asp:Label><asp:TextBox ID="txtEmail" runat="server" cssclass="text"></asp:TextBox><br />
    <asp:Button ID="butUpdate" runat="server" Text="Update Profile" OnClick="butUpdate_Click" /></asp:Panel></td></tr></table>
</asp:Content>

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 (0)
GO

Any help would be greatly appreciated.

Thanks

[Paladine]

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:

[AusClansViper]

Guess what.. I HAD the same problem..

after about 3-4 hours searching the net for a solution i gave up..

I even notice you posted on another site..

To correct the guy above AddWithValue Exists in ASP.NET v2

TO FIX the problem.. add a if statement
if(!IsPostBack)
{
// Bind Data Here..
}

OnClick Event Handler
{
// UPDATE SP Routine
Bind Data..
}

If you have a problem drop me an email [e-mail]
any emails are welcome..

[Kate Albany]

Hello,

I use Java & Oracle, not .NET & SQL Server. Nevertheless, unless I am missing something about .NET, you need a 'commit' after the update and before you close the connection. Otherwise after the connection is colsed all your updates will be rolled back by the database by default.

Kate