deleting specific record in VB .NET

[Chunchie1]

I have a table of many records with "month" as one field.
i want to delete all records with Month field value for example "january". how can i do that? please give me a solution for a dataset. i have alread defined data adapter(oledbdataadpater1) and dataset (dataset11).

thank you.

 

[rjoubert]

Dim objDataTable As DataTable
Dim objDataRows As DataRow()
Dim objDataRow As DataRow

objDataTable = dataset11.Tables("YourTableName")
objDataRows = objDataTable.Select("Month='january'")

For each objDataRow in objDataRows
   objDataRow.Delete()
Next

DataAdapter.Update(dataset11)

Good Luck!

 

[Chunchie1]

Thanks much, rjoubert.

I tried the code you gave me and i am getting the following error message:
------
An unhandled exception of type 'System.InvalidOperationException' occurred in PayerMan.exe

Additional information: Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.
-----
i included the code you gave me in an exception handling (Try...Catch...End try). Of course i have renamed to my objects accordingly.

what do u think is the problem?

 

[rjoubert]

You need to have a DeleteCommand setup for your data adapter. Visual Studio usually creates those commands for you when you pull in your Database.

 

[Chunchie1]

I'm sorry to nag you but this is a bit advanced for me. could you please go on telling me about the DeleteCommand setup.

thanks.

 

[rjoubert]

Here's an article that shows you how to setup your database interaction...

http://www.akadia.com/services/dotnet_update_form.html

 

[Chunchie1]

that was very helpfull. thanks much. i will study more on that.

 

[Chunchie1]

Hello rjoubert

I still could find what to do with this error. below is my peice of code for the deletion of specific record. this code tries to delete all rows with 'Month' field value of January. Please help:
-----------------------------
Try
If Not (Me.cmbMonth.Text = "") Then
Dim msg As String
Dim title As String
Dim style As MsgBoxStyle
Dim response As MsgBoxResult
PayMonth = Me.cmbMonth.Text
msg = "This will delete any record for this month and create new data. Do you want to continue?" ' Define message.
style = MsgBoxStyle.DefaultButton2 Or _
MsgBoxStyle.Critical Or MsgBoxStyle.YesNo
title = "Payroll Manager" ' Define title.
' Display message.
response = MsgBox(msg, style, title)
If response = MsgBoxResult.Yes Then ' User chose Yes.
' Perform some action.
Dim objDataTable As DataTable
Dim objDataRows As DataRow()
Dim objDataRow As DataRow

objDataTable = EmpPayroll1.Tables("dbo_Payroll")
objDataRows = objDataTable.Select("Month='January'")

For Each objDataRow In objDataRows
objDataRow.Delete()
Next


Me.OleDbDataAdapter2.Update(EmpPayroll1, "dbo_Payroll")



Else
' Perform some other action.
End If

Else
MsgBox("Please choose a month for Payroll Data creation", MsgBoxStyle.Exclamation, "Payroll Manager")
Exit Sub
End If
Catch ex As Exception
Throw ex
End Try
----------------
what is wrong with this? or what should i do?

thanks

 

[fawkes]

That looks fine to me.

What is the command text for the delete command for OleDbDataAdapter2?

If there isn't a delete command it won't work as rjoubert said.

If you're using visual studio 2005 then take a look at the EmpPayroll dataset in the designer, select OleDbDataAdapter2 and configure it to produce a delete command automatically.

 

[Chunchie1]

Thanks Fawks...after going here and there ...thinking much i found the error is the delete and update command were not working properly in the configuration of OleDbDataAdapter2....that was b/c the table i'm updating doesnot have a primary key....so i created one for it...and it worked.