[2005] deleting record from the database. help!!!!!

[twinkie]

i have highlighted the selected row that needs to be deleted. the problem is after i deleted the selected row, i want it to be updated inside the database itself. like for e.g, i'm deleting this row : Nur, 13/05/2987, Harkiki. after i click the delete button, that particular row is deleted. den when i click on the update button, i want the record (without this row: Nur, 13/05/2987, Harkiki) to be inside the database. what r the codes ? could someone help me with the codes???

[jamesclarke112]

i have highlighted the selected row that needs to be deleted. the problem is after i deleted the selected row, i want it to be updated inside the database itself. like for e.g, i'm deleting this row : Nur, 13/05/2987, Harkiki. after i click the delete button, that particular row is deleted. den when i click on the update button, i want the record (without this row: Nur, 13/05/2987, Harkiki) to be inside the database. what r the codes ? could someone help me with the codes???

Maybe I am missing something here, but when you delete the row that is updating the database. It's not 2 seperate processes. The sql command would be something like

DELETE FROM table_name WHERE column_name = some_value

[twinkie]

shud i put ur codes inside the delete button or update button??

[twinkie]

my program still not working when using ur codes

[jamesclarke112]

You need to create a connection to your database e.g. I'm using Sql Server CE at the moment so this is

dim connectionString as string = "Data Source=test.sdf"
dim sqlConnection as SqlCeConnection = new SqlCeConnection
With sqlConnection
[INDENT].ConnectionString = strConn[/INDENT]
[INDENT].Open()[/INDENT]
End With

then create a command to delete the record

dim sqlCommand as SqlCeCommand
sqlCommand.connection = sqlConnection
sqlCommand.CommandText = "DELETE FROM table_name WHERE column_name = some_value"
sqlCommand.ExecuteNonQuery()

[twinkie]

i have created the connection to the database
den after that, i m stuck with that

[jamesclarke112]

Why don't you post the code (just the relevant bits please).

[twinkie]

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

' 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
End Sub


these codes r put in the delete button

[jamesclarke112]

You ar using ExecuteReader() when you need to use ExecuteNonQuery(). I'm not 100% sure but when you delete from the database there won't be anything returned back to read.

[twinkie]

it still not working

[twinkie]

if i never put ExcuteReader, then i cannot declare this : Dim dr As OleDb.OleDbDataReader.

[jamesclarke112]

Why do you want a DataReader if you are not returning any rows. DataReaders are used in SELECT satements.

if i never put ExcuteReader, then i cannot declare this : Dim dr As OleDb.OleDbDataReader.

That's not true. Just because you don't use the datareader doesn't mean you can't declare it. But what's the point if you are not going to use it.

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 cmd As New OleDb.OleDbCommand
Try
 
Dim strselect As String = "Delete from item where DateDeleted ='" & Format(DateTimePicker1.Value) & "'"
 
'create a new connection
con = New OleDb.OleDbConnection(strcon)
con.Open()
cmd.Connection = con
cmd.CommandText = strselect
cmd.ExecuteNonQuery()
con.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
End Sub

With regards to you displaying a message box saying the record is deleted when you catch the exception, this is probably in the worng place. You should also catch other exception like SqlException.

[jmcilhinney]

if i never put ExcuteReader, then i cannot declare this : Dim dr As OleDb.OleDbDataReader.

That's exactly the point. If you're deleting a record then you aren't going to be reading anything, so there's no need for a DataReader. I've recommended to you before that you should read some tutorials but it seems that you've chosen to ignore that advice.

[babyekc]

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.Value) & "'"
 
            'create a new connection 
            con = New OleDb.OleDbConnection(strcon)
            con.Open()
            cmd.Connection = con
            cmd.CommandText = strselect
            cmd.ExecuteNonQuery()
 
            '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
 
        ' 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
End Sub

these codes r put in the delete button

try this out.

[twinkie]

but it still not working. the record can only be deleted in the listview. y?

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:\manju\ooad-project\lina\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.ExecuteNonREADER()

con = New OleDb.OleDbConnection(strcon)
con.Open()
cmd.Connection = con
cmd.CommandText = strselect
cmd.ExecuteNonQuery()
con.Close()

' 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:\manju\ooad-project\lina\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:\manju\ooad-project\lina\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

[twinkie]

that codes are also in the delete form. other than the delete button.