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:
CODE
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.
CODE
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
CODE
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
11 Nov, 2007 - 10:12 PM
| 