I am using SQL server 2005 and VS2008.
I have used 4 text boxes to show the data from 4 columns of the database I have created. I am using dataadapter and dataset to retrieve the database and show in the textboxes.
con1 = new SqlConnection("Server=(local); uid=sa; pwd=123; database=ado");
da1 = new SqlDataAdapter("Select * from adotable", con1);
da1.MissingSchemaAction = MissingSchemaAction.AddWithKey;
ds1 = new DataSet();
da1.Fill(ds1, "peple");
textBox1.Text = ds1.Tables["peple"].Rows[rownum][0].ToString();
textBox2.Text = ds1.Tables["peple"].Rows[rownum][1].ToString();
textBox3.Text = ds1.Tables["peple"].Rows[rownum][2].ToString();
textBox4.Text = ds1.Tables[0].Rows[rownum][3].ToString();
When I try to insert a new value in the dataset or update a row or delete a row in the dataset it works fine.
When I try to update the database it works fine for insert but the database doesn't get updated for delete row and update row in dataset.
The following is the code for insert, update, delete respectively
private
void button6_Click(object sender, EventArgs e)
{
DataRow datrow = ds1.Tables[0].NewRow();
datrow[0] = textBox1.Text;
datrow[1] = textBox2.Text;
datrow[2] = textBox3.Text;
datrow[3] = textBox4.Text;
ds1.Tables[0].Rows.Add(datrow);
MessageBox.Show("Row added to datatable");
}
private
void button7_Click(object sender, EventArgs e)
{
ds1.Tables[0].Rows[rownum][0] = textBox1.Text;
ds1.Tables[0].Rows[rownum][1] = textBox2.Text;
ds1.Tables[0].Rows[rownum][2] = textBox3.Text;
ds1.Tables[0].Rows[rownum][3] = textBox4.Text;
ds1.AcceptChanges();
MessageBox.Show("Row updated");
button1.PerformClick();
}
private
void button8_Click(object sender, EventArgs e)
{
ds1.Tables[0].Rows[rownum].Delete();
ds1.AcceptChanges();
MessageBox.Show("Row deleted");
}
For updating the database I am using :
private
void button9_Click(object sender, EventArgs e) // This doesn't work for deleted row or updated row. This works fine for inserted row.
{
SqlCommandBuilder cb1 = new SqlCommandBuilder(da1);
da1.Update(ds1,
"peple");
}
Please help