Pervasive SQL error when filling oledb data adaptor

I just finished adding oledb connectivity to an application,
I had to make it continue to work with the existing ODBC connections
I wrote and tested it on verisons 7.9 2000i and 8.6
now i am getting an error on a client who is using version 9.50.077.002

it is

Error in GetAList  on line 6 :   [LNA][Pervasive][ODBC Engine Interface]Invalid cursor position; no keyset defined.

here is my code: it works in other places but why is it blowing up here?>

   If UseOLEDB = True Then
6:              DRPMSoledb.Fill(DSPMS, "PMI")
            Else
7:              DRPMS.Fill(DSPMS, "PMI")
            End If

hainansyndromeAsked:
Who is Participating?
 
mirtheilConnect With a Mentor Commented:
Maybe.  What does your query look like?  Does it sort on a non-indexed field? Does the database have large VarChar fields (greater than 300 bytes)?
0
 
hainansyndromeAuthor Commented:
also getting this error spradically
 No error message available, result code: E_UNEXPECTED(0x8000FFFF). 3
0
 
mirtheilCommented:
I've seen the "Invalid cursor position; no keyset defined" error when executing an SQL statement that orders on a non-indexed field and has a VarChar field greater than 300 bytes.  

PSQL 9.50 was updated to PSQL 9.71.  It might be worth trying with the update.  
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
hainansyndromeAuthor Commented:
mirtheil I am working with a clients PC and they are not upgrading, so ordering on an indexed file will fix it you think?
0
 
hainansyndromeAuthor Commented:
it has in it 1 area, i changed it to remove the convert and SQL_Varchar and order on "Acct ID" but I am still getting that. error

Distinct rtrim(convert(""Acct ID"",SQL_Varchar)) as AccountID
0
 
hainansyndromeAuthor Commented:
I was in error i am getting another error
Distinct rtrim(convert(""Patient ID"",SQL_Varchar)) as PMPatientID


 [LNA][Pervasive][ODBC Engine Interface]Numeric value out of range.
[LNA][Pervasive][ODBC Engine Interface]Expression evaluation error.
0
 
mirtheilCommented:
Just to be clear, this application was working with PSQL ODBC but using OLEDB, you get these two error messages.  Is that correct?  All you did was change the connection string to use OLEDB (and use OleDb objects instead of ODBC objects), right?
0
 
hainansyndromeAuthor Commented:
yes thats it correct I Added the ability ti use an oledb connection string and data adaptor
0
 
hainansyndromeAuthor Commented:
once i too the convert id to a varchar it worked
i converted to an a string in code
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.