Hi Mark,
Thanks for posting in the community!
From your description, you'd like to delete a datarow from a certain
DataSet and then use DataAdapter to update it so as to modify the data in
the database server, yes?
If there is anything I misunderstood, please feel free to let me know.
As for this question, I agree to bruce barker's suggestion that you need to
update DataSet before calling the AcceptChanges method. Because when you
call the DataAdaptor's update mehod to update a DataSet, the adpator will
lookup the DAtaset's record(datarow)'s state, if the state is modified , it
will update the value into the actual database, if state is deleted , it'll
delete the acutal row in database and the AcceptChanges method will clear
all the state flags to "Unchanged" and set the row's value to original
value, that's why you found that your dataset didn't actually update to the
actual database. For more detailed info on the AcceptChanges method, you
may refer to the follwing reference in MSDN:
#DataSet.AcceptChanges Method
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatadatasetcla
ssacceptchangestopic.asp?frame=true
In addtion, as for the later question:
"It looks as though it's deleting data directly from the sql server table."
This is because when the DataAdapter udpate a DataSet, it'll loop through
all the datarows in it's DataTAble and looked up each row's state, if state
is modified or deleted or added, it'll execute the proper sqlstatement to
do data manipulation in the actual database. So the code such as
Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT CategoryID,
CategoryName FROM Categories", nwindConn)
catDA.UpdateCommand = New SqlCommand("UPDATE Categories SET CategoryName =
@CategoryName " & _
"WHERE CategoryID = @CategoryID",
nwindConn)
catDA.UpdateCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15,
"CategoryName")
is to specify a certain sql statment for the DataAdaptor's proper
command(Insert,query,delete,update) so that the DataAdapter is able to do
db maniuplation in the actual database. Also, in some cases, we can use
the CommandBuilder to automatically generate sql statements for a
DataAdapter if the actual database stucture is not very complex. You can
view the following reference in MSDN for more detailed description on
"Automatically Generated Commands":
#Automatically Generated Commands
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconautomaticallygener
atedcommands.asp?frame=true
Also, here is some other tech articles in MSDN on updating database with
DataAdapter and DataSet, I believe they'll also be helpful to you:
#Updating the Database with a DataAdapter and the DataSet
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconUpdatingDatabaseWi
thDataAdapterDataSet.asp?frame=true
#Introduction to Dataset Updates
http://msdn.microsoft.com/library/en-us/vbcon/html/vbconintroductiontodatase
tupdates.asp?frame=true
Please check out the preceding suggestions. If you have any questions,
please feel free to post here.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure!
www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)