Page_load

[lebanese007]

Hi all,
what i'm trying to do here is when i open a page, it goes to a db and pull some info and display it in a textbox, then if i edit that text box, and hit save, it gets updated it in database.

I'm establishing a connection to the db under protected void Page_Load (object sender, EventArgs e) and it is getting the value correctly, below is the code for that.

 
SqlConnection conn;
conn = new SqlConnection("Data Source=SQLServer;Initial Catalog=I3_IC;Persist Security Info=True;User ID=username;Password=password");
 
SqlDataReader rdr = null;
 try
   {
     conn.Open();
     SqlCommand cmd = new SqlCommand("select Updateable_Message from UpdateableMessage", conn);
 
    rdr = cmd.ExecuteReader();
 
    while (rdr.Read())
       {
        TextBox.Text = rdr[0].ToString();
       }
  }
 
 finally
       {
        // close the reader
        if (rdr != null)
           {
                 rdr.Close();
            }
 
        // Close the connection
        if (conn != null)
            {
                conn.Close();
             }
            }

Below is my code for the save button

 
 protected void ButtonSave_Click(object sender, EventArgs e)
    {
 
 
        SqlDataSource dashDataSource = new SqlDataSource();
        dashDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["CRMConnectionString1"].ToString();
 
        dashDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
        dashDataSource.InsertCommand = "Update UpdateableMessage set Updateable_Message = @strUpdateMessage";
 
        dashDataSource.InsertParameters.Add("strUpdateMessage", TextBox.Text);
        int rowsAffected = 0;
 
        try
        {
            rowsAffected = dashDataSource.Insert();
        }
 
        catch (Exception ex)
        {
            Server.Transfer("Problem.aspx");
        }
 
        finally
        {
            dashDataSource = null;
        }
 
        if (rowsAffected != 1)
        {
            //Server.Transfer("Problem.aspx");
            LabelResult.Text = "The Hot Topic Message hasn't been updated successfully";
        }
        else
        {
            LabelResult.Text = "The Hot Topic Message has been updated successfully";
            TextBox.Text = "";
 
            //  Server.Transfer("AddRecords.aspx");
 
        }
 
    }
 

Now, if i hit save, it doesn't update!
if i removed the code from the Page_load, and when i first load the page, i don't see what is in the db Obviously and if i hit save the data in the text file gets saved successfully.

it is like either or :(
I'm sure there is way around it, but how?
any feedback is appriciated.

thanks in advance.

[balabaster]

I don't think your code is doing exactly what you think it is...
In your page load, you're running off to the database with the connection string "Data Source=SQLServer;Initial Catalog=I3_IC;Persist Security Info=True;User ID=username;Password=password" and getting a list of items from your table (satisfied by the query "Select Updateable_Message from UpdateableMessage")...that list may contain 1 item or it may contain 100 - either way, the list of items you retrieve is of an arbitrary length defined by the number of items in the table in your database.

Once you've got your list of items, you're going to loop through the list and on each iteration of the loop, you're setting the value in the text box to the value of the current item in the list... as the textbox is only ever storing the value for the current iteration of your loop, I'm assuming that the table will only ever contain a single row - if that assumption is correct, then you may as well skip the loop using:

if(rdr.HasRows()){
  rdr.read();
  TextBox1.Text = rdr[0].ToString();
}

Now, when you click your save button, are you updating your table or inserting a new item into your table? If you're updating which your design implies, you need the following:

SqlCommand oUpdateCmd = New SqlCommand("Update MyTable Set MyField = @Value", oCon);
oUpdateCmd.Parameters.Add(New SqlParameter("Value", TextBox1.Text);
oUpdateCmd.ExecuteNonQuery();

If you're actually inserting, then you need:

SqlCommand oInsertCmd = New SqlCommand("Insert Into MyTable(MyField) Values(@Value1)", oCon);
oInsertCmd.Parameters.Add("Value1", TextBox1.Text);
oInsertCmd.ExecuteNonQuery();

You will notice that other than the fact that my Sql query is different and I've named my variables differently, the two blocks of code are exactly the same structurally.

Does that make sense?

[lebanese007]

I don't think your code is doing exactly what you think it is...
In your page load, you're running off to the database with the connection string "Data Source=SQLServer;Initial Catalog=I3_IC;Persist Security Info=True;User ID=username;Password=password" and getting a list of items from your table (satisfied by the query "Select Updateable_Message from UpdateableMessage")...that list may contain 1 item or it may contain 100 - either way, the list of items you retrieve is of an arbitrary length defined by the number of items in the table in your database.

Once you've got your list of items, you're going to loop through the list and on each iteration of the loop, you're setting the value in the text box to the value of the current item in the list... as the textbox is only ever storing the value for the current iteration of your loop, I'm assuming that the table will only ever contain a single row - if that assumption is correct, then you may as well skip the loop using:

if(rdr.HasRows()){
  rdr.read();
  TextBox1.Text = rdr[0].ToString();
}

Now, when you click your save button, are you updating your table or inserting a new item into your table? If you're updating which your design implies, you need the following:

SqlCommand oUpdateCmd = New SqlCommand("Update MyTable Set MyField = @Value", oCon);
oUpdateCmd.Parameters.Add(New SqlParameter("Value", TextBox1.Text);
oUpdateCmd.ExecuteNonQuery();

If you're actually inserting, then you need:

SqlCommand oInsertCmd = New SqlCommand("Insert Into MyTable(MyField) Values(@Value1)", oCon);
oInsertCmd.Parameters.Add("Value1", TextBox1.Text);
oInsertCmd.ExecuteNonQuery();

You will notice that other than the fact that my Sql query is different and I've named my variables differently, the two blocks of code are exactly the same structurally.

Does that make sense?

balabaster, thanks for your reply.
You are right about the loop, it is just one string from the field in the table. I will remove that..

about the save button, i actually updating that record and i can see it in the table if i don't have the the code in the page_load section. if i do have the code in the page_load the problem is once i hit save, it comes back with the old information!

[balabaster]

Oh, hehe, I just realised what the problem is - when you submit, page load fires again causing what you wrote to be replaced with what is in the database once more. To fix the problem, in your page_load method, wrap the code with:

if(!page.IsPostBack){
//Do my stuff
}

Now it will load what is in the database only when the page loads. So when you write in the textbox and hit send, it'll upload it to the database.

[lebanese007]

YOU ARE THE MAAAAAAAAAAAAAN...
thanks..

one more question....now when i enter the save button everything gets saved but when it comes back tellimg that the operation was sucessful, there is no data in the textbox... do i need to put a select code simmilar to the one in the page_load right after i update, within the protected void ButtonSave_Click(object sender, EventArgs e) ?
Thanks again.

[balabaster]

YOU ARE THE MAAAAAAAAAAAAAN...
thanks..

one more question....now when i enter the save button everything gets saved but when it comes back tellimg that the operation was sucessful, there is no data in the textbox... do i need to put a select code simmilar to the one in the page_load right after i update, within the protected void ButtonSave_Click(object sender, EventArgs e) ?
Thanks again.

Change the EnableViewState property of your textbox to be true instead of false.

[lebanese007]

got it... i was clearing it right after the sucessfull message. duh

thanks man for all your help
be the way, it did not like

if(rdr.HasRows())
{
  rdr.Read();
TextBox.Text = rdr[0].ToString();
}
 

Compiler Error Message: CS1955: Non-invocable member 'System.Data.Common.DbDataReader.HasRows' cannot be used like a method.

[balabaster]

got it... i was clearing it right after the sucessfull message. duh

thanks man for all your help
be the way, it did not like
if(rdr.HasRows())
{
rdr.Read();
TextBox.Text = rdr[0].ToString();
}

Compiler Error Message: CS1955: Non-invocable member 'System.Data.Common.DbDataReader.HasRows' cannot be used like a method.

Try removing the () so it just says if(rdr.HasRows){ //stuff }

I need to get back to coding C# on a full time basis - my translation skills are starting to get rusty...I spend most of my time coding in VB now... it's all the same, just different syntax.