update using sqldataadapter

  • Thread starter Thread starter Deep
  • Start date Start date
D

Deep

dear friend

I have written a code to update table using sqldataadapter. It is
not giving error but not updating table. Is it incomplete. Please tell
me how to do it?

protected void btnUpd_Click(object sender, EventArgs e)
{
string sqlUpd = "update empl2 set name=@name, age=@age,
salary=@salary, gender=@gender where empid=@empid";
string sqlData = "select * from empl2";
SqlDataAdapter sda = new SqlDataAdapter();
SqlCommand cmdselect = new SqlCommand(sqlData,con);
sda.SelectCommand = cmdselect;
DataSet ds = new DataSet();
sda.Fill(ds, "Empl");
SqlCommand cmdUpd = new SqlCommand();
cmdUpd.CommandText = sqlUpd;
cmdUpd.Parameters.Add("@name", SqlDbType.VarChar);
cmdUpd.Parameters["@name"].Value = txtName.Text;

cmdUpd.Parameters.Add("@age", SqlDbType.Int);
cmdUpd.Parameters["@age"].Value = txtAge.Text;

cmdUpd.Parameters.Add("@salary", SqlDbType.Float);
cmdUpd.Parameters["@salary"].Value = txtSalary.Text;

cmdUpd.Parameters.Add("@gender", SqlDbType.VarChar);
cmdUpd.Parameters["@gender"].Value =
ddlGender.SelectedItem.Value;

cmdUpd.Parameters.Add("@empid", SqlDbType.Int);
cmdUpd.Parameters["@empid"].Value = txtEmpId.Text;
sda.UpdateCommand = cmdUpd;
sda.Update(ds, "Empl");


//cmd.Connection = con;
//con.Open();
//cmd.ExecuteNonQuery();
//con.Close();
}

Thanks In Advance
 
The SqlDataAdapter UpdateCommand is sort of like a precursor to Linq To
Sql for Entities where it acts on a dataTable (in memory dataTable from
your app). Here is a scenario that I slapped together:


private void btnBasicUpdate1_Click_1(...)
{
//dgrv1 is a Datagridview Control on my winform
Load_dgrv1Test();
}

private void Load_dgrv1Test()
{
//dsGeneric is a form level Dataset Object I declared in
//Form_Load along with da1 (sqlDataAdapter) and conn1
//(my connection object) and tmpX is a Table in a Sql
//Server database for testing purposes. tmpX contains
//an Identity column called rowID and 3 varchar columns
//called fld1, fld2, fld3

dsGeneric.Clear();
da1.SelectCommand.CommandText = "Select * from tmpX";
da1.Fill(dsGeneric, "tbl1");
dgrv1.DataSource = dsGeneric.Tables["tbl1"];
}

private void btnBasicUpdateStuff2_Click(...)
{
da1.UpdateCommand.CommandText = "Update tmpX Set fld1 = @f1 Where
rowID = @ID";
da1.UpdateCommand.Parameters.Add("@ID", SqlDbType.Int, 4, "rowID");
da1.UpdateCommand.Parameters.Add("@f1", SqlDbType.VarChar, 100,
"fld1");

//I arbitrarily selected the 5th row from the dataTable
//to update fld1
DataRow dr = dsGeneric.Tables["tbl1"].Rows[4];
dr.BeginEdit();
dr["fld1"] = "ab123";
dr.EndEdit();

da1.Update(dsGeneric, "tbl1");

Load_dgrv1Test(); //display the updated field in dgrv1
}

---------------------------------------------------

If you just want to update a row in the sql server table without using a
datatable in the app, just use a straight forward SelectCommand

da1.SelectCommand.CommandText = "Update tmpX Set fld1 = @f1 Where rowID
= @ID"
da1.SelectCommand.Parameters.Add(...)
...
if (conn1.State == ConnectionState.Closed) conn1.Open();
da1.SelectCommand.ExecuteNonQuery();
conn1.Close();

Rich
 
Back
Top