Recordset.update with SQL Native Client doesn't work

We recently migrated from SQL Server 7 to SQL Server 2005. Now there's a curious thing with some legacy applications. I have pasted some code below. Don't judge me, because like I said, it's legacy.

You can see, that I have two connection strings. One is commented and accessing via SQL Native Client. The other one is doing this through the old SQL Server driver. Funnily enough, when I use the new Native Client driver, the exception "Run-time error '-2147467259 (80004005)' [Microsoft][SQL Native Client]Invalid attribute value" is thrown in the rsPorder2.Update line. With the old driver, this works just alright.

Is this a bug? Is there a way, to make the code run, because we don't want to search the whole application for other occurances, if not necessary.

Any insights would be greatly appreciated.

Best regards,
DD

Dim ilinx As New ADODB.Connection
Dim rsPorder As New ADODB.Recordset
Dim rsPorder2 As New ADODB.Recordset
Dim cmdLinx As New ADODB.Command
Dim strConn As String

Dim lvIli_number As String

'strConn = "DRIVER=SQL Native Client;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"

strConn = "DRIVER=SQL Server;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"
ilinx.Open strConn
Set cmdLinx.ActiveConnection = ilinx

lvIli_number = "12345"
cmdLinx.CommandText = "SELECT * FROM porder WHERE previous_ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1

rsPorder.Open cmdLinx, , 1, 1

If rsPorder.EOF Then

cmdLinx.CommandText = "SELECT * FROM porder WHERE ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1
rsPorder2.Open cmdLinx, , 1, 3

If rsPorder2.RecordCount > 0 Then

rsPorder2("Locked") = 0
rsPorder2.Update
End If

End If
rsPorder.Close

Hi,

did you trace the SQL Server with the profiler to see which command arrives at the server ? This would be very interesting to see if come commands arrive at the SQL Server level or not. The connection string you provided work for me with a .NET application an a ODBCConnection, so there has to be another problem with your connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi,

I just traced the server, but to be honest, I don't know, what to make out of it.
The last two steps are, which seem to represent the rsPorder2("Locked") = 0 bit.
declare @.p3 int
set @.p3=1
declare @.p4 int
set @.p4=1
exec sp_cursorfetch 180150003,256,@.p3 output,@.p4 output
select @.p3, @.p4

and

exec sp_cursor 180150003,32,1

I probably haven't been clear on this, but I can access the database with the Native Client just fine. Just when it comes to this particular update method, it's where I receive the exception described.

Best regards,
DD|||

Hi,

you should trace the information that is executed on the server while doing the Update method. Seems that the trace you posted here is something like a cursor move on the server. Otherwise I would open the post again for fellows which might already got in that problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just change the default CursorLocation into ilinx.CursorLocation = adUseClient and it will run correctly.|||

Thanks, I was having the very same problem and changing the Cursor Location to adUseClient works