dcsimg
Results 1 to 16 of 16

Thread: [RESOLVED] Run time Error 35603 Invalid Key

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2019
    Posts
    11

    Resolved [RESOLVED] Run time Error 35603 Invalid Key

    Private Sub delBtn_Click()
    rs.Open
    Dim list As ListItem
    Set list = ListView1.ListItems.Add(, CStr(rs!Project_Name))
    confirm = MsgBox("Do you want to delete the Employee Record", vbYesNo + vbCritical, "Deletion Confirmation")
    If confirm = vbYes Then
    ListView1.ListItems.Remove ListView1.SelectedItem.Key 'this line gives error
    Dim CM As ADODB.Command
    Set CM = New ADODB.Command
    Set CM.ActiveConnection = con
    CM.CommandType = adCmdText
    CM.CommandText = "DELETE FROM bmsTable WHERE Project_Name= ?"
    CM.Parameters.Append CM.CreateParameter("@Name", adVarChar, adParamInput, 50, ListView1.SelectedItem.Key)
    CM.Execute , , adExecuteNoRecords
    MsgBox "Record successfully deleted..", vbInformation, "Success"
    End If
    End Sub

  2. #2
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    18,449

    Re: Run time Error 35603 Invalid Key

    Welcome to the forums

    What do you get if you Debug.Print ListView1.SelectedItem.Key
    That question was just out of curiosity. Why use a key for deletion? This should work whether an item has a key or not, assuming there is indeed a selected item:
    Code:
    ListView1.ListItems.Remove ListView1.SelectedItem.Index
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2019
    Posts
    11

    Re: Run time Error 35603 Invalid Key

    I tried already that code It only deleted the first item not the selected item in listview

  4. #4
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    18,449

    Re: Run time Error 35603 Invalid Key

    Quote Originally Posted by kenlow View Post
    I tried already that code It only deleted the first item not the selected item in listview
    I think you need to step through your code and determine which list item that the .SelectedItem is referring to. Obviously it is not what you think. The .SelectedItem can be "Nothing".
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,962

    Re: Run time Error 35603 Invalid Key

    Quote Originally Posted by LaVolpe View Post
    I think you need to step through your code and determine which list item that the .SelectedItem is referring to. Obviously it is not what you think. The .SelectedItem can be "Nothing".
    Probably because he expects, after he does the ListView.ListItems.Add, that the added item is automatically the SelectedItem....

    Nevermind, that if that code had worked he would fail a few lines later, since he again refers to the SelectedItem.Key, which he has already deleted (as i said: if his code had worked....).

    A rule of thumb:
    1) Delete from Database
    2) If DeleteFromDatabase = SUCCESS Then Remove From AnyVisualRepresentation (Like from a ListView)
    Last edited by Zvoni; Oct 16th, 2019 at 07:22 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2019
    Posts
    11

    Re: Run time Error 35603 Invalid Key

    ListView1.ListItems.Remove ListView1.SelectedItem.Index

    I tried this sir but it deletes all my duplicate data
    Last edited by kenlow; Oct 16th, 2019 at 07:27 PM. Reason: Add info

  7. #7
    Member gilman's Avatar
    Join Date
    Jan 2017
    Location
    Bilbao
    Posts
    63

    Re: Run time Error 35603 Invalid Key

    You have to delete the ListItem, after delete de record in the BD, if you do it before, when you execute
    Code:
    CM.Parameters.Append CM.CreateParameter("@Name", adVarChar, adParamInput, 50, ListView1.SelectedItem.Key)
    the SelectedItem do not exists, or is other ListItem

  8. #8

    Thread Starter
    New Member
    Join Date
    Oct 2019
    Posts
    11

    Re: Run time Error 35603 Invalid Key

    Quote Originally Posted by Zvoni View Post
    Probably because he expects, after he does the ListView.ListItems.Add, that the added item is automatically the SelectedItem....

    Nevermind, that if that code had worked he would fail a few lines later, since he again refers to the SelectedItem.Key, which he has already deleted (as i said: if his code had worked....).

    A rule of thumb:
    1) Delete from Database
    2) If DeleteFromDatabase = SUCCESS Then Remove From AnyVisualRepresentation (Like from a ListView)
    Please enlighten me sir and if you don't mind can you give me example code

  9. #9

    Thread Starter
    New Member
    Join Date
    Oct 2019
    Posts
    11

    Re: Run time Error 35603 Invalid Key

    Quote Originally Posted by Zvoni View Post
    Probably because he expects, after he does the ListView.ListItems.Add, that the added item is automatically the SelectedItem....

    Nevermind, that if that code had worked he would fail a few lines later, since he again refers to the SelectedItem.Key, which he has already deleted (as i said: if his code had worked....).

    A rule of thumb:
    1) Delete from Database
    2) If DeleteFromDatabase = SUCCESS Then Remove From AnyVisualRepresentation (Like from a ListView)


    Please enlighten me sir and if you don't mind can you give me an example code

  10. #10
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    18,449

    Re: Run time Error 35603 Invalid Key

    What Zvoni is stating is that whenever you add a new listitem, it is not automatically the "SelectedItem". If you want that to happen, you might want to tweak your line to:

    Code:
    Set list = ListView1.ListItems.Add(, CStr(rs!Project_Name))
    Set ListView1.SelectedItem = list
    Also, he mentioned that if that selected item is actually removed from the listview, you attempt to include the deleted item's Key in your CreateParmeter line. Once the selected item is deleted, SelectedItem becomes Nothing.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,962

    Re: Run time Error 35603 Invalid Key

    Correct.
    It's also why i said to first delete from Database, and if that is successful, then (and only then!) delete from Listview.

    EDIT: Using the first Parameter of the Execute-Method of the Command-Object would allow him to check if DELETE was successful
    Last edited by Zvoni; Oct 17th, 2019 at 05:52 AM.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  12. #12

    Thread Starter
    New Member
    Join Date
    Oct 2019
    Posts
    11

    Re: Run time Error 35603 Invalid Key

    Quote Originally Posted by LaVolpe View Post
    What Zvoni is stating is that whenever you add a new listitem, it is not automatically the "SelectedItem". If you want that to happen, you might want to tweak your line to:

    Code:
    Set list = ListView1.ListItems.Add(, CStr(rs!Project_Name))
    Set ListView1.SelectedItem = list
    Also, he mentioned that if that selected item is actually removed from the listview, you attempt to include the deleted item's Key in your CreateParmeter line. Once the selected item is deleted, SelectedItem becomes Nothing.
    I tried this code but It didnt delete

  13. #13

    Thread Starter
    New Member
    Join Date
    Oct 2019
    Posts
    11

    Re: Run time Error 35603 Invalid Key

    Quote Originally Posted by LaVolpe View Post
    What Zvoni is stating is that whenever you add a new listitem, it is not automatically the "SelectedItem". If you want that to happen, you might want to tweak your line to:

    Code:
    Set list = ListView1.ListItems.Add(, CStr(rs!Project_Name))
    Set ListView1.SelectedItem = list
    Also, he mentioned that if that selected item is actually removed from the listview, you attempt to include the deleted item's Key in your CreateParmeter line. Once the selected item is deleted, SelectedItem becomes Nothing.
    I tried this code but It didnt delete

  14. #14
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,085

    Re: Run time Error 35603 Invalid Key

    Quote Originally Posted by kenlow View Post
    Please enlighten me sir and if you don't mind can you give me example code
    here a sample

    start a new Project and add this to a Modul
    Code:
    Option Explicit
    
    
    Public Cn As ADODB.Connection       'Connection to mdb
    Public DBName As String             'Name/Path to mdb
    
    'Connection
    Public Function ConnectionOpen(Cn As ADODB.Connection) As Boolean
       
       Dim Msg As String
      
          'Object generieren
          If Cn Is Nothing Then
             Set Cn = New ADODB.Connection
          End If
      
          With Cn
             If Cn.State = adStateOpen Then
                ConnectionOpen = True
                Exit Function
             End If
               DBName = App.Path & "\Adressen.mdb"
             'Beschreibung Connection
             On Error GoTo Error
             .CursorLocation = adUseClient
             .Mode = adModeShareDenyNone
             .Provider = "Microsoft.Jet.OLEDB.4.0"
             .ConnectionString = "Data Source=" & DBName
             .Open
             ConnectionOpen = True
          End With
    '      xyzForm.Show
    Error:
          If Err.Number <> 0 Then
             Msg = "Database Connection error" & vbCrLf & _
                   "  " & vbCrLf & vbCrLf & _
                   Err.Description
             ShowError Err.Number, Msg, "ConnectionOpen"
          End If
          On Error GoTo 0
    End Function
    
    Public Function GetRSEmployees(Optional ID As Long = 0, _
                                    Optional LockType As ADODB.LockTypeEnum = adLockReadOnly) _
                                    As ADODB.Recordset
    
       Dim Rs As ADODB.Recordset
       Dim sSQL As String
       
          If ID = 0 Then
             'read all
             sSQL = "Select * From Employees Order By LastName"
          Else
             'read single
             sSQL = "Select * From Employees Where EmployeeID = " & ID
          End If
          
          Set Rs = New ADODB.Recordset
          Rs.CursorLocation = adUseClient
          Rs.Open sSQL, Cn, adOpenKeyset, LockType
          
          If Rs.RecordCount > 0 Then
             Set GetRSEmployees = Rs
          End If
          
          Set Rs = Nothing
    End Function
    
    
    
    
    Public Sub ShowError(ErrNumber As Long, ErrDescription As String, _
                             Optional Titel As String = "")
    
       Dim Msg As String
          Msg = "Error " & ErrNumber & vbCrLf & vbCrLf & _
                ErrDescription
          If Len(Titel) > 0 Then
             MsgBox Msg, vbCritical, Titel
          Else
             MsgBox Msg, vbCritical
          End If
    End Sub
    and this to a Form
    with a Listview, 2 Textboxes and a Commanbutton
    Code:
    Private Sub Command1_Click()
    
       Dim ID As Long 'use ID for PrimaryKey
       Dim i As Long
       Dim sSQL As String
       Dim Msg As String, Titel As String
       Dim Li As ListItem
       
          If ListView1.ListItems.Count = 0 Then
             Exit Sub
          End If
          
       
          
          Set Li = ListView1.SelectedItem
          ID = Val(Li.Key) 'PrimaryKey
          
          Msg = "Employee " & Li.Text & vbCrLf & vbCrLf & _
                "delete this record ?" & vbCrLf & _
                "" & Space(10)
          If vbNo = MsgBox(Msg, vbYesNo Or vbDefaultButton2 Or vbQuestion, _
                    "delete Employee record") Then
             Exit Sub
          End If
          
          'Delete Record
          sSQL = "Delete From Employees Where EmployeeID = " & ID
          Cn.Execute sSQL
    
          'set focus to Listview again
          i = Li.Index
          ListView1.ListItems.Remove i
          If i > ListView1.ListItems.Count Then
             i = ListView1.ListItems.Count
          End If
          If i > 0 Then
             ListView1.ListItems(i).Selected = True
          End If
    End Sub
    
    Private Sub Form_Activate()
     Static Initdone As Boolean
       
          If Initdone Then
             Exit Sub
          End If
          
          Initdone = True
          
          Me.Refresh
          If Not ConnectionOpen(Cn) Then
             Unload Me
             Exit Sub
          End If
          Me.Refresh
          
          Employees2Listview
          If Not ListView1.SelectedItem Is Nothing Then
             ListView1_ItemClick ListView1.SelectedItem
          End If
    End Sub
    
    
    Private Sub Employees2Listview()
    
       Dim Rs As ADODB.Recordset
       Dim Li As ListItem
       
        'read all employees to Listview
        
        Set Rs = GetRSEmployees
          
          With ListView1
             .View = lvwReport
             .LabelEdit = lvwManual
             .GridLines = True
             .FullRowSelect = True
             .HideSelection = False
             
             .ColumnHeaders.Add , , "LastName", 1800
             .ColumnHeaders.Add , , "FirstName", 1500
             .ColumnHeaders.Add , , "Title", 1500
             
             Do While Not Rs.EOF
                Set Li = .ListItems.Add
                'use the PrimaryKey from your Table
                'in this case = EmployeeID
                'to use this PrimaryKey from the Database Table it
                'has to be alphanumeric so PrimaryKey & "x" = alphanumeric
                
                Li.Key = Rs.Fields("EmployeeID").Value & "x"
                Li.Text = Rs.Fields("LastName").Value & vbNullString
                Li.SubItems(1) = Rs.Fields("FirstName").Value & vbNullString
                Li.SubItems(2) = Rs.Fields("Title").Value & vbNullString
                Rs.MoveNext
             Loop
          End With
          
          Rs.Close
          Set Rs = Nothing
    End Sub
    
    Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
     Dim Li As ListItem
       Dim i As Long
    
          Set Li = ListView1.SelectedItem
          If Not Li Is Nothing Then
             Text1.Text = CStr(Val(Li.Key))
             Text2.Text = Li.Text
             Set Li = Nothing
          End If
    End Sub

    here the sample as Zip
    delete Listview.zip


    HTH
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,962

    Re: Run time Error 35603 Invalid Key

    When you open your Recordset, do you have (valid) Recordsets (RecordCount>0)?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  16. #16

    Thread Starter
    New Member
    Join Date
    Oct 2019
    Posts
    11

    Re: Run time Error 35603 Invalid Key

    Quote Originally Posted by ChrisE View Post
    here a sample

    start a new Project and add this to a Modul
    Code:
    Option Explicit
    
    
    Public Cn As ADODB.Connection       'Connection to mdb
    Public DBName As String             'Name/Path to mdb
    
    'Connection
    Public Function ConnectionOpen(Cn As ADODB.Connection) As Boolean
       
       Dim Msg As String
      
          'Object generieren
          If Cn Is Nothing Then
             Set Cn = New ADODB.Connection
          End If
      
          With Cn
             If Cn.State = adStateOpen Then
                ConnectionOpen = True
                Exit Function
             End If
               DBName = App.Path & "\Adressen.mdb"
             'Beschreibung Connection
             On Error GoTo Error
             .CursorLocation = adUseClient
             .Mode = adModeShareDenyNone
             .Provider = "Microsoft.Jet.OLEDB.4.0"
             .ConnectionString = "Data Source=" & DBName
             .Open
             ConnectionOpen = True
          End With
    '      xyzForm.Show
    Error:
          If Err.Number <> 0 Then
             Msg = "Database Connection error" & vbCrLf & _
                   "  " & vbCrLf & vbCrLf & _
                   Err.Description
             ShowError Err.Number, Msg, "ConnectionOpen"
          End If
          On Error GoTo 0
    End Function
    
    Public Function GetRSEmployees(Optional ID As Long = 0, _
                                    Optional LockType As ADODB.LockTypeEnum = adLockReadOnly) _
                                    As ADODB.Recordset
    
       Dim Rs As ADODB.Recordset
       Dim sSQL As String
       
          If ID = 0 Then
             'read all
             sSQL = "Select * From Employees Order By LastName"
          Else
             'read single
             sSQL = "Select * From Employees Where EmployeeID = " & ID
          End If
          
          Set Rs = New ADODB.Recordset
          Rs.CursorLocation = adUseClient
          Rs.Open sSQL, Cn, adOpenKeyset, LockType
          
          If Rs.RecordCount > 0 Then
             Set GetRSEmployees = Rs
          End If
          
          Set Rs = Nothing
    End Function
    
    
    
    
    Public Sub ShowError(ErrNumber As Long, ErrDescription As String, _
                             Optional Titel As String = "")
    
       Dim Msg As String
          Msg = "Error " & ErrNumber & vbCrLf & vbCrLf & _
                ErrDescription
          If Len(Titel) > 0 Then
             MsgBox Msg, vbCritical, Titel
          Else
             MsgBox Msg, vbCritical
          End If
    End Sub
    and this to a Form
    with a Listview, 2 Textboxes and a Commanbutton
    Code:
    Private Sub Command1_Click()
    
       Dim ID As Long 'use ID for PrimaryKey
       Dim i As Long
       Dim sSQL As String
       Dim Msg As String, Titel As String
       Dim Li As ListItem
       
          If ListView1.ListItems.Count = 0 Then
             Exit Sub
          End If
          
       
          
          Set Li = ListView1.SelectedItem
          ID = Val(Li.Key) 'PrimaryKey
          
          Msg = "Employee " & Li.Text & vbCrLf & vbCrLf & _
                "delete this record ?" & vbCrLf & _
                "" & Space(10)
          If vbNo = MsgBox(Msg, vbYesNo Or vbDefaultButton2 Or vbQuestion, _
                    "delete Employee record") Then
             Exit Sub
          End If
          
          'Delete Record
          sSQL = "Delete From Employees Where EmployeeID = " & ID
          Cn.Execute sSQL
    
          'set focus to Listview again
          i = Li.Index
          ListView1.ListItems.Remove i
          If i > ListView1.ListItems.Count Then
             i = ListView1.ListItems.Count
          End If
          If i > 0 Then
             ListView1.ListItems(i).Selected = True
          End If
    End Sub
    
    Private Sub Form_Activate()
     Static Initdone As Boolean
       
          If Initdone Then
             Exit Sub
          End If
          
          Initdone = True
          
          Me.Refresh
          If Not ConnectionOpen(Cn) Then
             Unload Me
             Exit Sub
          End If
          Me.Refresh
          
          Employees2Listview
          If Not ListView1.SelectedItem Is Nothing Then
             ListView1_ItemClick ListView1.SelectedItem
          End If
    End Sub
    
    
    Private Sub Employees2Listview()
    
       Dim Rs As ADODB.Recordset
       Dim Li As ListItem
       
        'read all employees to Listview
        
        Set Rs = GetRSEmployees
          
          With ListView1
             .View = lvwReport
             .LabelEdit = lvwManual
             .GridLines = True
             .FullRowSelect = True
             .HideSelection = False
             
             .ColumnHeaders.Add , , "LastName", 1800
             .ColumnHeaders.Add , , "FirstName", 1500
             .ColumnHeaders.Add , , "Title", 1500
             
             Do While Not Rs.EOF
                Set Li = .ListItems.Add
                'use the PrimaryKey from your Table
                'in this case = EmployeeID
                'to use this PrimaryKey from the Database Table it
                'has to be alphanumeric so PrimaryKey & "x" = alphanumeric
                
                Li.Key = Rs.Fields("EmployeeID").Value & "x"
                Li.Text = Rs.Fields("LastName").Value & vbNullString
                Li.SubItems(1) = Rs.Fields("FirstName").Value & vbNullString
                Li.SubItems(2) = Rs.Fields("Title").Value & vbNullString
                Rs.MoveNext
             Loop
          End With
          
          Rs.Close
          Set Rs = Nothing
    End Sub
    
    Private Sub ListView1_ItemClick(ByVal Item As MSComctlLib.ListItem)
     Dim Li As ListItem
       Dim i As Long
    
          Set Li = ListView1.SelectedItem
          If Not Li Is Nothing Then
             Text1.Text = CStr(Val(Li.Key))
             Text2.Text = Li.Text
             Set Li = Nothing
          End If
    End Sub

    here the sample as Zip
    delete Listview.zip


    HTH
    Thank you sir.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width