[2008] [ACCESS] How would I delete a certian row when ...

I think I'm deleting my Rows in a Complete Terrible Way. First I get the INDEX of the Selected Item (List Box, or COMBO then I usally add 1 to it to even it out to the Auto Increment that my Database Column Has. But See the Problem is, Say I have 5 Items

2
5
1
6
4

I want to Delete 1 and 6

That would be Index 3 and 4 & Auto Increment 3 and 4

But when I go and try and Delete 4 Now, it their is No AutoIncrement 3 so it just debuggs

Heres my Code: what would be a much better way of doing it? I was told to use a Value that would never be changed (CIndex [AutoIncrement Value]) but now how would I get that value :P

vb Code:

Public Sub DeleteEvent()
        With frmEvents
            Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseName)
            Connection.Open()
            Adapter.DeleteCommand = New OleDbCommand("DELETE FROM Events WHERE CIndex = @CIndex")
            Adapter.DeleteCommand.Connection = Connection
            Adapter.DeleteCommand.Parameters.AddWithValue("@CIndex", .cmbEditEvents.SelectedIndex)
            Adapter.DeleteCommand.ExecuteNonQuery()
            frmOverview.lstEvents.Items.RemoveAt(.cmbEditEvents.SelectedIndex)
            .cmbEditEvents.Items.RemoveAt(.cmbEditEvents.SelectedIndex)
            frmOverview.rtbEvents.Text = vbNullString
            Connection.Close()
            RemoveTextFromEvent()
        End With
    End Sub

Hey

Assuming that you are using a listbox, when you populate it with the information with the database, how about setting the listbox's tag property to the autoincrementing index from your database. That way, when it comes to deleting the row, all you have to do is to inspect the tag property of the item in question, and use this in your deletecommand.

Does that make sense?

Gary

Well what I was going to do, is get the COUNT of the Items in the Listbox

So say:

A
B
C
DEF
Testing
AHFHAFHAFS

Then I set CIndex to each Item in the ListBox

I would have to do a
For Each Count As Integer In lstEvents.Items.Count
Dim DRow as DataRow = DS.Tables(0).Rows(Count)
...
Next

Would this work? For say Rewriting the CIndex Column to Always be in Order?

So
1
2
3
instead of (if I deleted 2)
1
3

Hey,

I am not sure that I follow your logic.

How are your binding the Listbox to the information from the database? You should be able to set the Tag, of the items to the CIndex value, and the Text to the actual value. Then when it comes to deleting items you can use the Tag value for performing the delete.

Gary

What is this TAG? is like a INDEX or something? Or the Text of the Selected Item ?

at the moment, when I got CIndex Created its a Auto Increment NUMBER, which then increases every item I add but doesn't even out like:(1,3,4,6) when it should do something like this (1,2,3,4).

The tag that I have referring to is the tag property of the listbox items that you are adding. It is just another property that you can populate with information, but it doesn't show on the form.

The CIndex is probably working exactly as it is meant to. If of instance you add three rows and you get 1,2 and 3. If you then delete row 3, the next row that will be entered will have an index of 4, simply because 3 has already been used. It doesn't care that you have deleted 3, it will just keep incrementing that number.

Gary

BUt what exactly is TAG? I don't understand what the .TAG Feature does.

Maybe a code sample will help:

Code:

---

        Dim item As New ListViewItem("First Item")
        item.Tag = "index value from database"
        ListBox1.Items.Add(item)

---

.Tag is a property of an item in the ListBox, just like the Text Property, or the BackColor, or anything else. You can set it equal to anything that you like. In this case, I am suggesting that you set it equal to the CIndex value from your database.

That make sense?

Gary

So if I get this correctly:

I do a loop for my Rows in CIndex in the Access Database File and then I set Each Different Item's TAG in the ListBox to the CIndex?

Well, it comes back to the question that I asked you earlier. How are you populating your listbox? You shouldn't need to loop through your items again, you should be able to do it when you are initially creating them.

What I'm doing is every time the person creates a new course - it will create a new .MDB File with that course name - then inside the file it has the Table

information

inside it has - CIndex CourseCode RoomNumber Teacher Year

How I'm reading it is fairly simple, just reading the File Names and removing the Extension to it

Ok, so now I am even more confused?!?

Have you not created a connection to the database? Have you created a select query to retrieve the entries from your table?

Also, why are you creating an MDB file for each course? Why not have a table in one MDB which contains a list of courses? It seems a bit excessive to generate a whole new MDB file each time.

Just a thought though.

Gary