SqlDataAdapter Help

[DaveL]

Hello All,
I have a DataTable (dtTemp) and a Processing DataTable dtActive
I need to take the Identity key fileds and 2 other fields from dtactive and
move to dtTemp
Because dtActive is a Joined Datatable and i can't use sqldataAdapter to
Update the
Records for batchId and BatchDate back at the physical Table

What i want to do is Add rows to dtTemp from dtActive , Then use
SqlDataAdapter to
Update the Physical Table

dtTemp Holds 0 Rows -> select top 0 Idkey,BatchId,BatchDate from
ActiveTable (nolock)
I did this when i Retrieved the Join'd Table dtActive (returnd 2 Row Sets)

While Processing dtActive , add Rows to Update back at the server

example
for (int x=0;x<dtActive.Rows.Count;x++)
{
//do some processing
this.MoveFiles(dtActive.rows[x]);
//add the idkey and batchid and batchdate to the tmptable
DataRow row = dtTemp.NewRow()
dtTemp["Idkey"]=dtActive.Rows[x]["Idkey"];
dtTemp["BatchId"]=this.BatchId;
dtTemp["BatchDate"]=DateTime.Now;
}
dtTemp.AcceptChanges();

//when we get to here we are ready to update the ActiveTable (physical
table) with batch ids

sqlDataAdapter ad = new SqlDataAdapter()

ad.UpdateCommand= new SqlCommand()
ad.Updatecommand.CommandText="Update ActiveTable Set
BatchId=@Batchid,BatchDate=@BatchDate where Idkey=@IdKey"
ad.UpdateCommand.CommandType=CommandType.Text;
ad.UpdateCommand.Connection= oConn;

ad..UpdateCommand.Parameters.Add("@BatchId", SqlDbType.Int);
ad..UpdateCommand.Parameters["@BatchId"].SourceColumn = "BatchId";

ad..UpdateCommand.Parameters.Add("@BatchDate", SqlDbType.DateTime);
ad..UpdateCommand.Parameters["@BatchDate"].SourceColumn = "BatchDate";

ad..UpdateCommand.Parameters.Add("@Idkey", SqlDbType.Int);
ad..UpdateCommand.Parameters["@Idkey"].SourceColumn = "Idkey";

//set up the insertcommand Adapter needs this

ad.Insertcommand = new SqlCommand()
ad.InsertCommand.CommandText = "Insert into ActiveTable (BatchId,BatchDate)
values (@BatchId,@BatchDate)"
ad.InsertCommand.CommandType=CommandType.Text;
ad.InsertCommand.Connection = oConn;
ad.InsertCommand.Parameters.Add("@BatchId", SqlDbType.Int);
ad.InsertCommand.Parameters["@BatchId"].SourceColumn = "BatchId";

ad.InsertCommand.Parameters.Add("@BatchDate", SqlDbType.DateTime);
ad.InsertCommand.Parameters["@BatchDate"].SourceColumn = "BatchDate";

oConn.Open()
ad.Update(dtTemp)
oConn.Close()

this Runs perfect ..but no Rows are Updated , If i Remark the AcceptChanges
above the insert works fine...
Can Sombody help me with this code....
I dont know why the UpdatePart is not working thanks
I did not Use SqlCommandBuilder was Unable to get that to work also
so i hand coded it till i can get it working and have full understanding
what i am doing wrong


Dave L

 

[DaveL]

Fouind it...
after AcceptChanges()
needed to SetRowModified()

DaveL

Hello All,
I have a DataTable (dtTemp) and a Processing DataTable dtActive
I need to take the Identity key fileds and 2 other fields from dtactive
and move to dtTemp
Because dtActive is a Joined Datatable and i can't use sqldataAdapter to
Update the
Records for batchId and BatchDate back at the physical Table

What i want to do is Add rows to dtTemp from dtActive , Then use
SqlDataAdapter to
Update the Physical Table

dtTemp Holds 0 Rows -> select top 0 Idkey,BatchId,BatchDate from
ActiveTable (nolock)
I did this when i Retrieved the Join'd Table dtActive (returnd 2 Row
Sets)

While Processing dtActive , add Rows to Update back at the server

example
for (int x=0;x<dtActive.Rows.Count;x++)
{
//do some processing
this.MoveFiles(dtActive.rows[x]);
//add the idkey and batchid and batchdate to the tmptable
DataRow row = dtTemp.NewRow()
dtTemp["Idkey"]=dtActive.Rows[x]["Idkey"];
dtTemp["BatchId"]=this.BatchId;
dtTemp["BatchDate"]=DateTime.Now;
}
dtTemp.AcceptChanges();

//when we get to here we are ready to update the ActiveTable (physical
table) with batch ids

sqlDataAdapter ad = new SqlDataAdapter()

ad.UpdateCommand= new SqlCommand()
ad.Updatecommand.CommandText="Update ActiveTable Set
BatchId=@Batchid,BatchDate=@BatchDate where Idkey=@IdKey"
ad.UpdateCommand.CommandType=CommandType.Text;
ad.UpdateCommand.Connection= oConn;

ad..UpdateCommand.Parameters.Add("@BatchId", SqlDbType.Int);
ad..UpdateCommand.Parameters["@BatchId"].SourceColumn = "BatchId";

ad..UpdateCommand.Parameters.Add("@BatchDate", SqlDbType.DateTime);
ad..UpdateCommand.Parameters["@BatchDate"].SourceColumn = "BatchDate";

ad..UpdateCommand.Parameters.Add("@Idkey", SqlDbType.Int);
ad..UpdateCommand.Parameters["@Idkey"].SourceColumn = "Idkey";

//set up the insertcommand Adapter needs this

ad.Insertcommand = new SqlCommand()
ad.InsertCommand.CommandText = "Insert into ActiveTable
(BatchId,BatchDate) values (@BatchId,@BatchDate)"
ad.InsertCommand.CommandType=CommandType.Text;
ad.InsertCommand.Connection = oConn;
ad.InsertCommand.Parameters.Add("@BatchId", SqlDbType.Int);
ad.InsertCommand.Parameters["@BatchId"].SourceColumn = "BatchId";

ad.InsertCommand.Parameters.Add("@BatchDate", SqlDbType.DateTime);
ad.InsertCommand.Parameters["@BatchDate"].SourceColumn = "BatchDate";

oConn.Open()
ad.Update(dtTemp)
oConn.Close()

this Runs perfect ..but no Rows are Updated , If i Remark the
AcceptChanges above the insert works fine...
Can Sombody help me with this code....
I dont know why the UpdatePart is not working thanks
I did not Use SqlCommandBuilder was Unable to get that to work also
so i hand coded it till i can get it working and have full understanding
what i am doing wrong


Dave L