C# Website Records

How to add records to my database.

Page 1 of 1

3 Replies - 3175 Views - Last Post: 14 November 2007 - 10:37 AM Rate Topic: -----

#1 dmxxmd   User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 30
  • Joined: 26-October 07

C# Website Records

Post icon  Posted 11 November 2007 - 11:12 PM

Hi guys, Im busy building my own website I got alot of textboxes where I view my data, and that works so I can click on the next button and all of the textboxes updates with the next record..
Now I wanto add another button that empties the tboxes and then create a whole new row in my database with info that I type in by hand and after that I need to save it...

My code to view it looks like this more or less..
		if (!Page.IsPostBack)
		{
			_ds = new DataSet();
	SqlConnection _MyCon = new SqlConnection("Server=DDDDS;database=SPAudit;uid=SSA;pwd=SSA;Connection Timeout=120;pooling=false");

			SqlDataAdapter _da = new SqlDataAdapter("SELECT * FROM MainDetail", _MyCon);

			_da.SelectCommand.CommandType = CommandType.Text;
			_MyCon.Open();
			_da.Fill(_ds);

			_MyCon.Close();

			Session["mydata"] = _ds;

			int index = indexer + 1;
			tbIndex.Text = index.ToString();
			indexer = 0;
			showName();
		}
	}

protected void getNextRecord(object sender, EventArgs e)
	{
		indexer = int.Parse(tbIndex.Text.ToString());
		tbIndex.Text = indexer.ToString();
		indexer += 1;
		tbIndex.Text = indexer.ToString();
		showName();
	}

	protected void getPrevRecord(object sender, EventArgs e)
	{
		indexer = int.Parse(tbIndex.Text.ToString());
		tbIndex.Text = indexer.ToString();
		indexer -= 1;
		tbIndex.Text = indexer.ToString();
		showName();
	}
 private void showName()
	{
		_ds = (DataSet)Session["mydata"];
		TextBoxAuditeeName.Text = _ds.Tables[0].Rows[indexer]["AuditeeName"].ToString();
		//ta2.Text = _ds.Tables[0].Rows[indexer]["RespBU"].ToString();
		//TextBox3.Text = _ds.Tables[0].Rows[indexer]["AuditNameCode"].ToString();
		TextBoxBU.Text = _ds.Tables[0].Rows[indexer]["BU"].ToString();
		TextBoxRP.Text = _ds.Tables[0].Rows[indexer]["RespPerson"].ToString();
		TextBoxNrOfEmpl.Text = _ds.Tables[0].Rows[indexer]["NrOfEmpl"].ToString();
		TextBoxTOC.Text = _ds.Tables[0].Rows[indexer]["TypeOfCompany"].ToString();
		TextBoxBEEST.Text = _ds.Tables[0].Rows[indexer]["BEEStatus"].ToString();
		TextBoxSOW.Text = _ds.Tables[0].Rows[indexer]["SOW"].ToString();
		TextBoxCEO.Text = _ds.Tables[0].Rows[indexer]["CEO"].ToString();
		TextBoxCEOFax.Text = _ds.Tables[0].Rows[indexer]["CEOFax"].ToString();
		TextBoxCEOCont.Text = _ds.Tables[0].Rows[indexer]["CEOContctNumber"].ToString();
		TextBoxCEOE.Text = _ds.Tables[0].Rows[indexer]["CEOFax"].ToString();

	}
}



Please help me ho to add new records and then save them to the database...thanx

I appriciate it..
DMXXMD

Is This A Good Question/Topic? 0
  • +

Replies To: C# Website Records

#2 PsychoCoder   User is offline

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

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

Re: C# Website Records

Posted 12 November 2007 - 06:13 AM

Well first, to clear the form use a snippet I wrote for clearing a form with the click of a button, without having to reference each control individually, since you appear to have only textboxes I modified the snippet to only check for textboxes, it looks like:

public static void ClearForm(System.Windows.Forms.Control parent)
{
	foreach (System.Windows.Forms.Control ctrControl in parent.Controls)
	{
		 //Loop through all controls 
		 if (object.ReferenceEquals(ctrControl.GetType(), typeof(System.Windows.Forms.TextBox)))
		 {
			  //Check to see if it's a textbox
			 //If it is then set the text to String.Empty (empty textbox) 
			  ((System.Windows.Forms.TextBox)ctrControl).Text = string.Empty;
		  }
	 }
}



Then on the click of your Clear you call it like so ClearForm(this);, so that part is out of the way. Then for adding the record you're going to want to use either parameterized SQL, or a stored procedure to protect yourself from a SQL Injection Attack. In this example Ill be using parameterized SQL for inserting a new record.

The easiest way to do this is to create a function and pass your variables to enter, but since you have a ton of items to insert we'll go this route (you can always change it if need be. In this example I will only be using 2 or 3 variables as this is just an example of how to do this.

private void InsertRecord
{
	 //create out SqlConnection and
	//SqlCommand objects
	SqlConnection conn = new SqlConnection("YourConnectionStringHere");
	SqlCommand cmd = new SqlCommand();
	//string variable to hold your query, you will
	//notice the @value1, @value2.., this is how you
	//use parameterized SQL, doing it this way prevents
	//a SQL Injection (well makes it far more difficult
	string query = "INSERT INTO table_name VALUES(@value1,@value2,@value3);
	//now we set the properties of our SqlCommand object,
	//along with adding your parameters
	//first tell it what it's going to execute
	cmd.CommandText = query;
	//now tell it it's going to be executing inline sql
	//We would have used CommandType.StoredProcedure
	//if we were executing a stored procedure
	cmd.CommandType = CommandType.Text;  
	//now we will use AddWithValue to add our
	//parameters to our query via the SqlCommand object
	cmd.Parameters.AddWithValue("@value1", TextBox1.Text);
	cmd.Parameters.AddWithValue("@value2,TextBox2.Text)
	cmd.Parameters.AddWithValue("@value3,TextBox3.Text)
	//now tell it what connection to use
	cmd.Connection = conn;
	//open the connection to the database
	conn.open
	//now use ExecuteNonQuery to execute our statement
	cmd.ExecuteNonQuery();
}



Now, I did not add the try...catch block but when dealing with database work always put your code inside a try...catch block, so if anything happens i.e.; connection is lost, database is down, etc. it will catch them and let you know so you can display a nice message to the user.

You will notice that I used AddWithValue, this is what you want to use when using parameterized SQL< or a stored procedure for that matter, this is the new way of doing it in .Net 2.0, for 1.1 it was Add and you had to provide the data type, with the new way there is no need to add the data type.

For executing the query I used ExecuteNonQuery always use this method when you're not returning and values with your query, such as inserting a record. It does however return a value, the number of records affected, for an insert it is generally 1. You can also use this to determine if the insert was successful, like so

 int status = cmd.ExecuteNonQuery();
if(!(status = 1)
{
	 //Display a failure message
}
else
{
	 //display your successful message
}



There are a couple other methods for executing a SQL query:

I hope this helps :)
Was This Post Helpful? 0
  • +
  • -

#3 dmxxmd   User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 30
  • Joined: 26-October 07

Re: C# Website Records

Posted 12 November 2007 - 06:36 AM

Hi There thank you for replying, Quite a smart code you gave me but the thing is im using a website so on that code it gives me a error (Error1The type or namespace name 'Windows' does not exist in the namespace 'System' (are you missing an assembly reference?)

Please assist me :) thank you for your time PshycoCoder

DMXXMD
Was This Post Helpful? 0
  • +
  • -

#4 PsychoCoder   User is offline

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

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

Re: C# Website Records

Posted 14 November 2007 - 10:37 AM

Oops, you're right, I forgot to alter this, it should be

public static void ClearForm(Control parent)
{
	foreach (Control ctrControl in parent.Controls)
	{
		//Loop through all controls
		if (object.ReferenceEquals(ctrControl.GetType(), typeof(TextBox)))
		{
			//Check to see if it's a textbox
			//If it is then set the text to String.Empty (empty textbox)
			((TextBox)ctrControl).Text = string.Empty;
		}
		if (ctrControl.HasChildren)
		{
			ClearForm(parent);
		}
	}
}



Also made it recursive since all controls (GroupBox, Panel, etc) are "parents" and will contain controls, so this will call itself until no more TextBoxes are found. I apologize :) This code now compiles and works :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1