HELP!!! Re: Deleting from Database with Listview
In the code where you ADD the items to the ListView:
Set li = LV.ListItems.Add(, , rs.Fields(fldname))
you will need to add a field (between the two commas, which is the KEY of
the entry (The Primary Key of the record).
This code asppears to be something that you have copied from soemwhere, and
you are now trying to chang the fuctionality to conform to something that
you need. This is rather dangerous to do when you do not fully understand
what the code is doing in the first palce.(The reason for this guess is that
this is a GETTING STARTED discussion, but this is fairly complex and reasonable
advanced code, so that the code seems somewhat out of place in a GETTING
In the Table, from which you are retrieving the data, theere will be a field
(probbaly the first field in the record) which defines the PRIMARY KEY of
that record (a value which UNIQUELY identifies that record). This field's
value will serve as the KEY of the ListView item, and it is the value in
this field which should be used in the line which adds the line to the ListView
control. If this value is in fact a string, then you can use that value
precisely as is. If that value happens to be a Number (which it probably
is), then you will need to "convert" it to a string, usually accomplished
by simply appending a single character onto the RIGHT END of the value (like
this - assuming that the Primary Key of the record IS IN FACT in Field(0))
Set li = LV.ListItems.Add(,rs.Fields(0) & "K" , rs.Fields(fldname))
Then, in the Delete Line, you would have code like this:
cn.Execute "DELETE from sn where <KeyField> = " & Val(ListView1.SelectedItem.Key)
where <KeyField> is the NAME of the field (in your table) which holds the
Primary Key. This means that you MUST know the names of the fields in the
table, and MUST know which of the fields holds the Primary Key. (Clearly
if you know this field name, then you can use that field-name EXPLICITLY
in the line:
Set li = LV.ListItems.Add(,rs.Fields(<NameOfPrimaryKeyField>) & "K" , rs.Fields(fldname))
"Bryan" <email@example.com> wrote:
>Thanks for your help, but that didnt work,
>here is what im working with....
> 'here is the connection....
> ' works fine
> Set cn = New adodb.Connection
> cn.ConnectionString = "Provider=Microsoft.jet.oledb.3.51;" & _
> "Data Source = c:\vbstuff\cfidbtotxt\sndb.mdb"
> Set comm = New adodb.Command
> comm.CommandText = "sn"
> comm.CommandType = adCmdTable
> comm.ActiveConnection = cn
> Set rs = New adodb.Recordset
> Set rs.Source = comm
> rs.CursorLocation = adUseClient
> rs.Open , , adOpenStatic, adLockPessimistic
> LDlistView ListView1, rs
>'here is the code for loading listview1
>'this works fine
> Sub LDlistView(LV As ListView, rs As adodb.Recordset, _
> Optional MaxRecords As Long)
> Dim Fld As adodb.Field, alignment As Integer
> Dim reccount As Long, i As Long, fldname As String
> Dim li As ListItem
> On Error GoTo errhndlr
> 'clear the contents of the listview control
> ' create the columnheader collection
> For Each Fld In rs.Fields
> 'filter out undesired field types
> Select Case Fld.Type
> Case adBoolean, adCurrency, adDate, adDecimal, adDouble
> alignment = lvwColumnRight
> Case adInteger, adNumeric, adSingle, adSmallInt, adVarNumeric
> alignment = lvwColumnRight
> Case adBSTR, adChar, adVarChar, adVariant
> alignment = lvwColumnLeft
> Case Else
> alignment = -1 ' This means unsupported field type
> End Select
> 'if field type is ok, create a column with the correct alignment
> If alignment <> -1 Then
> 'the first column must be left aligned
> If LV.ColumnHeaders.Count = 0 Then alignment = lvwColumnLeft
> LV.ColumnHeaders.Add , , Fld.Name, Fld.DefinedSize * 200, _
> End If
> ' exit if there are no fields that can be shown
> If LV.ColumnHeaders.Count = 0 Then Exit Sub
> ' add all the records in the recordset
> Do Until rs.EOF
> reccount = reccount + 1
> ' add the main list item object
> fldname = LV.ColumnHeaders(1).Text
> Set li = LV.ListItems.Add(, , rs.Fields(fldname))
> ' add all subsequent listsubitem objects
> For i = 2 To LV.ColumnHeaders.Count
> fldname = LV.ColumnHeaders(i)
> li.ListSubItems.Add , , rs.Fields(fldname) & ""
> If reccount = MaxRecords Then Exit Do
> end sub
> Private Sub ListView1_DblClick()
> 'here is where the problem lies...
> 'the code you gave me...
> cn.Execute "DELETE from sn where KeyField = '" & ListView1.SelectedItem.Key
>& " '"
> LDlistView ListView1, rs
>This gives me an error, "No value given for one or more required parameters"
>Whats the problem here? I still don't understand
>Appreciate some more help, Thanks!!!
>"Arthur Wood" <firstname.lastname@example.org> wrote:
>>Use the ADO Connection object:
>> cnADO.Execute "DELETE from Table where KeyField = '" & ListView1.SelectedItem.Key
>>I am assuming here that you are adding the Items to the Listview control
>>using as the Key field in the Add method the Key value for the item in
>>You might trying posting some of the code that you have tried and we will
>>try to assist you.
>>"Bryan" <email@example.com> wrote:
>>>i am using ADO please help!!!
>>>"Arthur Wood" <firstname.lastname@example.org> wrote:
>>>> What Data Access Technology are you using: DAO or ADO? The difference
>>>>is important here.
>>>>"Bryan" <email@example.com> wrote:
>>>>>I have a listview that shows records in a database. The only way that
>>>>>delete anything doesnt work it deletes the first item in the listview
>>>>>the item selected, but if i don't use the movefirst method it errors
>>>>>"Requested operation requires a current record" and doesnt delete any
>>>>>heres a snippet of my code:
>>>>>Private Sub ListView1_DblClick()
>>>>>rs.MoveFirst 'If i leave this here it deletes the first record in the
>>>>> 'listview and not the selected one
>>>>>rs("SN") = Listview1.SelectedItem
>>>>>rs("RN") = Listview1.SelectedItem
>>>>> 'refresh the listview
>>>>>If anyone can help i would appreciate it. I know it has to be something
>>>>>I don't think i need to have rs.movefirst, but i know i need something
>>>>>at that point.
>>>>>Thanks in advance for any help!!!!!
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center