Trickster
asked on
Database connection VS .NET / SQL Server
I am trying out to create a database connected application using MS Visual Studio .NET.
I create a new project (MDI) and says that it should have database access.. I enter the name of the ODBC DNS and the database which
it should connect to and then create the project.. (ODBC DNS works perfectly - I can use it in Borland environment without problems).
Visula STudio creates the project and when running it with F5 it displays the default resource dialog form.. The problems occurs when I try to interact with the record set.
I put a CEDIT control on the form which I name IDC_EDIT_USERTYPE and my intention is to show the usertype field in that control.
In the view-class in the 'DoDataExchange(CDataExcha
' DDX_FieldText(pDX, IDC_EDIT_USERTYPE, (CString) m_pSet->m_user_type, m_pSet); (m_user_type is the variable which the record connects to that specific field in the recordset.
Ok, I run the app with F5 and this is where my two problems occur.
1: Almost every time I start the app for the first time I get a timeout error. The database is on (local) machine and I am connected to it through SQL server, so I know it's up. I also get this error sometimes after running the app a while.
2: The CEdit field displays a correct value from one row of the database. When I use "Next Record" in the toolbar I get an Assertion Error in dbcore.cpp in the method "void CRecordset::SetFieldNull(v
The call that creates the assertion failure is:
// If no field found, m_nFieldFound will still be zero
ASSERT(fx.m_nFieldFound != 0);
When I ignore the assertion or debug it but then continue, I get the next value in the correct CEDIT field, but every time I choose "Next record" I get the assertion failure, so I have to (in debug mode) choose ignore every single time..
Why? :)
dbcore.cpp is a system file and the only things I've added to the standard project created by Visual Studio are the CEdit control and the line in DoDataExchange..
I create a new project (MDI) and says that it should have database access.. I enter the name of the ODBC DNS and the database which
it should connect to and then create the project.. (ODBC DNS works perfectly - I can use it in Borland environment without problems).
Visula STudio creates the project and when running it with F5 it displays the default resource dialog form.. The problems occurs when I try to interact with the record set.
I put a CEDIT control on the form which I name IDC_EDIT_USERTYPE and my intention is to show the usertype field in that control.
In the view-class in the 'DoDataExchange(CDataExcha
' DDX_FieldText(pDX, IDC_EDIT_USERTYPE, (CString) m_pSet->m_user_type, m_pSet); (m_user_type is the variable which the record connects to that specific field in the recordset.
Ok, I run the app with F5 and this is where my two problems occur.
1: Almost every time I start the app for the first time I get a timeout error. The database is on (local) machine and I am connected to it through SQL server, so I know it's up. I also get this error sometimes after running the app a while.
2: The CEdit field displays a correct value from one row of the database. When I use "Next Record" in the toolbar I get an Assertion Error in dbcore.cpp in the method "void CRecordset::SetFieldNull(v
The call that creates the assertion failure is:
// If no field found, m_nFieldFound will still be zero
ASSERT(fx.m_nFieldFound != 0);
When I ignore the assertion or debug it but then continue, I get the next value in the correct CEDIT field, but every time I choose "Next record" I get the assertion failure, so I have to (in debug mode) choose ignore every single time..
Why? :)
dbcore.cpp is a system file and the only things I've added to the standard project created by Visual Studio are the CEdit control and the line in DoDataExchange..
ASKER
Hmm.. If I set a break at dbcore.cpp at the assertion failure and trace back through the call stack it's the DDX_FieldText(pDX, IDC_EDIT1, (CString) m_pSet->m_user_type, m_pSet); method that calls it. (In ConnectView::DoDataExchang
This is the only call I have in the DDoDataExchange method except for the initial CRecordView::DoDataExchang
In the DoFieldExchange method I only have 2 different bool fields and after removing both I still get the same error...
// T
This is the only call I have in the DDoDataExchange method except for the initial CRecordView::DoDataExchang
In the DoFieldExchange method I only have 2 different bool fields and after removing both I still get the same error...
// T
It's the DoFieldExchange() method that should have the correct field names. If you remove them, no fields are found and you will get more assertions. You may set a break point to the first RFX_... line of DoFieldExchange() . Then by stepping into the functions you will see the name required and perhaps you will find the missing or misspelled field name.
Could you give us the all table column names as shown by a database tool and the code of the DoFieldExchange() method?
Regards, Alex
Could you give us the all table column names as shown by a database tool and the code of the DoFieldExchange() method?
Regards, Alex
Don't forget the data types and SQL types of your fields. In SQL Server there is a NCHAR type that is a multibyte type and that isn't compatible to CRecordSet.
ASKER
Sure, but I can't find any differences in spelling or in case-sensitivity :-( I only ask for 6 values, and here they are:
SQL Server:
Name: Type: Length: Allows Null
--------------------------
user_id nvarchar 20 No
amount_left money 8 Yes
user_type nvarchar 15 No
date_created datetime 8 No
logged_on bit 1 No
free_time int 4 Yes
void CSidewalkDBConnectSet::DoF
{
pFX->SetFieldType(CFieldEx
// Macros such as RFX_Text() and RFX_Int() are dependent on the
// type of the member variable, not the type of the field in the database.
// ODBC will try to automatically convert the column value to the requested type
RFX_Text(pFX, _T("[user_id]"), m_user_id);
RFX_Double(pFX, _T("[amount_left]"), m_amount_left);
RFX_Text(pFX, _T("[user_type]"), m_user_type);
RFX_Date(pFX, _T("[date_created]"), m_date_created);
RFX_Bool(pFX, _T("[logged_on]"), m_logged_on);
RFX_Long(pFX, _T("[free_time]"), m_free_time);
}
And also for reference, the DoDataExchange method:
void CSidewalkDBConnectView::Do
{
CRecordView::DoDataExchang
// you can insert DDX_Field* functions here to 'connect' your controls to the database fields, ex.
// DDX_FieldText(pDX, IDC_MYEDITBOX, m_pSet->m_szColumn1, m_pSet);
// DDX_FieldCheck(pDX, IDC_MYCHECKBOX, m_pSet->m_bColumn2, m_pSet);
// See MSDN and ODBC samples for more information
DDX_FieldText(pDX, IDC_EDIT_USERTYPE, (CString) m_pSet->m_user_type, m_pSet);
}
SQL Server:
Name: Type: Length: Allows Null
--------------------------
user_id nvarchar 20 No
amount_left money 8 Yes
user_type nvarchar 15 No
date_created datetime 8 No
logged_on bit 1 No
free_time int 4 Yes
void CSidewalkDBConnectSet::DoF
{
pFX->SetFieldType(CFieldEx
// Macros such as RFX_Text() and RFX_Int() are dependent on the
// type of the member variable, not the type of the field in the database.
// ODBC will try to automatically convert the column value to the requested type
RFX_Text(pFX, _T("[user_id]"), m_user_id);
RFX_Double(pFX, _T("[amount_left]"), m_amount_left);
RFX_Text(pFX, _T("[user_type]"), m_user_type);
RFX_Date(pFX, _T("[date_created]"), m_date_created);
RFX_Bool(pFX, _T("[logged_on]"), m_logged_on);
RFX_Long(pFX, _T("[free_time]"), m_free_time);
}
And also for reference, the DoDataExchange method:
void CSidewalkDBConnectView::Do
{
CRecordView::DoDataExchang
// you can insert DDX_Field* functions here to 'connect' your controls to the database fields, ex.
// DDX_FieldText(pDX, IDC_MYEDITBOX, m_pSet->m_szColumn1, m_pSet);
// DDX_FieldCheck(pDX, IDC_MYCHECKBOX, m_pSet->m_bColumn2, m_pSet);
// See MSDN and ODBC samples for more information
DDX_FieldText(pDX, IDC_EDIT_USERTYPE, (CString) m_pSet->m_user_type, m_pSet);
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ahh.. Hmm.. In the declaration for the variables the wizard had chosen unicode CStringW for declaration of nvarchar variables - that's why I had a cast to (CString) in the DoDataExchange. When I let the ODBC driver convert it by declaring the var to CString instead and removing the cast it worked!!
Strange, but I am happy! :)
Thanks for the help
// T
Strange, but I am happy! :)
Thanks for the help
// T
One more comment:
CRecordSet uses ODBC to connect to your SQL Server Database. So, yo easily may change the database engine to MS ACCESS, ORACLE, DB2, MYSQL, and others. However, this only works if a you avoid to use non-compatible data types.
In your example above, the only data type i would recommend is 'int'. All others will not work on some of engines mentioned above. I have made a big system where i used only one type, VARCHAR, that is (almost) compatible to all systems, and it was a good decision because i never had problems with that.
So i would recommend NOT to use
- money (if you are lucky it will be converted to double elsewhere, but the precision will be
different from system to system)
- datetime (that is a really mess, because nearly none is compatible to any other. If you take
a string and store date-time in YYYYMMDDhhmmss you will be happy)
- bit (take an int - 1/0 - or string - 'T'/'F')
Regards, Alex
CRecordSet uses ODBC to connect to your SQL Server Database. So, yo easily may change the database engine to MS ACCESS, ORACLE, DB2, MYSQL, and others. However, this only works if a you avoid to use non-compatible data types.
In your example above, the only data type i would recommend is 'int'. All others will not work on some of engines mentioned above. I have made a big system where i used only one type, VARCHAR, that is (almost) compatible to all systems, and it was a good decision because i never had problems with that.
So i would recommend NOT to use
- money (if you are lucky it will be converted to double elsewhere, but the precision will be
different from system to system)
- datetime (that is a really mess, because nearly none is compatible to any other. If you take
a string and store date-time in YYYYMMDDhhmmss you will be happy)
- bit (take an int - 1/0 - or string - 'T'/'F')
Regards, Alex
Normally, you have entries like
RFX_Text(pFX, "Name", m_strName);
RFX_Bool(pFX, "DoILikeHer", m_bLikeHer);
in Wizard-generated code. However, one of these names couldn't be found in a RFX_Bool line.
Maybe there is a problem with case-sensitivity.
You should set a breakpoint to the line with the DoFieldExchange() call in dbcore.cpp and check the fx.m_pstr Parameter for the name of the required fields.
Regards, Alex