delete a row from table

[mark kurten]

I'm getting confused because the code below I have seen in other examples,
but I'm trying to delete records out of a dataset and then do an update..The
code below isn't deleting records out of a dataset, correct? It looks as
though it's deleting data directly from the sql server table.

 

[Mark Kurten]

for some reason when i delete a row, the row doesn't get deleted from the
acutal data table in SQL server. my code follows
what am i missing?

thanks.

Dim row As DataRow

Try

row = ds.Tables("TestTable").Select("emp_id='" & txtEmpID.Value & "' and
fname='" & txtFName.Value & "' and lname='" & txtLName.Value & "' ")(0)

row.Delete()

ds.AcceptChanges()

myAdapter.Update(ds)

myAdapter.Fill(ds, "TestTable")

Catch err As Exception

lblError.Text = err.ToString()

End Try

 

[bruce barker]

AcceptChanges() is used on a dataset to actually delete deleted rows and
clear status flags. As you call this before calling Update on the adapter,
the Update finds nothing to do.

try:

row.Delete()
myAdapter.Update(ds)
ds.AcceptChanges() '* no longer need modification info in dataset

-- bruce (sqlwork.com)

 

[Mark Kurten]

Here is my code: I'm receiving the following error.

Thanks.
Try

row = ds.Tables("TestTable").Select("emp_id=" & txtEmpID.Value)(0)

row.Delete()

myAdapter.Update(ds.Tables("TestTable"))

ds.AcceptChanges()

Session("ds") = ds

Catch err As Exception

lblError.Text = err.ToString()

End Try



System.InvalidOperationException: Update requires a valid DeleteCommand when
passed DataRow collection with deleted rows.

 

[Michael Ramey]

Your adapter has no clue how to delete data out of your table unless you
tell it. You have to create a command object to specify the delete sql you
are going to use when a deletion occurs. Then set your
adapters.DeleteCommand to this command.

dim delCmd as SqlCommand = new SqlCommand("Delete From myTable where
empid=@emp_id", myConn)
sqlCommand.parameters.add(@emp_id, txtEmpID.Value)

myAdapter.DeleteCommand = delCmd

 

[Steven Cheng[MSFT]]

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.)

 

[Steven Cheng[MSFT]]

Hi Mark,


Have you had a chance to check out my suggestion or have you got any ideas
on this issue? If you need any further assistance, please feel free to let
me know.



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.)