6 Replies - 7466 Views - Last Post: 18 January 2013 - 06:50 AM Rate Topic: -----

#1 aortell24  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 07-December 12

SQL Update query not updating record

Posted 17 January 2013 - 01:42 PM

Hello I was not sure wither to put this in C# or Asp.net. I am having a problem with a SQL update query. It is not updating the record and not throwing any error or exception. I have a INSERT and DELETE query on the same table that
both run successfully. I am very new to SQL so any help would be greatly appreciated. I am not sure of all the code I need
to post so if any more is needed please let me know. Thanks
 public partial class DeletePage : System.Web.UI.Page
    {
        string strTxt1;
        string strTxt2;
        string strTxt3;
        string strTxt4;
        string index;

        protected void Page_Load(object sender, EventArgs e)
        {
            string strValue = Request.QueryString["row"];
            string[] values = strValue.Split('|');
            strTxt1 = values[0];
            strTxt2 = values[1];
            strTxt3 = values[2];
            strTxt4 = values[3];
            index = values[4];
            txtISBN.Text = strTxt1;
            txtTitle.Text = strTxt2;
            txtEdition.Text = strTxt3;
            txtCopyright.Text = strTxt4;
        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {
            Response.Redirect("~/default.aspx");
        }

        protected  void btnConfirmDelete_Click(object sender, EventArgs e)
        {
            SqlDataSource1.DeleteCommand = string.Format("DELETE FROM Titles WHERE ISBN='{0}'", txtISBN.Text);
            SqlDataSource1.Delete();
            Response.Redirect("/default.aspx");
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            SqlDataSource1.UpdateCommand = string.Format("UPDATE Titles SET Title='{0}', EditionNumber='{1}', Copyright='{2}' WHERE ISBN='{3}'",
                txtTitle.Text, txtEdition.Text, txtCopyright.Text, txtISBN.Text);
            SqlDataSource1.Update();
            Response.Redirect("/default.aspx");
        }
    }

public partial class NewBook : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {
            Response.Redirect("~/Default.aspx");
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            SqlDataSource1.InsertCommand = string.Format("INSERT INTO Titles (ISBN, Title, EditionNumber, Copyright)  VALUES ('{0}', '{1}', '{2}', '{3}')",
                txtISBN.Text, txtTitle.Text, txtEdition.Text, txtCopyright.Text);
            SqlDataSource1.Insert();
            Response.Redirect("/Default.aspx");
        }
    }



Is This A Good Question/Topic? 0
  • +

Replies To: SQL Update query not updating record

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9569
  • View blog
  • Posts: 36,229
  • Joined: 12-June 08

Re: SQL Update query not updating record

Posted 17 January 2013 - 01:45 PM

Moving to ASP.NET.
Was This Post Helpful? 0
  • +
  • -

#3 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1182
  • View blog
  • Posts: 1,677
  • Joined: 24-August 11

Re: SQL Update query not updating record

Posted 17 January 2013 - 02:58 PM

First, I would recommend looking into using parameters, rather than string.format, to insert variables into your SQL statements. For example:

SqlDataSource1.UpdateCommand = 
    @"UPDATE Titles 
    SET Title=@Title, EditionNumber=@EditionNumber, Copyright=@Copyright 
    WHERE ISBN=@ISBN";
SqlDataSource1.UpdateParameters.Add("@Title", txtTitle.Text);
SqlDataSource1.UpdateParameters.Add("@EditionNumber", txtEdition.Text);
SqlDataSource1.UpdateParameters.Add("@Copyright", txtCopyright.Text);
SqlDataSource1.UpdateParameters.Add("@ISBN", txtISBN.Text);



If you look at tlhIn`toq's tutorial links, there's a link to a tutorial about using SQL Parameters and why you should do it.

Second, what does the Update() command return? It returns the number of rows affected, so I'm curious if it returns a '0' or not.
Was This Post Helpful? 0
  • +
  • -

#4 aortell24  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 07-December 12

Re: SQL Update query not updating record

Posted 17 January 2013 - 03:12 PM

Thanks for the reply update returns 1. I did have it without string.format and it did the same thing
so I just used format to clean it up a little.

I will look at that tut thanks alot.
Was This Post Helpful? 0
  • +
  • -

#5 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1182
  • View blog
  • Posts: 1,677
  • Joined: 24-August 11

Re: SQL Update query not updating record

Posted 17 January 2013 - 04:15 PM

If it's returning greater than 0, then it should be updating the record. This may seem silly, but are you sure that you're not just putting the same data back into the database? Are you refreshing your view after the update?
Was This Post Helpful? 0
  • +
  • -

#6 aortell24  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 07-December 12

Re: SQL Update query not updating record

Posted 17 January 2013 - 05:08 PM

shouldnt a postback cause the page to refresh
Was This Post Helpful? 0
  • +
  • -

#7 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1182
  • View blog
  • Posts: 1,677
  • Joined: 24-August 11

Re: SQL Update query not updating record

Posted 18 January 2013 - 06:50 AM

Yes, it should. Just for giggles, try explicitly refreshing the control that is displaying the data by calling DataBind again after the Update. Again, if the data you're updating with is different than what's in your database, and the Update() command is returning 1, then the command is technically working.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1