W
Sat Feb 04 08:04:56 CST 2006
In the 2.0 framework you can set rowstate
http://msmvps.com/blogs/williamryan/archive/2006/01/14/81063.aspx.
You can just step through in the debugger and check the RowState, it's a
property of the datarow. But from this code, I'm positive this is the
problem (or very sure) . It doesnt' look like the rows are getting delted
locally from the table anywhere
"Rich" <Rich@discussions.microsoft.com> wrote in message
news:52A947DB-4953-4D0E-9BB4-4DBE0078A0B6@microsoft.com...
> 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?
>
> "W.G. Ryan - MVP" wrote:
>
>> 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" <Rich@discussions.microsoft.com> wrote in message
>> news:F563E457-10AA-4E71-A8A9-8D761FB76955@microsoft.com...
>> >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.
>> >
>> > "W.G. Ryan - MVP" wrote:
>> >
>> >> 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.
>> >> "Rich" <Rich@discussions.microsoft.com> wrote in message
>> >> news:CEC4E5BF-625A-496D-8BFC-3EE886AE5972@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>