how to insert data in database using dataset in c# windows application

[aliiya]

hi!
I am trying to insert data from textboxes in sql server 2005 database using dataset(table adapters) in c# windows application
herez the simplest coding that i know but its not working.
I have made two tables in my database i.e. product and country table.
In country table adapter i add query of insert and execute there and its working correctly but when i m using textboxes to insert data in tables, it not works and even not giving any errors or exceptions.

plz help me out!

private void button1_Click(object sender, EventArgs e)
        {
            DataSet1TableAdapters.countryTableAdapter cta = new WindowsApplication4.DataSet1TableAdapters.countryTableAdapter();
            DataSet1.countryDataTable ctbl = new DataSet1.countryDataTable();
            cta.Insertdata(textBox1.Text, textBox2.Text);
        }

[sanch01r]

Aliiya,
You can add values from directly to a database using the following example, However, I recommend creating a stored procedure instead of using variables within your string, I just did it this way as an example:

string  _InsertRow = "INSERT INTO `"+ DatabaseName +"`.`" + TableName +"` (`FirstName` ,`LastName` ,`Company` ,`E-Mail` ,`Phone` ,`ProgrammingLanguage` , `DateTime`)VALUES ('" + fName + "', '" + lName + "', '" + comp + "', '" + email + "', '" + phone + "', '" + proglang + "', '" + datetime + "');";
                
                
                
                SqlConnection conn = new SqlConnection(_ConnStr);
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = _InsertRow;


                cmd.Parameters.Add(new SqlParameter("@FirstName", MySqlDbType.VarChar, 50)).Value = txtFName.Text;
                cmd.Parameters.Add(new SqlParameter("@LastName", MySqlDbType.VarChar, 50)).Value = txtLName.Text;
                cmd.Parameters.Add(new SqlParameter("@Company", MySqlDbType.VarChar, 50)).Value = txtCompany.Text;
                cmd.Parameters.Add(new SqlParameter("@E-Mail", MySqlDbType.VarChar, 50)).Value = txtEmail.Text;
                cmd.Parameters.Add(new SqlParameter("@Phone", MySqlDbType.VarChar, 50)).Value = txtPhone.Text;
                cmd.Parameters.Add(new SqlParameter("@ProgrammingLanguage", MySqlDbType.VarChar, 50)).Value = txtProgrammingLanguage.Text;
                cmd.Parameters.Add(new SqlParameter("@DateTime", MySqlDbType.VarChar, 50)).Value = txtDateTime.Text;


                conn.Open();

                cmd.ExecuteNonQuery();

[sknake]

Aliiya,
You can add values from directly to a database using the following example, However, I recommend creating a stored procedure instead of using variables within your string, I just did it this way as an example:

string  _InsertRow = "INSERT INTO `"+ DatabaseName +"`.`" + TableName +"` (`FirstName` ,`LastName` ,`Company` ,`E-Mail` ,`Phone` ,`ProgrammingLanguage` , `DateTime`)VALUES ('" + fName + "', '" + lName + "', '" + comp + "', '" + email + "', '" + phone + "', '" + proglang + "', '" + datetime + "');";
                
                
                
                SqlConnection conn = new SqlConnection(_ConnStr);
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = _InsertRow;


                cmd.Parameters.Add(new SqlParameter("@FirstName", MySqlDbType.VarChar, 50)).Value = txtFName.Text;
                cmd.Parameters.Add(new SqlParameter("@LastName", MySqlDbType.VarChar, 50)).Value = txtLName.Text;
                cmd.Parameters.Add(new SqlParameter("@Company", MySqlDbType.VarChar, 50)).Value = txtCompany.Text;
                cmd.Parameters.Add(new SqlParameter("@E-Mail", MySqlDbType.VarChar, 50)).Value = txtEmail.Text;
                cmd.Parameters.Add(new SqlParameter("@Phone", MySqlDbType.VarChar, 50)).Value = txtPhone.Text;
                cmd.Parameters.Add(new SqlParameter("@ProgrammingLanguage", MySqlDbType.VarChar, 50)).Value = txtProgrammingLanguage.Text;
                cmd.Parameters.Add(new SqlParameter("@DateTime", MySqlDbType.VarChar, 50)).Value = txtDateTime.Text;


                conn.Open();

                cmd.ExecuteNonQuery();

I don't get it... you're building the query dynamically with what seems to be inserting the values directly in to the command text, but also adding parameters?

Here is another example:

public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    private void simpleButton1_Click(object sender, EventArgs e)
    {
      const string query = "Insert Into Employees (RepNumber, HireDate) Values (@RepNumber, @HireDate)";
      string connStr = BuildSqlNativeConnStr("apex2006sql", "Leather");

      try
      {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand(query, conn))
          {
            cmd.Parameters.Add(new SqlParameter("@RepNumber", 50));
            cmd.Parameters.Add(new SqlParameter("@HireDate", DateTime.Today));
            cmd.ExecuteNonQuery();
          }
        }
      }
      catch (SqlException)
      {
        System.Diagnostics.Debugger.Break();
      }
    }

[sanch01r]

Yes, you can insert data from text boxes directly to a table, you don't have to use the table adapter. My example above creates a new row and inserts the text box values.

[Marcwolf]

Or one can create a stored procedure and call it from within your application. This way you get an additional layer between your raw table data and your application.
Myself - I use stored procedures for doing certain auditing tasks, checking the their has been no data collision (Persons A updates before Person B updates overwriting Persons A changes). In many of my applications the direct access to the tables is forbidden - for example - medical records can be very sensitive and you want to know who has accessed them and for what. Using a stored procedure to get the record one can also have additional information in the parameters with the reader's name and the purpose of the read.
Another very successful Auditing system I have seen is one that takes a snapshop before and after any changes and saves that in a seperate database.
For many people this level of security will be far more than what they need but without stored procedures it would have to be done all in code which can easily be undone if the underling tables permissions allow the users to access directly.
Take Care
Dave