locked
HOW DO I: Insert on a table, using tableadapter? RRS feed

  • Question

  • Hi,

    I guess my subject said a lot of my problem, but anyways i will try
    to explain it.

    this is it:

      Im new at this c# stuff, so i was tryin' to insert
    a row, into a two colums table.

    after tryin' with the DataRow Class, I got to the "tableAdapter.insert" method.

    it worked once... but I could'nt commit the changes..
    not even using the AceptChanges method.

    anyways you get my point... anybody can help?
    Monday, September 25, 2006 6:27 AM

Answers

  • there is no TableAdapter.Insert method, unless perhaps (im unsure) you created it using a Wizard?

    basically programmatically, you create a dataset to hold all your records, and a tableAdapter.

    the tableAdapter requires a SelectCommand first, in its constructor (OleDbCommand or SqlCommand)

    Then afterwards, you can implement the InsertCommand in the table adapter and finally execute the Update() command to commit changes to the database.

    Example:

    DataSet theDataSet = new DataSet(); //you should declare this on a global scale level, at the top of the class

    SqlDataAdapter theDataAdapter = new SqlDataAdapter(); //as well as this.

    ..

    ..

    //fill records:

    SqlCommand theSQLCommand = new SqlCommand("SELECT * FROM tableName", new SqlConnection(connectionString);

    this.theDataAdapter.SelectCommand = theSQLCommand;

    theSQLCommand.Connection.Open();

    this.theDataAdapter.Fill(this.theDataSet);

    theSQLCommand.Connection.Close();

     

    this.theDataGridView.DataSource = this.theDataSet.Tables[index].DefaultView;

     

    this basically:

  • creates the appropriate objects for carrying out a selection of records from the database

  • fills the dataset with records from the given SqlCommand (object that contains our command to execute)

  • binds the dataset to the datagridview

     

    Now normally, when you update the dataGridView, say add new records, delete or modify records, it automatically commits those changes to the dataset, so when you finally want to commit to the database, simply call the update method on say, the click of a button:

    this.theDataAdapter.Update(this.theDataSet);

     

    there can be a couple of issues here:

  • It may throw an error stating the it needs an InsertCommand/UpdateCommand

    so you can do this before calling the Update() method:

    this.theDataAdapter.InsertCommand = new SqlCommandBuilder(this.theDataAdapter).GetInsertCommand();

     

    This would normally create the insertcommand, based on the dataset schema etc.... - you can do the same thing with the DeleteCommand and UpdateCommand - you may need these if you are performing deletion/updating.

     

    Now, specifically, if you do not have a datagridview for example, there are other ways of updating/inserting/deleting records in Sql. Just to add, the above example was used for SQL but you can easily change it for OleDb connection/type.

     

    You can say insert a row of record from the click of a button, giving it values from say textboxes.

     

    SqlCommand theSQLCommand = new SqlCommand("INSERT INTO tableName (field1, field2) VALUES (@param1, @param2);

    theSQLCommand.Connection = new SqlConnection(connectionString);

    SqlParameter param1 = new SqlParameter("@param1", SqlDbType.typeHere, size);

    param1.Value = valueHere;

    SqlParameter param2 = new SqlParameter("@param2", SqlDbType.typeHere, size);

    param2.Value = valueHere;

     

    theSQLCommand.Parameters.Add(param1);

    theSQLCommand.Parameters.Add(param2);

     

    theSQLCommand.Connection.Open();

    theSQLCommand.ExecuteNonQuery();

    theSQLCommand.Connection.Close();

     

    this will insert data into the database, giving it the values as parameters (see below why I used parameters), setting up the parameters correctly by giving it the datatype (which is an enum field) and the size of the field to expect/give.

    The same thing happens when you are deleting and updating a record, the syntax/T-SQL query changes - that's all there is too it really

     

    The reason I used parameters for this:

  • safety - protects from direct SQL Attacks, same thing also applies with OleDb

     

    the disadvantage of using the "SQL Injection" approach, as I am now (SELECT *....INSERT INTO.....)

  • slow

    the best practice would be to create a stored procedure in the database, then call it, giving it the parameters. The performance will be fast and having a parameter approach makes it securer.

     

    I think I've gone a bit off topic which I apologize, and also apologize if this is not what you are after however do hope it helps in some way :-)

     

Tuesday, September 26, 2006 6:29 PM

All replies

  •  

    may you are using VS 2005

    your data which juste be inserted still in your dataTable

    i t means that you need to update it to your database ( for example SqlServer ) to save your changes

    tableAdapter.update()

    // table Adapter is a name of your dataadapter of the table that ypou want to update it to database

     

    hope that helps you.

    Monday, September 25, 2006 6:43 AM
  • thx, for the answare but I guess its not exactly what im looking for...
    i mean, i did that once, and it worked.
    but now when i call tableadater.insert, it does'nt even insert,
    temporally on the table...
    Tuesday, September 26, 2006 5:28 PM
  • there is no TableAdapter.Insert method, unless perhaps (im unsure) you created it using a Wizard?

    basically programmatically, you create a dataset to hold all your records, and a tableAdapter.

    the tableAdapter requires a SelectCommand first, in its constructor (OleDbCommand or SqlCommand)

    Then afterwards, you can implement the InsertCommand in the table adapter and finally execute the Update() command to commit changes to the database.

    Example:

    DataSet theDataSet = new DataSet(); //you should declare this on a global scale level, at the top of the class

    SqlDataAdapter theDataAdapter = new SqlDataAdapter(); //as well as this.

    ..

    ..

    //fill records:

    SqlCommand theSQLCommand = new SqlCommand("SELECT * FROM tableName", new SqlConnection(connectionString);

    this.theDataAdapter.SelectCommand = theSQLCommand;

    theSQLCommand.Connection.Open();

    this.theDataAdapter.Fill(this.theDataSet);

    theSQLCommand.Connection.Close();

     

    this.theDataGridView.DataSource = this.theDataSet.Tables[index].DefaultView;

     

    this basically:

  • creates the appropriate objects for carrying out a selection of records from the database

  • fills the dataset with records from the given SqlCommand (object that contains our command to execute)

  • binds the dataset to the datagridview

     

    Now normally, when you update the dataGridView, say add new records, delete or modify records, it automatically commits those changes to the dataset, so when you finally want to commit to the database, simply call the update method on say, the click of a button:

    this.theDataAdapter.Update(this.theDataSet);

     

    there can be a couple of issues here:

  • It may throw an error stating the it needs an InsertCommand/UpdateCommand

    so you can do this before calling the Update() method:

    this.theDataAdapter.InsertCommand = new SqlCommandBuilder(this.theDataAdapter).GetInsertCommand();

     

    This would normally create the insertcommand, based on the dataset schema etc.... - you can do the same thing with the DeleteCommand and UpdateCommand - you may need these if you are performing deletion/updating.

     

    Now, specifically, if you do not have a datagridview for example, there are other ways of updating/inserting/deleting records in Sql. Just to add, the above example was used for SQL but you can easily change it for OleDb connection/type.

     

    You can say insert a row of record from the click of a button, giving it values from say textboxes.

     

    SqlCommand theSQLCommand = new SqlCommand("INSERT INTO tableName (field1, field2) VALUES (@param1, @param2);

    theSQLCommand.Connection = new SqlConnection(connectionString);

    SqlParameter param1 = new SqlParameter("@param1", SqlDbType.typeHere, size);

    param1.Value = valueHere;

    SqlParameter param2 = new SqlParameter("@param2", SqlDbType.typeHere, size);

    param2.Value = valueHere;

     

    theSQLCommand.Parameters.Add(param1);

    theSQLCommand.Parameters.Add(param2);

     

    theSQLCommand.Connection.Open();

    theSQLCommand.ExecuteNonQuery();

    theSQLCommand.Connection.Close();

     

    this will insert data into the database, giving it the values as parameters (see below why I used parameters), setting up the parameters correctly by giving it the datatype (which is an enum field) and the size of the field to expect/give.

    The same thing happens when you are deleting and updating a record, the syntax/T-SQL query changes - that's all there is too it really

     

    The reason I used parameters for this:

  • safety - protects from direct SQL Attacks, same thing also applies with OleDb

     

    the disadvantage of using the "SQL Injection" approach, as I am now (SELECT *....INSERT INTO.....)

  • slow

    the best practice would be to create a stored procedure in the database, then call it, giving it the parameters. The performance will be fast and having a parameter approach makes it securer.

     

    I think I've gone a bit off topic which I apologize, and also apologize if this is not what you are after however do hope it helps in some way :-)

     

Tuesday, September 26, 2006 6:29 PM
  • Actually this answare seemed to me very helpful, with only one problem..
    the Sqlcommand stuff, i cant find it nither as a DataType or a class.
    is there anything wrong with my IDE?
    Wednesday, September 27, 2006 7:34 PM
  • you need to import the System.Data.SqlClient namespace at the top of the class file to obtain the Sql classes
    Wednesday, September 27, 2006 7:46 PM