Update using Stored Procedure (ASP.NET with C#)

  • (2 Pages)
  • +
  • 1
  • 2

28 Replies - 31959 Views - Last Post: 11 February 2009 - 12:31 PM Rate Topic: -----

#1 blakpower   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 26
  • Joined: 09-February 09

Update using Stored Procedure (ASP.NET with C#)

Post icon  Posted 09 February 2009 - 03:34 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Update using Stored Procedure (ASP.NET with C#)

#2 General Adamus   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 110
  • Joined: 05-February 09

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 09 February 2009 - 03:56 PM

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
Was This Post Helpful? 0
  • +
  • -

#3 AshishKumar_Gupta   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 28
  • Joined: 19-January 09

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 09 February 2009 - 08:28 PM

Hi

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

Thanks
Was This Post Helpful? 0
  • +
  • -

#4 Jayman   User is offline

  • Student of Life
  • member icon

Reputation: 423
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 09 February 2009 - 09:03 PM

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;


Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 09 February 2009 - 11:02 PM

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);


Was This Post Helpful? 0
  • +
  • -

#6 General Adamus   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 110
  • Joined: 05-February 09

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 05:40 AM

View PostPsychoCoder, 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
Was This Post Helpful? 0
  • +
  • -

#7 blakpower   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 26
  • Joined: 09-February 09

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 07:41 AM

Hi

i have tried all those ways and the result stays the same
Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1535
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 08:21 AM

View Postblakpower, 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.
Was This Post Helpful? 0
  • +
  • -

#9 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 08:24 AM

View PostGeneral 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
Was This Post Helpful? 0
  • +
  • -

#10 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7380
  • View blog
  • Posts: 15,311
  • Joined: 16-October 07

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 09:43 AM

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

Was This Post Helpful? 1
  • +
  • -

#11 General Adamus   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 110
  • Joined: 05-February 09

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 09:44 AM

View PostPsychoCoder, on 10 Feb, 2009 - 07:24 AM, said:

View PostGeneral 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

Was This Post Helpful? 0
  • +
  • -

#12 blakpower   User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 26
  • Joined: 09-February 09

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 02:34 PM

View Posteclipsed4utoo, on 10 Feb, 2009 - 07:21 AM, said:

View Postblakpower, 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");
		}
	}
}


Was This Post Helpful? 1

#13 General Adamus   User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 110
  • Joined: 05-February 09

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 02:38 PM

View Postblakpower, on 10 Feb, 2009 - 01:34 PM, said:

View Posteclipsed4utoo, on 10 Feb, 2009 - 07:21 AM, said:

View Postblakpower, 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
Was This Post Helpful? 1
  • +
  • -

#14 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1535
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 02:48 PM

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

Was This Post Helpful? 1
  • +
  • -

#15 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Update using Stored Procedure (ASP.NET with C#)

Posted 10 February 2009 - 04:28 PM

View PostGeneral 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
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2