delete a row from 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("Test Table").Select( "emp_id='" & txtEmpID.Value & "' and
fname='" & txtFName.Value & "' and lname='" & txtLName.Value & "' ")(0)

row.Delete()

ds.AcceptChange s()

myAdapter.Updat e(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.Updat e(ds)
ds.AcceptChange s() '* no longer need modification info in dataset

-- bruce (sqlwork.com)

"Mark Kurten" <ma*********@ac ordia.com> wrote in message
news:#f******** ******@TK2MSFTN GP09.phx.gbl...

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("Test Table").Select( "emp_id='" & txtEmpID.Value & "' and
fname='" & txtFName.Value & "' and lname='" & txtLName.Value & "' ")(0)

row.Delete()

ds.AcceptChange s()

myAdapter.Updat e(ds)

myAdapter.Fill( ds, "TestTable" )

Catch err As Exception

lblError.Text = err.ToString()

End Try

[Mark Kurten]

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

Thanks.
Try

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

row.Delete()

myAdapter.Updat e(ds.Tables("Te stTable"))

ds.AcceptChange s()

Session("ds") = ds

Catch err As Exception

lblError.Text = err.ToString()

End Try

System.InvalidO perationExcepti on: Update requires a valid DeleteCommand when
passed DataRow collection with deleted rows.

"bruce barker" <no***********@ safeco.com> wrote in message
news:u2******** ******@tk2msftn gp13.phx.gbl...

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.Updat e(ds)
ds.AcceptChange s() '* no longer need modification info in dataset

-- bruce (sqlwork.com)

"Mark Kurten" <ma*********@ac ordia.com> wrote in message
news:#f******** ******@TK2MSFTN GP09.phx.gbl...

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("Test Table").Select( "emp_id='" & txtEmpID.Value & "' and
fname='" & txtFName.Value & "' and lname='" & txtLName.Value & "' ")(0)

row.Delete()

ds.AcceptChange s()

myAdapter.Updat e(ds)

myAdapter.Fill( ds, "TestTable" )

Catch err As Exception

lblError.Text = err.ToString()

End Try

[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.Delete Command to this command.

dim delCmd as SqlCommand = new SqlCommand("Del ete From myTable where
empid=@emp_id", myConn)
sqlCommand.para meters.add(@emp _id, txtEmpID.Value)

myAdapter.Delet eCommand = delCmd

"Mark Kurten" <ma*********@ac ordia.com> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..

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

Thanks.
Try

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

row.Delete()

myAdapter.Updat e(ds.Tables("Te stTable"))

ds.AcceptChange s()

Session("ds") = ds

Catch err As Exception

lblError.Text = err.ToString()

End Try

System.InvalidO perationExcepti on: Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.

"bruce barker" <no***********@ safeco.com> wrote in message
news:u2******** ******@tk2msftn gp13.phx.gbl...

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.Updat e(ds)
ds.AcceptChange s() '* no longer need modification info in dataset

-- bruce (sqlwork.com)

"Mark Kurten" <ma*********@ac ordia.com> wrote in message
news:#f******** ******@TK2MSFTN GP09.phx.gbl...

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("Test Table").Select( "emp_id='" & txtEmpID.Value & "' and fname='" & txtFName.Value & "' and lname='" & txtLName.Value & "' ")(0)
row.Delete()

ds.AcceptChange s()

myAdapter.Updat e(ds)

myAdapter.Fill( ds, "TestTable" )

Catch err As Exception

lblError.Text = err.ToString()

End Try

[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.
"Michael Ramey" <raterus@localh ost> wrote in message
news:uk******** ******@TK2MSFTN GP11.phx.gbl...

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.Delete Command to this command.

dim delCmd as SqlCommand = new SqlCommand("Del ete From myTable where
empid=@emp_id", myConn)
sqlCommand.para meters.add(@emp _id, txtEmpID.Value)

myAdapter.Delet eCommand = delCmd

"Mark Kurten" <ma*********@ac ordia.com> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..

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

Thanks.
Try

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

row.Delete()

myAdapter.Updat e(ds.Tables("Te stTable"))

ds.AcceptChange s()

Session("ds") = ds

Catch err As Exception

lblError.Text = err.ToString()

End Try

System.InvalidO perationExcepti on: Update requires a valid DeleteCommand

when

passed DataRow collection with deleted rows.

"bruce barker" <no***********@ safeco.com> wrote in message
news:u2******** ******@tk2msftn gp13.phx.gbl...

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.Updat e(ds)
ds.AcceptChange s() '* no longer need modification info in dataset

-- bruce (sqlwork.com)

"Mark Kurten" <ma*********@ac ordia.com> wrote in message
news:#f******** ******@TK2MSFTN GP09.phx.gbl...
> 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("Test Table").Select( "emp_id='" & txtEmpID.Value & "'

and > fname='" & txtFName.Value & "' and lname='" & txtLName.Value & "' ")(0) >
> row.Delete()
>
> ds.AcceptChange s()
>
> myAdapter.Updat e(ds)
>
> myAdapter.Fill( ds, "TestTable" )
>
> Catch err As Exception
>
> lblError.Text = err.ToString()
>
> End Try
>
>

[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.Accept Changes Method
http://msdn.microsoft.com/library/en...datadatasetcla
ssacceptchanges topic.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.UpdateCom mand = New SqlCommand("UPD ATE Categories SET CategoryName =
@CategoryName " & _
"WHERE CategoryID = @CategoryID",
nwindConn)
catDA.UpdateCom mand.Parameters .Add("@Category Name", SqlDbType.NVarC har, 15,
"CategoryNa me")

is to specify a certain sql statment for the DataAdaptor's proper
command(Insert, query,delete,up date) 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
"Automatica lly Generated Commands":
#Automatically Generated Commands
http://msdn.microsoft.com/library/en...maticallygener
atedcommands.as p?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...tingDatabaseWi
thDataAdapterDa taSet.asp?frame =true

#Introduction to Dataset Updates
http://msdn.microsoft.com/library/en...uctiontodatase
tupdates.asp?fr ame=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.)