Remove Selected item from database when removed from listview(Resolved -Thanks!!!!! )

Hi All

Need a little help with this one, I am trying to write code to remove a selected item from the database when the user selects an item in the list view and here is what I have so far

Dim adoRemove As Recordset

Dim vacancyid As String
Dim code As String
vacancyid = Form7.txtFields(0).Text
code = ListView1.List(ListView1.ListIndex)

Adoremove.open = "select * from tblrequired requirements where lngvacancyid = " & vacancyid And bytcode = code ,db, , adOpenStatic, adLockOptimistic

.Delete
.Close

I have two columns in the listview and I want to the sql statement to get the value from the 2nd column in the listview for the selected item

Can you help me?

Thanks in advance

Hi There
I have got as far as being able to delete the record is if i do this -

[Highlight=VB]
Dim adoRemove As Recordset

Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=XPHOME;uid=Paul ;pwd=;database=ContractManagement;"

Set adoRemove = New Recordset

Dim vacancyid As String
Dim code As String
vacancyid = Form7.txtFields(0).Text
code = 6
'Dim tblrequired As String
tablerequired = tblexpertiserequirements


adoRemove.Open "select * from tblexpertiserequirements where lngvacancyid = " & vacancyid, db, adOpenStatic, adLockOptimistic
adoRemove.MoveFirst

adoRemove.Find "bytexpertisecode = '" & code & "'"

adoRemove.Delete

[Highlight=VB]

This will delete a the record where the code is 6 - I just need to know how to reference the column in the listview which
contains the code

Thanks

So here we are - I have two columns in the listview

When removing an item from the database, i need to know the Vacancyid ( from the textbox on the form I dont have a problem with that ) and the bytexpertisecode( from the second column in the listview). I cant find out how to do it, i have searched and searched to no avail
Please help

Get the vacancy ID.

Use the connection object's EXECUTE method to run a DELETE statement against your table, using the vacancy ID.

If you want the second column value use the below. HTH :)

VB Code:

ListView1.SelectedItem.SubItems(1)

Right...this is dead easy. I didn't read this thread yesterday as I thought someone would have already answered it:

VB Code:

Private Sub cmdDelete_Click()
Dim adoConn As Connection
Dim lvwItem As ListItem
Dim strSQL As String
    Set lvwItem = ListView1.SelectedItem
    If Not (lvwItem Is Nothing) Then
        Set adoConn = New Connection
        adoConn.Open CONN_STRING
        strSQL = "DELETE * FROM TABLE WHERE ID = " & lvwItem.SubItems(1)
        adoConn.Execute strSQL
        ListView1.ListItems.Remove lvwItem.Index
        Set lvwItem = Nothing
        adoConn.Close
        Set adoConn = Nothing
    End If
End Sub

Woooof

Quote:

---

Originally posted by Wokawidget
Right...this is dead easy. I didn't read this thread yesterday as I thought someone would have already answered it:

VB Code:

Private Sub cmdDelete_Click()
Dim adoConn As Connection
Dim lvwItem As ListItem
Dim strSQL As String
    Set lvwItem = ListView1.SelectedItem
    If Not (lvwItem Is Nothing) Then
        Set adoConn = New Connection
        adoConn.Open CONN_STRING
        strSQL = "DELETE * FROM TABLE WHERE ID = " & lvwItem.SubItems(1)
        adoConn.Execute strSQL
        ListView1.ListItems.Remove lvwItem.Index
        Set lvwItem = Nothing
        adoConn.Close
        Set adoConn = Nothing
    End If
End Sub

Woooof

---

Use stored procedures, you shouldn't be executing SQL on the client side. Too much network traffic going in and out. Have the server do the grunt work.

Quick note - it's DELETE FROM, not DELETE * FROM - didn't want you to spend forever debugging a typo...

Also, I agree with JHermiz - use a SPROC - pass it just the listview value as a parameter and let it do the delete with the parameter.

No SPs in Access...

Yea yea, I was tired this morning. Sorry about the * :D

Woof

alf, you reference the second column exactly as you would with the first.

It is simply :

strItem=List.List(List.ListIndex)

this will give you the selected content of whatever column

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Thanks for helping me out with somethign that was driving me crazy - i will have à cold beer waiting for all!!!!
This is what i am using and it works!

[/Highlight]
Dim adoRemove As Recordset

Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=XPHOME;uid=Paul ;pwd=;database=ContractManagement;"

Set adoRemove = New Recordset

Dim vacancyid As String
Dim code As String
vacancyid = Form7.txtFields(0).Text



code = ListView2.SelectedItem.SubItems(1)



'Dim tblrequired As String
'tablerequired = tblexpertiserequirements

'adoRemove.Open "DELETE FROM tblexpertiserequirements WHERE lngvacancyid = " & ListView2.SelectedItem.SubItems(1), db, adOpenStatic, adLockOptimistic
adoRemove.Open "select * from tblexpertiserequirements where lngvacancyid = " & vacancyid, db, adOpenStatic, adLockOptimistic


adoRemove.Find "bytexpertisecode = '" & code & "'"

adoRemove.Delete
adoRemove.Close

[/Highlight]

at the last

VB Code:

try

[/Highlight] instead [Highlight=VB]