MS SQL

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

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jun 2005

Posts: 4

Reputation: jturlington is an unknown quantity at this point

Solved Threads: 0

jturlington jturlington is offline

Offline

Newbie Poster

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

Jul 7th, 2005

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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
<%@ 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>
<%@ 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>

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Feb 2003

Posts: 793

Reputation: Paladine has a spectacular aura about

Solved Threads: 26

Paladine

Offline

Master Poster

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

Jul 8th, 2005

Ok first off, have you tested your Stored Procedure?

Here is what it should look like :

Help with Code Tags

(Toggle Plain Text)

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:

Help with Code Tags

(Toggle Plain Text)

 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.

Assistant Manager, Pharmacy Informatics
Wordpress Learning Blog
Updated : ASP.Net Login Code

•

Join Date: May 2006

Posts: 1

Reputation: AusClansViper is an unknown quantity at this point

Solved Threads: 0

AusClansViper AusClansViper is offline

Offline

Newbie Poster

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

May 28th, 2006

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..

•

Join Date: Jun 2005

Posts: 71

Reputation: Kate Albany is an unknown quantity at this point

Solved Threads: 1

Kate Albany Kate Albany is offline

Offline

Junior Poster in Training

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

May 29th, 2006

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