[2005] How to delete button from database-using listview

[twinkie]

how to delete the records from the database? this are my codes: can help me?? urgent reply!!!!

Public Class DeleteFormf

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strcon As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\temp\db1.mdb"
Dim con As OleDb.OleDbConnection
Dim dr As OleDb.OleDbDataReader
Dim cmd As New OleDb.OleDbCommand
Try

Dim strselect As String = "Delete from item where DateDeleted ='" & Format(DateTimePicker1.Text) & "'"

'create a new connection
con = New OleDb.OleDbConnection(strcon)
con.Open()
cmd.Connection = con
cmd.CommandText = strselect
cmd.ExecuteNonQuery()
con.Close()

'While dr.Read Then
'TextBox2.Text = dr("ItemId")
'TextBox1.Text = dr("ReferenceNo")
'cbxtype.Text = dr("ItemCategory")
'DateTimePicker1.Text = dr("DateDeleted")
'End While
'End If
'dr.Close()

Catch ex As Exception
MessageBox.Show("The record is deleted")
End Try

' Dim alvw As ListView.SelectedIndexCollection = lvfine.SelectedIndices

' For Each lvw As Integer In alvw
'lvfine.Items.RemoveAt(lvw) ' Remove them from the list view.
' Next

For i As Integer = Me.lvfine.SelectedItems.Count - 1 To 0 Step -1
Me.lvfine.Items.Remove(Me.lvfine.SelectedItems(i))
Next i

For i As Integer = Me.lvfine.SelectedIndices.Count - 1 To 0 Step -1
Me.lvfine.Items.RemoveAt(Me.lvfine.SelectedIndices(i))
Next i

While Me.lvfine.SelectedItems.Count > 0
Me.lvfine.Items.Remove(Me.lvfine.SelectedItems(0))
End While

' Try
'Dim strselect As String = "Insert into item(DateDeleted) values('" & Format(DateTimePicker1.Value) & "')"

'create a new connection
'con = New OleDb.OleDbConnection(strcon)
'con.Open()
'cmd.Connection = con
'cmd.CommandText = strselect
'dr = cmd.ExecuteReader

'If dr.Read Then
'TextBox2.Text = dr("ItemId")
'TextBox1.Text = dr("ReferenceNo")
'cbxtype.Text = dr("ItemCategory")
' DateTimePicker1.Text = dr("DateDeleted")

'End If
'dr.Close()

'Catch ex As Exception
' MessageBox.Show("The record is deleted")
' End Try

End Sub

Private Sub DeleteFormf_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'Db1DataSet.item' table. You can move, or remove it, as needed.
' Me.ItemTableAdapter.Fill(Me.Db1DataSet.item)
Dim strcon As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\temp\db1.mdb"
Dim con As OleDb.OleDbConnection
Dim dr As OleDb.OleDbDataReader
Dim cmd As New OleDb.OleDbCommand
Try
Dim strselect As String = "Select * from item where DateDeleted= '" & Format(DateTimePicker1.Text) & "'"

'create a new connection
con = New OleDb.OleDbConnection(strcon)
con.Open()
cmd.Connection = con
cmd.CommandText = strselect
dr = cmd.ExecuteReader

If dr.Read Then
'TextBox2.Text = dr("ItemId")
'TextBox1.Text = dr("ReferenceNo")
cbxtype.Text = dr("ItemCategory")
DateTimePicker1.Text = dr("DateDeleted")

End If
dr.Close()


Catch eException As Exception
MessageBox.Show(eException.Message)
End Try
' If cbxtype.Text = "Overdue" Then
lvfine.View = View.Details 'view stuff in listview
lvfine.Columns.Add("ISBN", 60, HorizontalAlignment.Left)
lvfine.Columns.Add("Title", 80, HorizontalAlignment.Left)
lvfine.Columns.Add("Category", 100, HorizontalAlignment.Left)
lvfine.Columns.Add("Classification", 70, HorizontalAlignment.Left)
lvfine.Columns.Add("Status", 80, HorizontalAlignment.Left)
lvfine.Columns.Add("Author", 80, HorizontalAlignment.Left)
lvfine.Columns.Add("DatePublished", 70, HorizontalAlignment.Left)
lvfine.Columns.Add("DateCreated", 80, HorizontalAlignment.Left)
lvfine.Columns.Add("Cost", 80, HorizontalAlignment.Left)
lvfine.Columns.Add("PublisherName", 70, HorizontalAlignment.Left)
lvfine.Columns.Add("ReferenceNo", 80, HorizontalAlignment.Left)
lvfine.Columns.Add("Edition", 80, HorizontalAlignment.Left)

End Sub

Private Sub cbxtype_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxtype.SelectedIndexChanged
Dim strcon As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\temp\db1.mdb"
Dim con As OleDb.OleDbConnection
Dim dr As OleDb.OleDbDataReader
Dim cmd As New OleDb.OleDbCommand

Try



Dim strselect As String = "Select * from item where ItemCategory = '" & cbxtype.Text & "'"

'create a new connection
con = New OleDb.OleDbConnection(strcon)
con.Open()
cmd.Connection = con
cmd.CommandText = strselect
dr = cmd.ExecuteReader

lvfine.Items.Clear()
While dr.Read()

Dim Item As New ListViewItem

Item.Text = (dr("ItemId"))

'Item.SubItems.Add(dr(""))
Item.SubItems.Add(dr("ItemTitle"))
Item.SubItems.Add(dr("ItemCategory"))
Item.SubItems.Add(dr("ItemClassification"))
Item.SubItems.Add(dr("Status"))
Item.SubItems.Add(dr("AuthorName"))
Item.SubItems.Add(dr("DateOfPublished"))
Item.SubItems.Add(dr("DateCreated"))
Item.SubItems.Add(dr("CostPriceOfItem"))
Item.SubItems.Add(dr("PublisherName"))
Item.SubItems.Add(dr("ReferenceNo"))
Item.SubItems.Add(dr("Edition"))


lvfine.Items.Add(Item)


End While
dr.Close()


' If cbxtype.Text = "Lost" Then
'lost.ShowDialog()
' End If

Catch eException As Exception
MessageBox.Show(eException.Message)
End Try
' If Not lvfine.SelectedItems.Count = 0 Then tbxdays.Text = lvfine.SelectedItems(0).SubItems(3).Text





End Sub

Private Sub lvfine_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lvfine.SelectedIndexChanged

End Sub
End Class

[jmcilhinney]

This is now your fifth thread on deleting records from a database based on the contents of a ListView. It is considered poor form to create more than one thread for the same question. I believe that this has been mentioned to you before... more than once. If you don't get an answer to your question, that is not a valid reason to create another thread. You should consider why you didn't get an answer. Perhaps you need to reword the question. Perhaps you need to read a tutorial or two. Regardless of the reason, if you want to get people's attention then add a post to your existing thread rather than starting a new thread. I'm sure an intelligent person like yourself will be able to understand this clear information and will refrain from creating duplicate threads in the future. Thank you.

[jmcilhinney]

Further, if you want help with deleting then why have you posted all that code for selecting and inserting? Posting more code than is relevant, plus not using VBCODE tags to make your code more readable means that people are less likely to help because it takes more effort to do so. Also, you have code there. Presumably you've executed it and it doesn't work. What happens? Do you get an error message? Make it easier for usto help by supplying all the relevant information and no irrelevant information and you're more likely to get a helpful response.

For the reasons mentioned above I'm not going to read your code in detail, but if you want to delete a record by date that has been entered in a DateTimePicker you should do it like this:

Dim myConnection As New OleDbConnection("connection string here")
Dim myCommand As New OleDbCommand("DELETE FROM MyTable WHERE [Date] = @Date", myConnection)
 
myCommand.Parameters.AddWithValue("@Date", myDateTimePicker.Value.Date)
 
Try
    myConnection.Open()
    myCommand.ExecuteNonQuery()
    MessageBox.Show("Record deleted.")
Catch ex As Exception
    MessageBox.Show(ex.ToString())
Finally
    myConnection.Close()
End Try