ADODB Recordset update failing in SQL2016

The backend SQL server has recently been upgraded from Microsoft SQL 2008 R2 to SQL2016
An application was unchanged but is now failing to update a recordset.  

The ODBC SQLNCLI11 has been in use the past year with SQL 2008 R2.

      ' Get the transfer record from tblTransfers
   strSQL = "tblTransfers"
   strSQL = strSQL & " WHERE IMERef = '" & frm.IMERef & "'"
   Set rst = New ADODB.Recordset
   dcnn
   
   rst.Open strSQL, gcnn, adOpenKeyset, adLockOptimistic, adCmdTable
   
   ' SQL errors should not occur
   If rst.RecordCount <> 1 Then
     If rst.RecordCount = 0 Then
       msg2 = "Cannot find the Transfer record"
     Else
       msg2 = "More than one Transfer record has been found"
     End If
     GoTo ErrHandle
   End If

    rst!Modifier.Value = [TempVars]![Logon_UserID].[Value]
    rst!DateMod.Value = Now()
    rst!grossamount = frm.SendAmount 'v9_5_1 for future changes to fields
    rst.Update

Open in new window


Code Adding new records works.  

Any suggestions for issues associated with an SQL2016?
Nigel Keith-WalkerContractorAsked:
Who is Participating?
 
Nigel Keith-WalkerContractorAuthor Commented:
Following the debugging, I checked on the cursor location property.  It is adUseClientBatch.

referring to the MS documentation https://msdn.microsoft.com/en-us/library/ee252442(v=bts.10).aspx

The enumeration for adUseClient and  adUseClientBatch are both 3.  

If the CursorLocation property is set to adUseClient, the recordset will be accessible as read-only, and recordset updates to the host are not possible.

I will try changing this to adUseServer, and advise.
0
 
ManjuIT - Project ManagerCommented:
Shouldnt this be,

    ' Get the transfer record from tblTransfers
   strSQL = "tblTransfers"

    ' Get the transfer record from tblTransfers
   strSQL = "Update tblTransfers"

as you are updating the records?
0
 
Nigel Keith-WalkerContractorAuthor Commented:
The parameter adCmdTable is already specifying that the table is tblTransfers.  
I suspect that the problem could be with the cursor type no longer working with a version later than SQL2008
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ManjuIT - Project ManagerCommented:
i meant, update keyword is missing in the strsql. I presume your insert query works fine and update isnt.
0
 
Nigel Keith-WalkerContractorAuthor Commented:
The strSQL only accesses the single record as a recordset.  The recordset fields are then changed before the last statement which updates the record on the server - rst.update.  We know that this worked with SQL 2008 R2
0
 
ManjuIT - Project ManagerCommented:
Paste the error code here please
0
 
Ryan ChongCommented:
The cursortype and locktype look ok... but try this:

' Get the transfer record from tblTransfers
   strSQL = "select * tblTransfers"
   strSQL = strSQL & " WHERE IMERef = '" & frm.IMERef & "'"
   Set rst = New ADODB.Recordset
   dcnn
   
   rst.Open strSQL, gcnn, adOpenKeyset, adLockOptimistic
   
   ' SQL errors should not occur
   If rst.RecordCount <> 1 Then
     If rst.RecordCount = 0 Then
       msg2 = "Cannot find the Transfer record"
     Else
       msg2 = "More than one Transfer record has been found"
     End If
     GoTo ErrHandle
   End If

    rst!Modifier.Value = [TempVars]![Logon_UserID].[Value]
    rst!DateMod.Value = Now()
    rst!grossamount = frm.SendAmount 'v9_5_1 for future changes to fields
    rst.Update

Open in new window


if it's not working, post the error message here as what mentioned by @Manju

also, do look into your ConnectionString  settings and make sure the credential allows Update operations?
0
 
Nigel Keith-WalkerContractorAuthor Commented:
Hi the connection string is:


 Public Const DBCONNSTRING = "Provider=SQLNCLI11;Password=???????;User ID=???????;Initial Catalog=?????;Data Source=sql.?????.com.au"

No error message was received at all.  The update just failed to occur.
0
 
Ryan ChongCommented:
check the user permission of User ID and make sure you got roles such as data owner/ data writter?
0
 
Nigel Keith-WalkerContractorAuthor Commented:
Yep.  The userid has db_datareader, db_datawriter and db_data_owner.  Of course it would need this to add new records.
0
 
Ryan ChongCommented:
just for testing purposes, are you able to execute an update statement using this same connection?
0
 
Nigel Keith-WalkerContractorAuthor Commented:
Thought about executing a Stored procedure, but I do not have an update transfer Sp.  Also it would not have checked out this connection.  I will create a VBA connection to test.  May take a few minutes.
0
 
Nigel Keith-WalkerContractorAuthor Commented:
Created a test function to update a field in the tblTransfer.  It fell over in the rst.open statement.


Public Function Test_update_Transfer()
'Test the update of the Transfer database
   
   Dim rst As New ADODB.Recordset
   Dim strSQL As String
   Dim msg As String
                 
   On Error GoTo ErrHandle

   strSQL = "tblTransfer"
   strSQL = strSQL & "'A322044'"
   Set rst = New ADODB.Recordset

   dcnn

   rst.Open strSQL, gcnn, adOpenKeyset, adLockOptimistic, adCmdTable
   
   If rst.RecordCount <> 1 Then
     msg = "Transfer Rec not found"
     GoTo Exit_Function
   End If
   
   rst!Message = "Test update on SQL 2016"
   
   rst.Update
   
Exit_Function:
   Exit Function

ErrHandle:
   msg = "Problem with Test_update_Transfer()" & vbCrLf & Err.Description
   MsgBox msg, vbOKOnly, "Test_update_Transfer"
End Function

Open in new window

Test_update.jpg
0
 
ManjuIT - Project ManagerCommented:
Try a space before the value and test again. share results

 strSQL = "tblTransfer"
   strSQL = strSQL & " 'A322044'"
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Why not just using a regular SELECT statement with a proper WHERE clause instead of just a table?
0
 
Nigel Keith-WalkerContractorAuthor Commented:
Adding a select and where statement brings us back to the original coding.  

Getting late in Australia.  I am an early riser 5:30, so will check the site when I wake up.

Keep the suggestions coming.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Your original code is
   strSQL = "tblTransfers"
   strSQL = strSQL & " WHERE IMERef = '" & frm.IMERef & "'"

Open in new window

instead of
   strSQL = "select * from tblTransfers WHERE IMERef = '" & frm.IMERef & "'"

Open in new window

0
 
Nigel Keith-WalkerContractorAuthor Commented:
The strSQL statement "tblTransfers WHERE IMEref = 'A322044'" is appropriate for the option adCmdTable

Option adCmdText would require the strSQL "Select * from tblTransfers WHERE IMEref  = 'A322044'"

I know that it is retrievng the correct record and only one record.  The issue is with the update.  Unless someone can advise how this may have changed with SQL 2016, I woud have thought that the cursor type location was more important.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Did you try with your corrected test function (adding the WHERE and spaces)?
0
 
Nigel Keith-WalkerContractorAuthor Commented:
Yep!  The connection needs to have the cursor location on the server.  adUseServer is the default buy the application has been using adUseClient for ten years initially with SQL 2005 then SQL 2008 without any problem.
0
 
Nigel Keith-WalkerContractorAuthor Commented:
The application has a mixture of connection methods.
  • STRSQL with recordset open
  • calls to stored procedures
It seems as though the recordset open requires cursor location adUseServer  whereas the stored procedures require adUseClient.

Does anybody have any ideas on this?
0
 
Nigel Keith-WalkerContractorAuthor Commented:
Just wondering if I have all of the VBA reference libraries necessary.  See attached file
VBA_Reference.jpg
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
ADO 2.8 is old enough to present a 2005 or 2008 "view" on functions, so I don't think you can get around using two different connections having different cursor location settings, if it is true you have to use adUseClient with SPs (I do not see why this should be necessary).
0
 
Ryan ChongCommented:
wondering what fix has been done in your application?
0
 
Nigel Keith-WalkerContractorAuthor Commented:
The fix has been made to the application, tested and put into production.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.