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

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

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

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:

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

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 ckolenko@ausclans.com
any emails are welcome..

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

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