Results 1 to 13 of 13

Thread: [RESOLVED] ADO Insufficient key column information for updating or refreshing

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2016
    Location
    Texas panhandle
    Posts
    50

    Resolved [RESOLVED] ADO Insufficient key column information for updating or refreshing

    Relative Newbie to ADO. DB Table named Code With Fields Text, Category, Name
    Also an AutoNumber Field named Index, which is Primary.

    Previously I used the following to get a recordset and never had a problem with it
    Code:
    Function OldRecSet(ByVal Sql As String) As ADODB.Recordset
     Set OldRecSet = New Recordset
     With OldRecSet
      .ActiveConnection = Conn
      .Source = Sql
      .CursorType = adOpenKeyset
      .LockType = adLockOptimistic
      .Open
     End With
    End Function
    Then I read about Sql Injection and switched to:
    Code:
    Function RecSet(ByVal Sql As String) As ADODB.Recordset
     Dim Cmd As ADODB.Command
     Set Cmd = New ADODB.Command
     With Cmd
      .ActiveConnection = Conn
      .CommandText = Sql
      .CommandType = adCmdText
     End With
     Set RecSet = New ADODB.Recordset
     With RecSet
      .CursorType = adOpenKeyset
      .CursorLocation = adUseClient
      .LockType = adLockOptimistic
      .Open Cmd
     End With
    End Function
    This above code works fine everywhere in my program until I tried this routine
    Code:
    Private Sub UpdateDB()
     Dim oRS As Recordset
     Dim Sql As String
     Dim Txt As String
     On Error GoTo ERRH
     Txt = RTB.Text
     Sql = "Select Text From Code Where Category = " & Quote(CurCat) & " And Name = " & Quote(CurItm)
     Set oRS = RecSet(Sql)
     oRS.Fields(0).Value = Txt
     oRS.Update
     Exit Sub
    ERRH:
     Debug.Print Sql
     Debug.Print Hex(Err.Number), Err.Description
    End Sub
    Immediate window result:
    Sql: Select Text From Code Where Category = 'Algorithms' And Name = 'AccurateRound'
    Err: 80004005 Insufficient key column information for updating or refreshing.

    I switched to the OldRecSet function for the above routine and it works fine.

    The Field Text is of type Memo. Could this be the reason?

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,246

    Re: ADO Insufficient key column information for updating or refreshing

    Well the problem with sql injection is that when you build a sql string using variables which the user may enter the user can enter stuff into one or more of those fields that will modify your sql statement. The way to avoid this is by using parameters but that is not what you have did.

    As for your error I can only guess that it is because you are not selecting the key field and it doesn't know what record to update in the db, perhaps the query is returning more than one record. Have you tried adding the key field into your select statement?

    Is there a reason why you are using a select query rather than an update query here?

  3. #3
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,924

    Re: ADO Insufficient key column information for updating or refreshing

    Quote Originally Posted by VBClassic04 View Post
    I switched to the OldRecSet function for the above routine and it works fine.

    The Field Text is of type Memo. Could this be the reason?
    No, the difference is, that:
    - your OldRecSet is using the (implicit, default) CursorLocation = adUseServer - in conjunction with CursorType = adOpenKeyset
    - your new RecSet is using explicit CursorLocation = adUseClient, where CursorType = adOpenKeyset is ignored (internally adjusted to adOpenStatic)

    Meaning, that with the new Routine (as your Error-Message indicates), you will now have to include the Tables PK-Fields in your Select,
    in case you want to use it for later Rs-Value-Updates or Rs-Deletes (in conjunction with Rs-Update or Rs-UpdateBatch).

    Also, your new RecSet-Function currently does nothing to prevent SQL-Injection-attacks...
    It would do that only:
    - when you'd use CommandObject-Parameters
    - in conjunction with an SQL-BaseString which is a String-Const or a String-Literal in your Code (and not a concatenation which includes potential UserInput)

    Here is an ADO-based GetRs() Routine - which shows how you could implement such a dynamic Param-Binding:
    Code:
    Public Function GetRs(Cnn As ADODB.Connection, ByVal SQL As String, ParamArray P()) As ADODB.Recordset
      Set GetRs = New ADODB.Recordset
      
      Dim Cmd As New ADODB.Command, i As Long
      Set Cmd.ActiveConnection = Cnn
          Cmd.CommandText = SQL
      For i = 0 To UBound(P)
        Select Case VarType(P(i))
          Case vbString: Cmd.Parameters.Append Cmd.CreateParameter(, adVarChar, , 4096, P(i))
          Case Else:     Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i))
        End Select
      Next
    
      GetRs.CursorLocation = adUseClient
      GetRs.Open Cmd, , adOpenStatic, adLockBatchOptimistic
      GetRs.Properties("Update Criteria") = adCriteriaKey 'allow only PKs, to locate Rows for Updates in the Where-Clause
      Set Cmd.ActiveConnection = Nothing
    End Function
    With the above Helper-Routine, your Recordset-retrieval could be written this way (note the additionally included PKField in magenta):
    Code:
    Const SQL as String = "Select Text, PkField From Code Where Category=? And Name=?" '<- SQL with no Concats
    Dim oRs As ADODB.Recordset 
    Set oRs = GetRs(Conn, SQL, CurCat, CurItm) '<- you can pass the Raw-VB-ParamValues without any extra Escaping or Quoting 
    Olaf
    Last edited by Schmidt; Apr 20th, 2019 at 09:36 AM.

  4. #4
    New Member
    Join Date
    Apr 2019
    Posts
    1

    Re: ADO Insufficient key column information for updating or refreshing

    Private Sub Command1_Click(Index As Integer)
    sql = "insert into customer (kd_cs,nama,almt,umur,tgl_pm,no_hp,no_fk ) " & _
    "Values('" & Text1 & "','" & Text2 & "','" & Text3 & "','" & Text4 & "','" & Format(DTPicker1, "yyyy-MM-dd") & "', '" & MaskEdBox1 & "','" & Text5 & "')"
    con.Execute (sql)

    Text1 = ””
    Text2 = ””
    Text3 = ””
    Text4 = ””
    DTPicker1 = Date
    MaskEdBox1 = ""
    Text5 = ""
    Adodc1.Refresh
    Text1.SetFocus
    End Sub

    Private Sub Command2_Click(Index As Integer)
    sql = "delete from customer where kd_cs= '" & Text1 & "'"
    con.Execute (sql)

    sql = "insert into customer (kd_cs,nama,almt,umur,tgl_pm,no_hp,no_fk ) " & _
    "Values('" & Text1 & "','" & Text2 & "','" & Text3 & "','" & Text4 & "','" & Format(DTPicker1, "yyyy-MM-dd") & "', '" & MaskEdBox1 & "','" & Text5 & "')"
    con.Execute (sql)

    Text1 = ””
    Text2 = ””
    Text3 = ””
    Text4 = ””
    DTPicker1 = Date
    MaskEdBox1 = ""
    Text5 = ""
    Adodc1.Refresh
    Text1.SetFocus
    End Sub

    Private Sub Command3_Click(Index As Integer)
    Text1 = ””
    Text2 = ””
    Text3 = ””
    Text4 = ””
    DTPicker1 = Date
    MaskEdBox1 = ""
    Text5 = ""
    Adodc1.Refresh
    Text1.SetFocus
    End Sub

    Private Sub Command4_Click(Index As Integer)
    sql = "delete from customer where kd_cs= '" & Text1 & "'"
    con.Execute (sql)

    Text1 = ””
    Text2 = ””
    Text3 = ””
    Text4 = ””
    DTPicker1 = Date
    MaskEdBox1 = ""
    Text5 = ""
    Adodc1.Refresh
    Text1.SetFocus
    End Sub

    Private Sub Command5_Click(Index As Integer)
    End
    End Sub

    Private Sub DataGrid1_Click()

    End Sub

    Private Sub DTPicker1_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = 13 Then
    Text5.SetFocus
    End If
    End Sub

    Private Sub Form_Activate()
    Text1 = ””
    Text2 = ””
    Text3 = ””
    Text4 = ""
    Text5 = ""
    DTPicker1 = Date
    MaskEdBox1 = ""

    End Sub

    Private Sub Form_Load()
    If con.State = adStateClosed Then
    connect
    End If
    End Sub

    Private Sub Text1_KeyPress(Index As Integer, KeyAscii As Integer)
    If KeyAscii = 13 Then
    sql = "select * from customer where kd_cs='" & Text1 & "'"
    Set rs = con.Execute(sql)
    If Not rs.EOF Then
    MsgBox ("data sudah ada")
    Text2 = rs!nama
    Text3 = rs!almt
    Text4 = rs!umur
    DTPicker1 = rs!tgl_pm
    MaskEdBox1 = rs!no_hp
    Text5 = rs!no_fk
    Else
    MsgBox ("data tidak ada lanjutkan input data baru")
    Text2 = ""
    Text3 = ""
    Text4 = ""
    DTPicker1 = Date
    MaskEdBox1 = ""
    Text5 = ""
    Text1.SetFocus
    End If

    End Sub

    Private Sub Text2_KeyPress(Index As Integer, KeyAscii As Integer)
    If KeyAscii = 13 Then
    Text3.SetFocus
    End If
    End Sub

    Private Sub Text3_KeyPress(Index As Integer, KeyAscii As Integer)
    If KeyAscii = 13 Then
    Text4.SetFocus
    End If

    End Sub

    Private Sub Text4_KeyPress(Index As Integer, KeyAscii As Integer)
    If KeyAscii = 13 Then
    DTPicker1.SetFocus
    End If

    End Sub

    Private Sub Text5_KeyPress(Index As Integer, KeyAscii As Integer)
    If KeyAscii = 13 Then
    Command1.SetFocus
    End If
    End Sub

  5. #5
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,924

    Re: ADO Insufficient key column information for updating or refreshing

    Quote Originally Posted by herpnotice@gmail.com View Post
    Code:
     ...
    Private Sub Command4_Click(Index As Integer)
       sql = "delete from customer where kd_cs= '" & Text1 & "'"
       con.Execute (sql)
    ...
    Not sure, what the purpose of the posting is - but it can serve as a good example for,
    "how not to do it".

    E.g. when an "evil user" who clicks your above Command4-Button,
    priorily did extend the content of your Text1-TextBox with the text: [ '; Drop Table customer; -- ]
    he will have performed a successful SQL-injection-attack (and your Table customer is gone from the DB).

    Olaf

  6. #6

    Thread Starter
    Member
    Join Date
    Sep 2016
    Location
    Texas panhandle
    Posts
    50

    Re: ADO Insufficient key column information for updating or refreshing

    Olaf, thanks for your help. Here is the revised UpdateDB routine.
    No errors, but no change to the database either.
    Something wrong with the '************ lines?

    Code:
    Private Sub UpdateDB()
     Dim oRs As Recordset
     Dim SQL As String
     On Error GoTo ErrH
     SQL = "Select Text, Index From Code Where Category=? And Name=?" '<- SQL with no Concats
     Set oRs = GetRs(Conn, SQL, CurCat, CurItm) '<- you can pass the Raw-VB-ParamValues without any extra Escaping or Quoting
     oRs.Fields(0).Value = RTB.Text   '*****************
     oRs.Update                            '*****************
     Exit Sub
    ErrH:
     Debug.Print SQL
     Debug.Print Hex(Err.Number), Err.Description
    End Sub

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    2,103

    Re: ADO Insufficient key column information for updating or refreshing

    What kind of RDBMS is this targeting? Microsoft SQL Server, Microsoft Access (JET) or anything else?

    Btw, does your Code table (the one you are selecting from) has Primary Key defined?

    cheers,
    </wqw>

  8. #8
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,924

    Re: ADO Insufficient key column information for updating or refreshing

    Quote Originally Posted by VBClassic04 View Post
    Olaf, thanks for your help. Here is the revised UpdateDB routine.
    No errors, but no change to the database either.
    Something wrong with the '************ lines?

    Code:
    Private Sub UpdateDB()
     Dim oRs As Recordset
     Dim SQL As String
     On Error GoTo ErrH
     SQL = "Select Text, Index From Code Where Category=? And Name=?" '<- SQL with no Concats
     Set oRs = GetRs(Conn, SQL, CurCat, CurItm) '<- you can pass the Raw-VB-ParamValues without any extra Escaping or Quoting
     oRs.Fields(0).Value = RTB.Text   '*****************
     oRs.Update                            '*****************
     Exit Sub
    ErrH:
     Debug.Print SQL
     Debug.Print Hex(Err.Number), Err.Description
    End Sub
    Perhaps my fault... (since I work with Rs.UpdateBatch in most of my Apps where ADO is in play, I've forgot to adapt the routine I've posted).

    Other than changing your own snippet to oRs.UpdateBatch in the above Code,
    you might want to adapt the GetRs-Routine I've posted instead:
    - from: GetRs.Open Cmd, , adOpenStatic, adLockBatchOptimistic
    - to: GetRs.Open Cmd, , adOpenStatic, adLockOptimistic

    Let me know, whether that solves your problem - if not, then there might be a different problem with the RichText-to-MemoField line.

    Olaf

  9. #9

    Thread Starter
    Member
    Join Date
    Sep 2016
    Location
    Texas panhandle
    Posts
    50

    Re: ADO Insufficient key column information for updating or refreshing

    UpdateBatch fixed the problem. Many Thanks.

  10. #10
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,924

    Re: ADO Insufficient key column information for updating or refreshing

    Quote Originally Posted by VBClassic04 View Post
    UpdateBatch fixed the problem. Many Thanks.
    Ok, good to know that it works this way now (similar to how I use to work with ADO).

    Just don't forget, that in this case - you will now have to replace each and every
    oRs.Update
    with
    oRs.UpdateBatch
    across your whole App ...
    (at all code-positions, where you apply the new GetRs-Routine I've posted, to retrieve an Rs for ValueUpdates/AddNews or Deletes).

    The advantage, when you do it this way (instead of fixing the adLockBatchOptimistic within GetRs itself) is,
    that your App is then - in principle - already prepared for "AppServer-mode" (e.g. when you place your DB behind an IIS-WebServer-instance).

    Olaf

  11. #11

    Thread Starter
    Member
    Join Date
    Sep 2016
    Location
    Texas panhandle
    Posts
    50

    Re: [RESOLVED] ADO Insufficient key column information for updating or refreshing

    Have done the replacements and all is good.

    Having marked it resolved, hate to ask but how do you handle a search query like this

    Select Name, Category, Index From Code Where Text Like '%Search%'

  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,924

    Re: [RESOLVED] ADO Insufficient key column information for updating or refreshing

    Quote Originally Posted by VBClassic04 View Post
    Have done the replacements and all is good.

    Having marked it resolved, hate to ask but how do you handle a search query like this

    Select Name, Category, Index From Code Where Text Like '%Search%'
    That would be:
    Code:
      Const SQL As String = "Select Name, Category, Index From Code Where Text Like ?" '<- SQL with no Concats
      Dim oRs As ADODB.Recordset
      Set oRs = GetRs(Conn, SQL, "%" & strToSearchFor & "%")
    I leave filling (and validating) the strToSearchFor Param (from a UserInput-TextBox or something) up to you...
    (one might check and ensure, that this strVariable either does not contain any "%"-Chars already - or properly escape these special-chars in the SearchString).

    Olaf

  13. #13

    Thread Starter
    Member
    Join Date
    Sep 2016
    Location
    Texas panhandle
    Posts
    50

    Re: [RESOLVED] ADO Insufficient key column information for updating or refreshing

    That nailed it. Thanks Olaf

Posting Permissions

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



Click Here to Expand Forum to Full Width