dataAdapter not updating - but sqlcommand works OK

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
....
Dim DA As SqlDataAdapter, DS As DataSet, curPos As Integer
Dim cmdSel, cmdIns, cmdDel As SqlCommand
....
DA = New SqlDataAdapter()
....
-----------------------this part works fine--------------------
conn1.Open()
strSqlDel = "Delete From tbl1 Where rowID = 10"
cmdDel = New SqlCommand(strSqlDel, conn1)
cmdDel.ExecuteNonQuery()
-----------------------------------------------------------------
---------but this one doesn't work - what am I missing for the
dataAdapter?----------
conn1.Open()
strSqlDel = "Delete From tbl1 Where rowID = 10"
cmdDel = New SqlCommand(strSqlDel, conn1)
DA.DeleteCommand = cmdDel
DA.Update(DS, "tbl1")
----------------------------------------------------------------------------

I look in Query analyzer and row 10 is still there when I try to delete it
with the DataAdapter. I can delete row 10 with the sqlCommand no problem.
What am I missing with the dataAdapter?

Thanks,
Rich
 
check the Rowstate - based on teh symptoms, I'm guessing your rowstate for
that row isn't what you think it is or the parameters are wrong. More than
likely it's the rowstate though. Just to be safe, turn on profiler and see
what's being sent back to the db, this is the safest way to confirm what's
going to the Server. Let me know if this all looks as you'd expect.
 
I turned on the profiler and I see a lot of activity. I confess that I don't
know how to use the information from the profiler. But the sqlCommand works
fine. I can select, update, insert, delete using the sqlCommand by itself.
But if I try to use the dataAdapter for other than just selecting a dataset,
it doesn't work. I can do

DA.Fill(DS, "someTbl")

that works. But I can't insert, update, or delete with the dataAdapter. Am
I missing a piece of code? At worst case scenario, I just won't use the
DataAdapter for inserts/updates/deletes.
 
If you can't update/delete or insert, then it's almost 100% sure it's one of
two problems
1- Your adapter has a join in the select statement and/or no primary key on
the table. This will disable commandbuilders or the configuration wizard
from generating those
2- your rowstate isn't changed. double check DataSetName.hasChanges and
make sure there's something to update - if you don't have changes there's
nothing for hte adapter to key off of to know what to update so it just
won't.
 
Rich,

In your code are you using the code to delete a row direct in the database
using the "SQLcommand".

To delete using a dataset/datatable dataadapter you have to mark the datarow
for deleting. (Therefore not Remove it, you did not however before you do) .

It can be something as
Mydatatable.rows(10).delete

If the proper delete commands and parameters for the dataadapter are than
made, you can do.

myDataAdapter(mytable)

I am busy with some samples for this, including hand made commands and
parameters for this. However I have it ready for OleDb not for SQLClient,
while I am not sure if I have that today. If I have it ready I will try to
remember it me to post the link to the sample here.

You can as well use the commandbuilder by the way.

Cor
 
Thanks for your reply. Here is what I have:

Note: I am only working with one table - tbl1 - no joins. tbl1 contains a
primary key and I am not using the wizard to generate the dataAdapter. The
select command works fine on the data adapter. It is the delete command that
is not working. I am generating the delete command in code.

------------this sub deletes rows OK - not using DataAdapter-----------
Sub DeleteRows()
Dim conn As sqlConnection, cmdDel As SqlCommand
Dim strSql As String = "Select * From tbl1"
conn = New SqlConnection
conn.ConnectionString = "Server=srv1;UID=sa;PWD=xyz;Database=db1"
cmdDel = New SqlCommand
cmdDel.Connection = conn
cmdDel.CommandType = CommandType.Text
cmdDel.CommandText = strSql
cmdDel.ExecuteNonQuery()
conn.Close()
End Sub

-------------------this sub does not delete rows - problem
Sub DeleteRows() <<<---this sub delete rows OK
Dim conn As sqlConnection, da1 As SqlDataAdapter, cmdDel As SqlCommand
Dim strSql As String = "Delete From tbl1 Where RowID = 10"
conn = New SqlConnection
conn.ConnectionString = "Server=srv1;UID=sa;PWD=xyz;Database=db1"
cmdDel = New SqlCommand
cmdDel.CommandType = CommandType.Text
cmdDel.Connection = conn
cmdDel.CommandText = strSql
dA1.DeleteCommand = cmdDel
dA1.Update(ds, "tbl1")
conn.Close()
End Sub

If RowState is teh issue, how can I check/test for RowState? Is there a way
to set RowState? I am pursuing this mostly for the learning. I believe I
must be missing some code that I would get if I generated the commands with
the wizard. But what am I missing?
 
For those who are interested in this.

It seems that Bill Ryan and Rich can let a DataAdapter use a datatable to
delete rows from a database without a DataAdapter.DeleteCommand confirm
that.

For those who cannot do that, this sample from which I wrote in my previous
message (to make it complete).

http://www.vb-tips.com/default.aspx?ID=3405596d-4556-4aa8-be12-d7c12bbb3726

They are brand new, including auto ident with direct affecting in this the
method as often told in this newsgroup by Bill Vaughn Scope_Identity().

This one beneath is almost the same for Access, here can that direct
affecting the numbers not be used and therefore is than needed a refill.

http://www.vb-tips.com/default.aspx?ID=87057b89-a61c-44b9-bbfa-f80b8e80394e

Any comment (about those samples) are welcome

Cor
 
Rich,
Have you filled the DataTable the DA is attached to with data? Also I
would think that you would just want to find the row with rowID = 10 and
delete that in the DataTable then update the DA with the normal delete
command set to make this work. For example just of the top assuming that
rowID is an Integer

DA.DeleteCommand = new SqlCommand("Delete FROM tbl1 WHERE rowID = @rid",
conn1);
DA.DeleteCommand.Parameters.Add("@rid", SqlDbType.Integer, 0, 0,
"rowID");

This should work if any row is deleted in the DataTable.

Ron Allen
 
Back
Top