VB Error with updating

Can someone please help with what should be a simple error?

I seem to not be initiating my recordset correctly as I get this error:
Current Recordset does not support updating.  This may be a limitation of the provider, or of the selected locktype.

Below is my code for initiating the recordset as well as the Add module that throws the error.
Private Sub fLoadGuiParmList()
    Dim fsCmd As String
    Dim fsName As String
 
    Call lstGuiParm.Clear
    fsCmd = ""
    fsCmd = fsCmd & "SELECT  * "
    fsCmd = fsCmd & "FROM   GPL "
   
    Set goRecSet = goConn.Execute(fsCmd)
    If Not goRecSet.EOF Then
       goRecSet.MoveFirst
        While Not goRecSet.EOF
            fsName = goRecSet("ParmNm")
            Call lstGuiParm.AddItem(fsName)
            goRecSet.MoveNext
        Wend
    End If
    lstGuiParm.ListIndex = 0
End Sub

Private Sub btnAdd_Click()
goRecSet.AddNew
     goRecSet!ParmNm = txtParmNmGui.Text
     goRecSet!StatusCd = txtStatusGui.Text
     goRecSet!DefaultParm = txtDefaultParmGui.Text
     goRecSet.Update
     MsgBox txtParmNmGui.Text + " Added."
     lstGuiParm.SetFocus
End Sub

Open in new window

kkbenjAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
GrahamSkanConnect With a Mentor RetiredCommented:
Here's how I would do it:


Dim goRecSet As New ADODB.Recordset
    '...
     goRecSet.Open fsCmd, conn, adOpenDynamic, adLockOptimistic, adCmdText

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
as per http://msdn.microsoft.com/en-us/library/windows/desktop/ms675023(v=vs.85).aspx, the recordset returned by Connection.Execute is always a read-only, forward-only cursor.
0
 
kkbenjAuthor Commented:
GrahamSkan - it doesn't auto-fill an option for adCmdText.
run-time error '91':
Object variable or With Block variable not set
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
kkbenjAuthor Commented:
This is VB6.
0
 
Éric MoreauSenior .Net ConsultantCommented:
where and how goRecSet is declared and initialized?
0
 
GrahamSkanRetiredCommented:
Intellisense doesn't help with the third parameter, that is true. Actually you can leave it off and let ADO work out what sort of source it is dealing with.

However, that wouldn't be the error. Have you instantiated the conn object?
0
 
kkbenjAuthor Commented:
Public goRecSet as ADODB.Recordset
Set goConn = CreateObject("ADODB.Connection")
with the rest of the public declarations at the beginning of the code.

Then I tried:
goRecSet.Open fsCmd, goConn, adOpenDynamic, adLockOptimistic, adCmdText
in the fLoadGuiParmList() sub in place of
    Set goRecSet = goConn.Execute(fsCmd)

Is something missing?
0
 
kkbenjAuthor Commented:
The connection object is most likely not the issue here, as the regular Execute works fine.  (just disallows the update)
0
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried replacing:
Public goRecSet as ADODB.Recordset
Set goConn = CreateObject("ADODB.Connection")

with:
Public goRecSet as new ADODB.Recordset
0
 
kkbenjAuthor Commented:
I added "New"

adCmdText now gives Compile error: syntax error
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
try: goRecSet.Open fsCmd, goConn, adOpenDynamic, adLockOptimistic
0
All Courses

From novice to tech pro — start learning today.