derived CRecordset field binding problem?

Posted on 2008-10-30
Last Modified: 2013-11-20
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?
Question by:PMH4514
  • 3
  • 2
LVL 39

Accepted Solution

itsmeandnobodyelse earned 250 total points
ID: 22848255
>>>> 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.  
LVL 49

Expert Comment

ID: 22848522
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).


Author Comment

ID: 22850272
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Author Comment

ID: 22850353
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?
LVL 39

Expert Comment

ID: 22850790
>>>> 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.  

Author Comment

ID: 22851053
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.


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question