|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
dataAdapter not updating - but sqlcommand works OK.... 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. Show quote "Rich" <R***@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 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. Show quote "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" <R***@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 > > > 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. Show quote "Rich" <R***@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" <R***@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 >> >> >> 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? Show quote "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" <R***@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" <R***@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 > >> > >> > >> > > > 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 Show quote "Rich" <R***@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" <R***@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" <R***@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 >> >> >> >> >> >> >> >> >> 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 Show quote "Rich" <R***@discussions.microsoft.com> schreef in bericht 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 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 Show quote "Rich" <R***@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  |
|||||||||||||||||||||||
Other interesting topics