DataAdapter doesn't delete

[hennovanrensburg]

Does anyone know why the following procedure would not invoke the
delete command on the dataadapter?

public int updateDataSet(string sSelectSQL, DataSet oDs)
{
SqlConnection oConn = new SqlConnection(sConnStr);
SqlDataAdapter oAdp = new SqlDataAdapter(sSelectSQL,oConn);
SqlCommandBuilder oBuilder = new SqlCommandBuilder(oAdp);
try
{
oAdp.DeleteCommand=oBuilder.GetDeleteCommand();
oAdp.UpdateCommand=oBuilder.GetUpdateCommand();
return oAdp.Update(oDs.Tables[0]);
}
catch(Exception ex)
{
throw ex;
}
finally
{
oBuilder=null;
oAdp=null;
oConn=null;
}

}


I'm passing a dataset that has been modified, since the last time I
requested it. This procedure is web service/method call.

Please help?!

 

[Christiaan van Bergen]

Hi Henno,

A quick look at your code gives me the impression that there could be a
difference between the tablename you use in sSelectSQL and the first table
in oDs.
You are calling : "return oAdp.Update(oDs.Tables[0]);"

Could you double check?

HTH
Christiaan

 

[Cor Ligthert [MVP]]

Hi,

Can you try it like this, it does not change your code, however I have
removed all the not necessary parts, which helps often to find an error
easier.

public int updateDataSet(string sSelectSQL, DataSet oDs)
{
SqlConnection oConn = new SqlConnection(sConnStr);
SqlDataAdapter oAdp = new SqlDataAdapter(sSelectSQL,oConn);
SqlCommandBuilder oBuilder = new SqlCommandBuilder(oAdp);
try
{

return oAdp.Update(oDs.Tables[0]);

}
catch(Exception ex)
{
throw ex;
}
}

I hope this helps,

Cor

 

[Sahil Malik [MVP]]

Here's the problem ---

Right before, return oAdp.Update(oDs.Tables[0]), there are no rows with
RowState.Deleted. This is probably the case considering the below is in a
webservice call - can you verify that?? It's rather easy to check,
oDS.Tables[0].Select("...one of the overloads will let you filter by
rowstates ..")

Can you check that up please?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[hennovanrensburg]

Indeed.

I added DataSet myset = oDs.GetChanges(DataRowState.Deleted); just
before dataAdapter.Update.

I checked to see whether the table name was the same, and it was.

It makes sense that the DataAdapter would not know which rows was
deleted. Working in this disconnected manner I suppose the dataset
given to the webservice doesn't include the Deleted rows attribute.

Does this make sense? Is there a way to resolve this?

Thanks!

 

[Sahil Malik [MVP]]

Okay so if I understood you correctly, the dataset as it is being sent over
the web service .. umm .. forgets about the deleted rows .. right?

The easiest way around this issue is by sending the diffgram instead of the
entire dataset.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[hennovanrensburg]

Just to give all you guys an update...

I was working on an existing project, and found that it's best to set a
Delete field in my table to true, rather than deleting the actual
record. This solved the problem

Thanks

 

[hennovanrensburg]

How would you send a diffgram to a webservice? I've never attempted
anything like this. Thanks

 

[hennovanrensburg]

I found it!!! The answer to all my problems!

While debugging i saw that the HasChange method always returns false.
even though i "removed" a row. I was calling
DataSet.Table[0].RemoveAt(i). This will not cause ADO to detect a
change in the dataset. Only if you call the delete method on the actual
row you wanna delete.

Thanks for all your help!