Prev: MDI Parent - Timer
Next: InvalidOverlappedToPinvoke was detected
From: Deep on 7 Jun 2010 10:36
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=(a)name, age=(a)age,
salary=(a)salary, gender=(a)gender where empid=(a)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
From: Rich P on 7 Jun 2010 11:59
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

*** Sent via Developersdex http://www.developersdex.com ***
From: Deep on 7 Jun 2010 12:53
Many many thanks my problem is solved.

On Jun 7, 8:59 pm, Rich P <rpng...(a)aol.com> wrote:
> 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
>
> *** Sent via Developersdexhttp://www.developersdex.com***

 | 
Pages: 1
Prev: MDI Parent - Timer
Next: InvalidOverlappedToPinvoke was detected