Update database in asp.net not working

Question

i have in asp.net a few textboxes and i wish to update my database with the values that they encapsulate .

The problem is that it doesn't work and although it doesn't work, the syntax seems correct and there are no errors present . Here is my linkbutton :

<asp:linkbutton id="clickOnSave" runat="server" 
                onclick="Save_Click" Text="Save Profile" />

and my update function

protected void Save_Click(object sender, EventArgs e)
{
    SqlConnection con = new System.Data.SqlClient.SqlConnection();
    con.ConnectionString = "DataSource=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\alex\\Documents\\seeubook_db.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

    con.Open();

    String commandString = "UPDATE users SET last_name='" + Text4.Text.Trim() + "' , first_name='" + Textbox1.Text.Trim() + "' , about_me='" + Textbox5.Text.Trim() + "' , where_i_live='" +  Textbox2.Text.Trim() + "' ,  where_i_was_born='" + Textbox3.Text.Trim() + "' , work_place='" + Textbox4.Text.Trim() + "' WHERE email='" + Session["user"] + "'";

    SqlCommand sqlCmd = new SqlCommand(commandString, con);
    sqlCmd.ExecuteNonQuery();
    con.Close();
}

If you have free time I suggest looking into LINQ to SQL. It is a huge improvement over ADO.NET. — The Muffin Man
– The Muffin Man, Commented Dec 27, 2010 at 10:48
I'd strongly recommend validating the user input, or at least look into SQL Injection — RobS
– RobS, Commented Dec 27, 2010 at 11:02

marc_s · Accepted Answer · 2010-12-27 10:57:28Z

4

I'm always a bit weary about the User Instance=true in a connection string..... at times, it tends to create a new MDF file "on the fly" and when you update that MDF, then your changes might be just "gone" one your app has completed running.... See MSDN docs on User Instances.

I would suggest that you:

attach your MDF file to SQL Server Express on your machine, using SQL Server Express Management Studio
then use a server-based approach to your SQL Server Express database rather than attaching a file...

In that case, your database connection string would then look something like:

server=.\\SQLEXPRESS;database=YourDatabaseName;Integrated Security=SSPI;

And while you're at it, I would also recommend to:

wrap your SqlConnection and SqlCommand into using blocks to ensure proper disposal
open your connection as late as possible
use a parametrized query instead of concatenating together your SQL command - doing so is a wide open door for SQL injection attacks!

So your code would look something like this:

string connStr = "server=.\\SQLEXPRESS;database=YourDatabaseName;Integrated Security=SSPI;";

string cmdStmt = "UPDATE dbo.Users SET last_name = @lastName, " + 
   "first_name = @firstName, about_me = @aboutMe, where_i_live = @whereILive, " +  
   "where_i_was_born = @whereIWasBorn, work_place = @workPlace " +
   "WHERE email = @userEMail";

using(SqlConnection sqlCon = new SqlConnection(connStr))
using(SqlCommand sqlCmd = new SqlCommand(cmdStmt, sqlCon))
{
   // define parameters 
   sqlCmd.Parameters.Add("@lastName", SqlDbType.VarChar, 50);
   sqlCmd.Parameters["@lastName"].Value = Text4.Text.Trim();
   // and so on for all the parameters

   sqlCon.Open();
   sqlCmd.ExecuteNonQuery();
   sqlCon.Close();
}

answered Dec 27, 2010 at 10:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Over a year ago

it still doesn't work. The thing is that when i put the update code in the "page_load" function it works, but when i put it in my save function it doesn't work. I've verified that i enter the save function and that the .ExecuteNonQuery() returns 1 so i don't understand why it won't work

Alex Over a year ago

ok marc, i'll send you via email the project + mdf . Thank you a lot for the extra effort !

erinus · Accepted Answer · 2010-12-27 11:00:53Z

0

Debug! Look your LinkButton Click Event really go into Save_Click function. And then check 'sqlCmd.ExecuteNonQuery();' return result.

answered Dec 27, 2010 at 11:00

erinus

7344 silver badges5 bronze badges

1 Comment

Alex Over a year ago

it does go into the function and the function returns "1" . But the changes don't occur

Dilip Suvagiya · Accepted Answer · 2013-12-30 06:24:04Z

0

You need to write your code for filling Textbox's at page load as below :

public page_load()
{
   if(!ispostBack)
   {
     // Write code to fill controls first time
    }

}

answered Dec 30, 2013 at 6:24

Dilip Suvagiya

3941 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

Update database in asp.net not working

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related