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

[altf4]

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

[altf4]

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

[altf4]

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

[mendhak]

Get the vacancy ID.

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

[lintz]

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

ListView1.SelectedItem.SubItems(1)

[Wokawidget]

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

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

[jhermiz]

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:

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.

[szlamany]

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.

[Wokawidget]

No SPs in Access...

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

Woof

[gotcha_crazy]

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

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

[altf4]

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]

[nareth]

at the last

try

[/Highlight] instead [Highlight=VB]