9 Replies - 4373 Views - Last Post: 27 March 2008 - 07:07 PM Rate Topic: -----

#1 kkgaming  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Updating Database from a cpanel page

Posted 27 March 2008 - 03:53 PM

I have populated information from my local database and added it to the cpanel page so a staff member can update the title (txtTitle.Text) and the content of the page (ftbContent.Text). Anyway, I am not sure how to update the database when they click the save changes button. I know I need an update statement, but how exactly do I get the database to update? Here is the table I am pulling the information from (in the database)

Posted Image

My code is below:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class cpanel : System.Web.UI.Page
{
	protected void Page_Init(object sender, EventArgs e)
	{
		//this is where you will check the session variable to determine if the user is logged in. If they aren't redirect them to the login page.

		//this is also where your select statements will go to populate the text boxes from the databases


		if (System.Convert.ToBoolean(Session["session"]) != true)
		{
			Response.Redirect("login.aspx");
		}


		//ConnectionStringSettings object to hold the string
		ConnectionStringSettings setting;
		//ConfigurationManager provides access to the config file
		setting = ConfigurationManager.ConnectionStrings["gcConnectionString"];
		//Name of your string, set in web.config
		string myConnectString = setting.ConnectionString;

		SqlConnection connection = new SqlConnection(myConnectString);
		connection.Open();

		//Start 1st reader---------------------------------------------------------------------

		//SqlCommand object for this connection
		SqlCommand command = new SqlCommand("Select page_content From content Where id = '1' ", connection);
		command.CommandType = CommandType.Text;

		SqlDataReader reader = command.ExecuteReader();

		//Display the results
		reader.Read();

		//If Statement for validation
		txtTitle.Text = reader.GetString(0).ToString();
	  

		reader.Close();

		//Start 2nd reader---------------------------------------------------------------------

		//SqlCommand object for this connection
		SqlCommand command2 = new SqlCommand("Select page_content From content Where id = '2' ", connection);
		command2.CommandType = CommandType.Text;

		SqlDataReader reader2 = command2.ExecuteReader();

		//Display the results
		reader2.Read();

		ftbContent.Text = reader2.GetString(0).ToString();


		reader2.Close();

	}


	protected void SaveChanges(object sender, EventArgs e)
	{
	   
	
	
	
	
	
	}


}

   
  


Would one of my update statements look like this:

 SqlCommand command = new SqlCommand("Update page_content Set page_content = 'ftbContent.Xhtml.ToString' From content Where id = '1' ", connection);

This post has been edited by kkgaming: 27 March 2008 - 04:18 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Updating Database from a cpanel page

#2 PsychoCoder  Icon User is offline

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

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

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 04:17 PM

You're going to want to use an UPDATE Statement. That links shows you how to structure your statement. If you have problems once you start your code then post the code and an explanation of whats going wrong.
Was This Post Helpful? 0
  • +
  • -

#3 kkgaming  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 04:20 PM

When I use this as my update statement:

SqlCommand command = new SqlCommand("Update page_content Set page_content = 'ftbContent.Xhtml.ToString' From content Where id = '1' ", connection);


and I click the button, nothing changes.. how do I tie it into the button click?

Here is the updated code:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class cpanel : System.Web.UI.Page
{
	protected void Page_Init(object sender, EventArgs e)
	{
		//this is where you will check the session variable to determine if the user is logged in. If they aren't redirect them to the login page.

		//this is also where your select statements will go to populate the text boxes from the databases


		if (System.Convert.ToBoolean(Session["session"]) != true)
		{
			Response.Redirect("login.aspx");
		}


		//ConnectionStringSettings object to hold the string
		ConnectionStringSettings setting;
		//ConfigurationManager provides access to the config file
		setting = ConfigurationManager.ConnectionStrings["gcConnectionString"];
		//Name of your string, set in web.config
		string myConnectString = setting.ConnectionString;

		SqlConnection connection = new SqlConnection(myConnectString);
		connection.Open();

		//Start 1st reader---------------------------------------------------------------------

		//SqlCommand object for this connection
		SqlCommand command = new SqlCommand("Select page_content From content Where id = '1' ", connection);
		command.CommandType = CommandType.Text;

		SqlDataReader reader = command.ExecuteReader();

		//Display the results
		reader.Read();

		txtTitle.Text = reader.GetString(0).ToString();
	  

		reader.Close();

		//Start 2nd reader---------------------------------------------------------------------

		//SqlCommand object for this connection
		SqlCommand command2 = new SqlCommand("Select page_content From content Where id = '2' ", connection);
		command2.CommandType = CommandType.Text;

		SqlDataReader reader2 = command2.ExecuteReader();

		//Display the results
		reader2.Read();

		ftbContent.Text = reader2.GetString(0).ToString();


		reader2.Close();

	}


	protected void SaveChanges(object sender, EventArgs e)
	{


		//ConnectionStringSettings object to hold the string
		ConnectionStringSettings setting;
		//ConfigurationManager provides access to the config file
		setting = ConfigurationManager.ConnectionStrings["gcConnectionString"];
		//Name of your string, set in web.config
		string myConnectString = setting.ConnectionString;

		SqlConnection connection = new SqlConnection(myConnectString);
		connection.Open();


		//SqlCommand object for this connection
	  
			
			SqlCommand command = new SqlCommand("Update page_content Set page_content = 'ftbContent.Xhtml.ToString' From content Where id = '1' ", connection);
			command.CommandType = CommandType.Text;
		
	
	}


}

   
  


Here is a picture of the cpanel page:

http://www.phantom36...ages/cpanel.png

This post has been edited by kkgaming: 27 March 2008 - 04:27 PM

Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder  Icon User is offline

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

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

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 04:38 PM

You set it up but never tell it to execute. To execute it, since it's an update statement, you wont be returning any values so we will use ExecuteNonQuery to execute the query. ExecuteNonQuery will return the number of rows that are affected, so we can check that value to see if it was a successful update.

Look at the code below


protected void SaveChanges(object sender, EventArgs e)
    {


        //ConnectionStringSettings object to hold the string
        ConnectionStringSettings setting;
        //ConfigurationManager provides access to the config file
        setting = ConfigurationManager.ConnectionStrings["gcConnectionString"];
        //Name of your string, set in web.config
        string myConnectString = setting.ConnectionString;

        SqlConnection connection = new SqlConnection(myConnectString);
        
        //create a string to hold our query
         string query = "Update content  Set page_content = '" + ftbContent.Xhtml.ToString + "'  Where id = " + YourIdVariable;
        //SqlCommand object for this connection
        SqlCommand command = new SqlCommand();
        command.CommandText = query;
        command.CommandType = CommandType.Text;
        command.Connection = connection;
 
        //open our connection
        connection.Open()

        //now execute the query and check the status
        if(!(command.ExecuteNonQuery > 0))
        {
               Response.Write("There was a problem updating your settings. Please contact the site administrator");
         }
    }




Now some of the things in my example will need to change, like where I use YourIdVariable will need to be changed to whatever you're using to hold the ID value, and the message I print, you may want something different.
Was This Post Helpful? 0
  • +
  • -

#5 kkgaming  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 04:50 PM

For some reason I am getting an error:

Compiler Error Message: CS0019: Operator '>' cannot be applied to operands of type 'method group' and 'int'

Source Error:



Line 97:
Line 98: //now execute the query and check the status
Line 99: if(!(command.ExecuteNonQuery > 0))
Line 100: {
Line 101: Response.Write("There was a problem updating your settings. Please contact the site administrator");



I don't have a variable holding the id number, I just have id number 1 and 2 in database.

This post has been edited by kkgaming: 27 March 2008 - 04:56 PM

Was This Post Helpful? 0
  • +
  • -

#6 PsychoCoder  Icon User is offline

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

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

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 05:04 PM

Sometimes you're going to have to figure out errors on your own. You cant always just assume someone is going to fix errors, as a programmer you need to learn to figure out what is causing the error.

I don't mind helping people, but there has to come a time where you take the steps necessary to be able to find the error and resolve it, especially 1 as simple as this one


protected void SaveChanges(object sender, EventArgs e)
    {


        //ConnectionStringSettings object to hold the string
        ConnectionStringSettings setting;
        //ConfigurationManager provides access to the config file
        setting = ConfigurationManager.ConnectionStrings["gcConnectionString"];
        //Name of your string, set in web.config
        string myConnectString = setting.ConnectionString;

        SqlConnection connection = new SqlConnection(myConnectString);
        
        //create a string to hold our query
         string query = "Update content  Set page_content = '" + ftbContent.Xhtml.ToString + "'  Where id = 1";
        //SqlCommand object for this connection
        SqlCommand command = new SqlCommand();
        command.CommandText = query;
        command.CommandType = CommandType.Text;
        command.Connection = connection;
 
        //open our connection
        connection.Open()

        //now execute the query and check the status
        if(command.ExecuteNonQuery() == 0)
        {
               Response.Write("There was a problem updating your settings. Please contact the site administrator");
         }
    }


Was This Post Helpful? 0
  • +
  • -

#7 kkgaming  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 05:24 PM

Your right, I am trying to figure out what is wrong but sometimes I need some extra help, but yeah, that was an easy fix. Anyway thanks a lot for the help PsychoCoder. I took out the pluses in the Select Statement, but that didn't update right. It says something like:

Operator '+' cannot be applied to operands of type 'string' and 'method group'

I am not sure how I would change the select statement.
Was This Post Helpful? 0
  • +
  • -

#8 Jayman  Icon User is offline

  • Student of Life
  • member icon

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

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 05:54 PM

Need to add the constructor () to the ToString method.

string query = "Update content  Set page_content = '" + ftbContent.Xhtml.ToString() + "'  Where id = 1";

Was This Post Helpful? 0
  • +
  • -

#9 kkgaming  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 06:05 PM

Oh duh, dunno how I missed that.. but know I am getting...

Incorrect syntax near 's'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Source Error:


Line 97:
Line 98: //now execute the query and check the status
Line 99: if (command.ExecuteNonQuery() == 0)
{
Response.Write("There was a problem updating your settings. Please contact the site administrator");
}
Line 100: connection.Close();
Line 101: }
Was This Post Helpful? 0
  • +
  • -

#10 kkgaming  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 75
  • Joined: 07-February 07

Re: Updating Database from a cpanel page

Posted 27 March 2008 - 07:07 PM

I got it to work, with this: Thanks everyone for the help!

 protected void SaveChanges(object sender, EventArgs e)
	{


		//ConnectionStringSettings object to hold the string
		ConnectionStringSettings setting;
		//ConfigurationManager provides access to the config file
		setting = ConfigurationManager.ConnectionStrings["gcConnectionString"];
		//Name of your string, set in web.config
		string myConnectString = setting.ConnectionString;

		SqlConnection connection = new SqlConnection(myConnectString);
		connection.Open();

		//create a string to hold our query
		 SqlCommand command = new SqlCommand("update content set page_content = '" + ftbContent.Xhtml.ToString() + "' where content_item = 'content'");
		//SqlCommand object for this connection
		command.CommandType = CommandType.Text;
		command.Connection = connection;
		
		

		//now execute the query and check the status
		if (command.ExecuteNonQuery() == 1)
			lblNotice.Text = "Update Successful!";

		connection.Close();

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1