CODEWITHSUNDEEP
asp.netsql-server
Alex
Alex

Reputation: 11137

Update database in asp.net not working

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

Upvotes: 0

Views: 2503

Answers (3)

Dilip Suvagiya
Dilip Suvagiya

Reputation: 394

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
    }

}

Upvotes: 0

erinus
erinus

Reputation: 734

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

Upvotes: 0

marc_s
marc_s

Reputation: 754200

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

Upvotes: 4

Related Questions