January 23, 2002 at 12:48 am
Dear All,
When I am trying to query a table from my VB application with ADO connection I am getting the following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cursor open failed because the size of the keyset row exceeded maximum allowed row size.
The code in VB I am using like this:
---------------------------------------------
table_name="myTable"
If rsRecords.State = adStateOpen Then rsRecords.Close
rsRecords.Open "select * from " & table_name, con, adOpenStatic, adLockOptimistic, adCmdText
MsgBox Err.Description
If rsRecords.RecordCount <> 0 Then
If (MsgBox(table_name & " Already contains " & rsRecords.RecordCount & " Records! Add to existing Records??", vbYesNo, "Confirm") = vbYes) Then
insert_data
Else
exit sub
End if
--------------------------------------------
I tried with other connection types than Static one. But, for those I am getting '-1' in the recordcount. I should get the positive or zero recordcount for checking the existing no of records.
Is there any possible way or alternative??
Thanx in advance
January 23, 2002 at 10:29 am
Isnt the problem with the row size, not some other issue? Try change the select * to select specific column names and see what happens.
Andy
January 24, 2002 at 3:47 am
I tried specifying the columns also. But, that did not help.
Anyway I could solve the problem by specifying the cursor location before opening the recordset in combination with ForwardOnly.
But, still I could not get why the hell that error comes????????? Any Ideas???
Solution code is like following:
---------------------------------------------
table_name="myTable"
If rsRecords.State = adStateOpen Then rsRecords.Close
rsRecords.CursorLocation = adUseClient
rsRecords.Open "select * from " & table_name, con, adOpenForwardOnly, adLockOptimistic, adCmdText
MsgBox Err.Description
If rsRecords.RecordCount <> 0 Then
If (MsgBox(table_name & " Already contains " & rsRecords.RecordCount & " Records! Add to existing Records??", vbYesNo, "Confirm") = vbYes) Then
insert_data
Else
exit sub
End if
--------------------------------------------
ThanQ for your comments and responses
January 24, 2002 at 9:40 am
Strange. No idea, but thanks for the followup.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 24, 2002 at 1:25 pm
Will try to look into when I have time - chasing a deadline this week!
Andy
January 29, 2002 at 3:01 am
friends!
still I am getting the same error...not at the same query but at some other query.
This time the old technic of specifying the cursor type is also not working.
can somebody look in to it?????????
February 4, 2002 at 3:24 pm
Are you sure the error is being thrown exactly where the MsgBox is coming up. If you are using On Error Resume Next it could be occurring lines before. Keep in mind err remains constant until set by another error or you set to 0. If you are using On Error Resume Next comment out and see what errors you may be missing along the way. Or do an On Error Goto and keep track of the line number so you can show in the message box. Finally, please post the code snippet you are having an issue with once you are sure there is no other underlying issue.
February 4, 2002 at 3:46 pm
If you really want to debug the error, please post your complete connection string (less login and password of course) and recordset property settings. The error number might also be helpful. (err.Number)
It appears this code is checking to see if there are records in the table. Rather than doing
Select *
Have you tried Select Count(*)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply