Solved

derived CRecordset field binding problem?

Posted on 2008-10-30
6
1,104 Views
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?
0
Comment
Question by:PMH4514
  • 3
  • 2
6 Comments
 
LVL 39

Accepted Solution

by:
itsmeandnobodyelse earned 250 total points
Comment Utility
>>>> 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.  
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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:
uniqueidentifier
nvarchar(max)
xml
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).

0
 

Author Comment

by:PMH4514
Comment Utility
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.

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:PMH4514
Comment Utility
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?
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
Comment Utility
>>>> 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.  
0
 

Author Comment

by:PMH4514
Comment Utility
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.

thanks!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Introduction: Dialogs (1) modal - maintaining the database. Continuing from the ninth article about sudoku.   You might have heard of modal and modeless dialogs.  Here with this Sudoku application will we use one of each type: a modal dialog …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now