Inserting data to a sql database table creating a new datarow instance

[Nishara]

Hi

I am trying to insert data into a sql server database table using sqlDataAdapter and adding newrow method.But the values entered are not saved in the database.What I did is............

string sql = "Select * from Users";               
                dataCmd.CommandText = sql;

                SqlDataAdapter da = new SqlDataAdapter(dataCmd);
                da.SelectCommand = new SqlCommand(sql, cn);

                da.SelectCommand.ExecuteNonQuery();

                DataTable UserTable = new DataTable();
                da.Fill(UserTable);


                DataRow dtr;
                dtr = UserTable.NewRow(); 

                dtr["UserID"] = txtID.Text;                
                dtr["User_Name"] = txtName.Text;              

                UserTable.Rows.Add(dtr);               
                MessageBox.Show("Row Inserted");
                this.usersTableAdapter1.Fill(this.testDataSet.Users);

What has gone wrong here?????

Or can you help me to solve this???

Thanks a lot..

[sknake]

You need to specify a Select, Insert, Delete, and Update query for your data adapter. You also need to call .Update() on your data adapter after you add, edit, or delete a DataRow in your DataSet/DataTable.

If you modify a row then re-fill your dataset you're blowing away the changes.

[DanielGreen]

Hello!

You forgot to update the SqlDataAdapter -> an example from MSDN

public DataSet CreateCmdsAndUpdate(string connectionString,
    string queryString) 
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.SelectCommand = new OleDbCommand(queryString, connection);
        OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

        connection.Open();

        DataSet customers = new DataSet();
        adapter.Fill(customers);

        //code to modify data in dataset here

        adapter.Update(customers);

        return customers;
    }
}

In your case you must do this:

SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.Update(UserTable);

hope this helps!

Daniel