derived CRecordset field binding problem?

I have a view called 'view_worklist'  defined in MS SQL server that produces output columns of the following types:

int, uniqueidentifier, nvarchar(255), nvarchar(max), datetime, xml

In Visual C++ 6.0 class wizard, I created CWorklistRS derived from CRecordset.  I chose to bind all the columns and the wizard did its thing.  I then changed the member type from CString to CByteArray for each of the uniqueidentifier columns and replaced the RFX_Text macro with RFX_Binary for the same.

I then implemented GetDefaultSQL to return "select * from view_worklist where id=1"

Connection to the server works properly. I create an instance of CWorklistRS as follows:

            CWorklistRS rsWorklist(pDB);
            if( rsWorklist.Open( CRecordset::snapshot ))

however I end up getting an exception in:

LRESULT AFXAPI AfxCallWndProc(CWnd* pWnd, HWND hWnd, UINT nMsg,

The exception reads "Restricted data type attribute violation"
State:07006,Native:0,Origin:[Microsoft][SQL Native Client]
m_nRetCode = -1

Any ideas what I'm doing wrong? Is it perhaps because the data source is a view and not a table? If so how do I handle that?
Who is Participating?
itsmeandnobodyelseConnect With a Mentor Commented:
>>>> Is it perhaps because the data source is a view and not a table?
No, table or view is the same for ODBC.

I assume it is because of the nvarchar columns which were UNICODE text type. Is your app a also UNICODE?

I also wonder about nvarchar(max), datetime and xml. Do you really need these *strange* datatypes which I never heard of that they were directly supported by MFC recordsets.  Why not making simple fixed sized text and number fields especially if you have a view which might change the data type of the original fields.  
The ClassWizard will work to create a CRecordset-derived object from a database view as well as a table.

Howevver, there are several non-standard items in your view:
I don't know which of them is causing the problem. Try removing these and adding them back one at a time (creating a new CRecordset-derived object each time).

PMH4514Author Commented:
Hi Guys -
Yes I the app is compiled UNICODE.

Yes, regarding uniqueidentifier, nvarchar(max), xml  - I am brainstorming reworking those.. They are appropriate data-types within the grand scheme of the database and web application front-end.. Well, uniqueidentifier is as all records using them need to be unique across "space and time".. nvarchar(max) is actually a typo, it's specified as nvarchar(2000), sorry about that.. and XML - how else would I store a large chunk of XML code Tthe text field type if I recall doesn't concatenate well in TSQL code with the + operator which is something a stored procedure is doing.

Anyway,  The C++ application in question is just a small DLL that is running as an agent more or less and is the only MFC piece of the larger architecture and it only looks at this single view, so I'd hate to use less appropriate data-types in the database schema merely to support such a small piece of the overall picture..

itsmeandnobodyelse wrote:
>>especially if you have a view which might change the data type of the original fields.  

Interesting.. It did not occur to me to have the view change the data type of the original fields.. That would be implemented with CAST or CONVERT within the SQL code that built the view? That's good food for though that might solve all of this..  thanks.

That said, I did go and remove  the "strange" type references, XML and uniqueidentifier, so now the CRecordset derived class is only for integer, nvarchar(2000), nvarchar(255) and datetime and I received the same exception "Restricted data type attribute violation" when trying to open it.

So then I removed the datetime type, and still have the same problem.

Any thoughts?

I'm going to try as well to cast everything, in the view, to strings to see if I can make it play nice with MFC.

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

PMH4514Author Commented:
EDIT - actually, I take that last comment back.. Once I removed the dataetime type, while I did still receive an exception, it was not the restricted data type exception, rather, "Invalid character value for cast specification"

Perhaps it has a problem with null fields?
>>>> Once I removed the dataetime type,
Yes datetime unfortunately is still something special as it varies between databases. Here you convert it to a - say 19 chars - string field in your view and it should work.

>>>> "Invalid character value for cast specification"
It was 10 years ago and SQL Server firstly comes with nvarchar instead of varchar2. I mean to remember we had similar problems. Look at your RecordExchange member function what macros it used for the nvarchar2. If it is RfxText it is probably wrong cause as far as I know RfxText is for ANSI strings only. I don't know exactly whether VC6 was providing a Rfx macro for UNICODE. If not, you would need to write it yourself, probably. To these times I had my own ODBC recordset class and it wasn't so much difficult to support UNICODE strings.  
PMH4514Author Commented:
itsmeandnobodyelse - I am going to accept your original response as the solution, specifically:

" Why not making simple fixed sized text and number fields especially if you have a view which might change the data type of the original fields.  "

I was not aware that I could CONVERT in the query that assembles the view. I did so, making everything nvarchar types. I made the necessary changes in my MFC code and now it all works perfectly.

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.