[blakpower]

Hi

I have a s.p to update administrators on my site. on the sql side the update works, when i hard code the values in it works but when i pass values that are in the textbox it does not update

here is my code

S.P

1	CREATE PROCEDURE [dbo].[spUpdateAdministrator]
2				@email varchar(50),
3				@password varchar (16),
4				@firstName varchar (30),
5				@lastName varchar (30)
6	
7	AS
8	BEGIN
9		  -- SET NOCOUNT ON added to prevent extra result sets from
10		 -- interfering with SELECT statements.
11		 SET NOCOUNT ON;
12   UPDATE [fantasyfootball].[dbo].[Admin]
13	  SET [E-mail] = @email
14		 ,[Password] = @password
15		   ,[First_Name] = @firstName
16		 ,[Last_Name] = @lastName
17   WHERE [E-mail] = @email

Function calling stored procedure

1	public void UpdateAdministrator(String email, String pword,String fname, String lname)
2		{
3			SqlCommand updateAdministrator = new SqlCommand("spUpdateAdministrator");
4			string connString = ConfigurationManager.ConnectionStrings["fantasyfootballConnectionString"].ConnectionString;
5			SqlConnection conn = new SqlConnection(connString);
6			updateAdministrator.Connection = conn;
7			updateAdministrator.CommandType = CommandType.StoredProcedure;
8	
9			using (conn)
10		   {
11			   //Adding the required parameters
12			   updateAdministrator.Parameters.Add("@email", SqlDbType.VarChar);
13			   updateAdministrator.Parameters.Add("@password", SqlDbType.VarChar);
14			   updateAdministrator.Parameters.Add("@firstName", SqlDbType.VarChar);
15			   updateAdministrator.Parameters.Add("@lastName", SqlDbType.VarChar);
16  
17			   //Setting the values for each parameter
18			   updateAdministrator.Parameters["@email"].Value = (string)_Email;
19			   updateAdministrator.Parameters["@password"].Value = (string)_Password;
20			   updateAdministrator.Parameters["@firstName"].Value = (string)_FirstName;
21			   updateAdministrator.Parameters["@lastName"].Value = (string)_LastName;
22  
23			   updateAdministrator.Connection.Open();
24			   updateAdministrator.ExecuteNonQuery();
25			   updateAdministrator.Connection.Close();
26		   }
27	   }
28  
29	

code behind the update button

1	protected void btnUpdate_click(object sender, ImageClickEventArgs e)
2		{
3			if (Page.IsValid)
4			{
5				Administrator Administrator = new Administrator(txtEMail.Text, txtPWord.Text, txtFName.Text, txtLName.Text);
6				Administrator.UpdateAdministrator(txtEMail.Text, txtPWord.Text, txtFName.Text, txtLName.Text);
7				Response.Redirect("adminhompageadmin.aspx");
8			}
9		}

Any help would be useful

[General Adamus]

It is because your parameters are not defined correctly.

Instead of trying to use the Add() overloads, use a standard SQLParameter object.

i.e.

Dim p1 as new sqlparameter( <--Check intellisense for parameters
Dim p2 as new sqlparameter

updateAdministrator.Parameters.Add(p1)
updateAdministrator.Parameters.Add(p2)


Adamus

[AshishKumar_Gupta]

Hi

SqlParameter p0 = new SqlParameter("@parameter", SqlDbType.datatype);
p0.Value = youvalue for parameter;

Thanks

[Jayman]

Modify the following statements, as shown, to use the values being passed into the UpdateAdministratorm method.

 //Setting the values for each parameter
updateAdministrator.Parameters["@email"].Value = email;
updateAdministrator.Parameters["@password"].Value = pword;
updateAdministrator.Parameters["@firstName"].Value = fname;
updateAdministrator.Parameters["@lastName"].Value = lname;

[PsychoCoder]

It's actually simpler if you use the AddWithValue Method with your SQL parameters

//Adding the required parameters
updateAdministrator.Parameters.AddWithValue("@email", email);
updateAdministrator.Parameters.AddWithValue("@password", pword);
updateAdministrator.Parameters.AddWithValue("@firstName", fname);
updateAdministrator.Parameters.AddWithValue("@lastName", lname);

[General Adamus]

PsychoCoder, on 9 Feb, 2009 - 10:02 PM, said:

It's actually simpler if you use the AddWithValue Method with your SQL parameters

//Adding the required parameters
updateAdministrator.Parameters.AddWithValue("@email", email);
updateAdministrator.Parameters.AddWithValue("@password", pword);
updateAdministrator.Parameters.AddWithValue("@firstName", fname);
updateAdministrator.Parameters.AddWithValue("@lastName", lname);

You should avoid AddWithValue because it doesn't allow you to set a dbtype or length. You want to at least set these options to avoid overflow exceptions or datatype conversion errors.

Adamus

[blakpower]

Hi

i have tried all those ways and the result stays the same

[eclipsed4utoo]

blakpower, on 10 Feb, 2009 - 09:41 AM, said:

Hi

i have tried all those ways and the result stays the same

then you are not posting everything. I use the exact same code to do stored procedure calls and it works just fine.

[PsychoCoder]

General Adamus, on 10 Feb, 2009 - 03:40 AM, said:

You should avoid AddWithValue because it doesn't allow you to set a dbtype or length. You want to at least set these options to avoid overflow exceptions or datatype conversion errors.

Actually if you do some research AddWithValue is the preferred way of adding SQL parameters. In 2.0 AddWithValue replaces the Add method

[baavgai]

Mostly looks fine. However, I don't understand where you pass "string pword" and then load with value "(string)_Password". Where the does that value come from? Looks like it's probably your error.

Defining the parameter type and size is always preferable to AddWithValue or the depreciated Add where you essentially do the same thing. If you Add with just a value, the connection must do more work to determine the database type. If you're explicit, you avoid that work. Also, if you define your command with a fully qualified parameter list, then you can cache the command and call it many times without the overhead of regeneration.

Here's some code that may work for you:

// always have a single point of reference for you connection
// makes it easier to change later
private SqlConnection GetConnection() {
	string connString = ConfigurationManager.ConnectionStrings["fantasyfootballConnectionString"].ConnectionString;
	return new SqlConnection(connString);
}

// why have this separate?
// You could save it's creation and speed up your code if you have to call it a lot
private void GetCmdUpdateAdministrator() {
	SqlCommand cmd = GetConnection().CreateCommand(); // note, now the connection is already included
	cmd.CommandText = "[dbo].[spUpdateAdministrator]"; // sometimes MSSQL likes full name
	cmd.CommandType = CommandType.StoredProcedure;
	
	cmd.Parameters.Add("@email", SqlDbType.VarChar, 50);
	cmd.Parameters.Add("@password", SqlDbType.VarChar, 16);
	cmd.Parameters.Add("@firstName", SqlDbType.VarChar, 30);
	cmd.Parameters.Add("@lastName", SqlDbType.VarChar, 30);
}


public void UpdateAdministrator(string email, string pword, string fname, string lname) {
	SqlCommand cmd = GetCmdUpdateAdministrator();
	// What is this: (string)_Email. you never defined it before
	
	cmd.Parameters["@email"].Value = email;
	cmd.Parameters["@password"].Value = pword;
	cmd.Parameters["@firstName"].Value = fname;
	cmd.Parameters["@lastName"].Value = lname;

	try {
		cmd.Connection.Open();
		cmd.ExecuteNonQuery();
	} finally {
		cmd.Connection.Close();
	}
}

Hope this helps.

edit: wonky post dupe

This post has been edited by baavgai: 10 February 2009 - 09:45 AM

[General Adamus]

PsychoCoder, on 10 Feb, 2009 - 07:24 AM, said:

General Adamus, on 10 Feb, 2009 - 03:40 AM, said:

You should avoid AddWithValue because it doesn't allow you to set a dbtype or length. You want to at least set these options to avoid overflow exceptions or datatype conversion errors.

Actually if you do some research AddWithValue is the preferred way of adding SQL parameters. In 2.0 AddWithValue replaces the Add method

AddWithValue was designed for parameterized inline text queries which should be avoided like the plague.

i.e. myQuery = "INSERT INTO Test (fName) Values(@fName)"

cmd.Parameters.AddWithValue("@fName", txtFname.Text)

This new feature was to benefit the front end developers who have already completed the datatype and length validation in the form.

It should NOT be used in middle-tier development.

Adamus

This post has been edited by General Adamus: 10 February 2009 - 09:46 AM

[blakpower]

eclipsed4utoo, on 10 Feb, 2009 - 07:21 AM, said:

blakpower, on 10 Feb, 2009 - 09:41 AM, said:

Hi

i have tried all those ways and the result stays the same

then you are not posting everything. I use the exact same code to do stored procedure calls and it works just fine.

I passed the values into the textbox from a my db on the page load. i think because the page is not refreshed. the changes i made are not picked up

here is all the code from the page behind

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Sql;
using System.Data.SqlClient;

public partial class editadmin : System.Web.UI.Page
{
	Administrator Admin;
	protected void Page_Load(object sender, EventArgs e)
	{
		Admin = new Administrator();
	   
		String selectedAdministrator = (string)Session["email"]; 
		Admin.ListAdministrator(selectedAdministrator);
		txtEMail.Text = (string)Session["email"];
		txtPWord.Text = Admin.Password; 
		txtFName.Text = Admin.FirstName;
		txtLName.Text = Admin.LastName;

	}

	protected void btnDelete_click(object sender, ImageClickEventArgs e)
	{
		Admin.DeleteAdministrator(txtEMail.Text);
	}
	protected void btnCancel_click(object sender, ImageClickEventArgs e)
	{

	}
	protected void btnUpdate_click(object sender, ImageClickEventArgs e)
	{ 
		if (Page.IsValid)
		{
   

			Admin.UpdateAdministrator(txtEMail.Text, txtPWord.Text,txtFName.Text, txtLName.Text);
			Response.Redirect("confirmeditadmin.aspx");
		}
	}
}

[General Adamus]

blakpower, on 10 Feb, 2009 - 01:34 PM, said:

eclipsed4utoo, on 10 Feb, 2009 - 07:21 AM, said:

blakpower, on 10 Feb, 2009 - 09:41 AM, said:

Hi

i have tried all those ways and the result stays the same

then you are not posting everything. I use the exact same code to do stored procedure calls and it works just fine.

I passed the values into the textbox from a my db on the page load. i think because the page is not refreshed. the changes i made are not picked up

here is all the code from the page behind

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Sql;
using System.Data.SqlClient;

public partial class editadmin : System.Web.UI.Page
{
	Administrator Admin;
	protected void Page_Load(object sender, EventArgs e)
	{
		Admin = new Administrator();
	   
		String selectedAdministrator = (string)Session["email"]; 
		Admin.ListAdministrator(selectedAdministrator);
		txtEMail.Text = (string)Session["email"];
		txtPWord.Text = Admin.Password; 
		txtFName.Text = Admin.FirstName;
		txtLName.Text = Admin.LastName;

	}

	protected void btnDelete_click(object sender, ImageClickEventArgs e)
	{
		Admin.DeleteAdministrator(txtEMail.Text);
	}
	protected void btnCancel_click(object sender, ImageClickEventArgs e)
	{

	}
	protected void btnUpdate_click(object sender, ImageClickEventArgs e)
	{ 
		if (Page.IsValid)
		{
   

			Admin.UpdateAdministrator(txtEMail.Text, txtPWord.Text,txtFName.Text, txtLName.Text);
			Response.Redirect("confirmeditadmin.aspx");
		}
	}
}

Wrap your page_load code in...

If not page.ispostback
'Your code here
End if

Adamus

[eclipsed4utoo]

ok...I see the problem.

It's seems that you don't fully understand how ASP.Net works and how it handles postbacks. Setting a breakpoint and stepping through the code would have probably helped you out.

So, when you click the Update button, here are the steps that are happening:

1. the "Admin" object is set to null.
2. the Page_Load method is executed.
3. the btnUpdate_Click method is executed.

1. Because you have "Administrator Admin;" outside of the methods, this means that it will get set to null on each page refresh.

2. In your page load, you are not specifying to only do that code when the page is first loaded and not on each postback. Since it is running the code on each postback, it is setting the data in the textboxes to the data from the object...effectively overwriting any changes you typed in.

3. Since you have now overwritten any changes, your Update method doesn't look like it's doing anything because you are updating with the same values that are already there.

So to fix:


Page_Load

protected void Page_Load(object sender, EventArgs e)
	{
		if(!(Page.IsPostback))
	   {
			  Administrator Admin = new Administrator();
	   
			  String selectedAdministrator = (string)Session["email"]; 
			  Admin.ListAdministrator(selectedAdministrator);
			  txtEMail.Text = (string)Session["email"];
			  txtPWord.Text = Admin.Password; 
			  txtFName.Text = Admin.FirstName;
			  txtLName.Text = Admin.LastName;

			  Admin = null;
		}
	}

btnUpdate_Click

	protected void btnUpdate_click(object sender, ImageClickEventArgs e)
	{ 
		if (Page.IsValid)
		{
			Administrator Admin = new Administrator();
			Admin.UpdateAdministrator(txtEMail.Text, txtPWord.Text,txtFName.Text, txtLName.Text);
			Response.Redirect("confirmeditadmin.aspx");

			Admin = null;
		}
	}

This post has been edited by eclipsed4utoo: 10 February 2009 - 02:49 PM

[PsychoCoder]

General Adamus, on 10 Feb, 2009 - 07:44 AM, said:

AddWithValue was designed for parameterized inline text queries which should be avoided like the plague.

i.e. myQuery = "INSERT INTO Test (fName) Values(@fName)"

cmd.Parameters.AddWithValue("@fName", txtFname.Text)

This new feature was to benefit the front end developers who have already completed the datatype and length validation in the form.

It should NOT be used in middle-tier development.

Adamus

Well I guess we'll have to agree to disagree When I was studying (and eventually took) the MCAD and MCSD Exams it was stated that AddWithValue was the better approach to use with SQL Parameters. With ADO.NET 2.0 there is an implicit conversion that is handled for the developer. I did some testing with 2.0 and did a test, when I used Add I got the following warning

Quote

Warning 1 'Public Function Add(parameterName As String, value As Object) As System.Data.SqlClient.SqlParameter' is obsolete: 'Add(String parameterName, Object value) has been deprecated. Use AddWithValue(String parameterName, Object value).

Even Michael Wheat (A Microsoft MVP) advocates using AddWithValue as well