CRecordset.Close() causes exception

Hi,
I'm having problems with VC++ 6.0 reading an Access 2000 database.
CRecordset::Close() throws an exception. I've created a simple test
dialog which does an open and close of a CRecordset. The database has
one table called 'Addresses' in it. The CAddressSet was created by the
Class Wizard.
void CDatabaseDlg::OnOK()
{
CDatabase db;
db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
// Construct the snapshot object
CAddressSet rsAddr( NULL );
// Set options if desired, then open the recordset
rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
rsAddr.Close( );
db.Close( );
Dialog::OnOK();
}
The Close method throws on the AFX_SQP_SERVER line below:
RETCODE nRetCode;
if (m_hstmt != SQL_NULL_HSTMT)
{
AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
m_hstmt = SQL_NULL_HSTMT;
}
This program just couldn't get any simpler - and yet it doesn't work!
Anybody got any clue as to why?
Many thanks,
Jeremy
Recordset is not connected to database? Recordset is completely empty?
etc?
Free software - Baxter Codeworks www.baxcode.com
"Jezzer" <Jemtaylor@.yahoo.com> wrote in message
news:1115894836.839503.104060@.g14g2000cwa.googlegr oups.com...
> Hi,
> I'm having problems with VC++ 6.0 reading an Access 2000 database.
> CRecordset::Close() throws an exception. I've created a simple test
> dialog which does an open and close of a CRecordset. The database has
> one table called 'Addresses' in it. The CAddressSet was created by the
> Class Wizard.
> void CDatabaseDlg::OnOK()
> {
> CDatabase db;
> db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
> // Construct the snapshot object
> CAddressSet rsAddr( NULL );
> // Set options if desired, then open the recordset
> rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
>
> rsAddr.Close( );
> db.Close( );
>
> Dialog::OnOK();
> }
> The Close method throws on the AFX_SQP_SERVER line below:
> RETCODE nRetCode;
> if (m_hstmt != SQL_NULL_HSTMT)
> {
> AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
> m_hstmt = SQL_NULL_HSTMT;
> }
> This program just couldn't get any simpler - and yet it doesn't work!
> Anybody got any clue as to why?
> Many thanks,
> Jeremy
>
|||Yes, I see why it might look like that with the NULL in the CRecordSet
declaration. I've defined my test database using ODBC manager and the
GetDefaultConnect() method looks like this:
CString CAddressSet::GetDefaultConnect()
{
return _T("ODBC;DSN=db1");
}
where 'db1' is my database with the Addresses table in it. This was all
put there by the wizard.
I have had this same test retrieving the data succesfully but I took
that stuff out to make the example less cluttered.
It's just the Close that crashes whether you retrieve stuff or not.
cheers anyway...
Jeremy
|||Hi all,
I've made some progress with this problem now. I've discovered that
CRecordSet::Close() only crashes when running under Visual Studio -
when the exe is run stand-alone it works fine. I can remove the 'fudge'
and it loads the DB Ok.
The 'fudge' I use where I set m_hstmnt = SQL_NULL_HSTMT in the
::Close() method means that when debugging the method does not crash -
but it seems to cause the Access interface to run *extremely* slowly.
No CPU being used - just seems to be waiting an awful lot. So I suspect
that whatever is being freed by the 'SQLFreeStmt' call is getting
clogged. My hunch is that Access or ODBC is waiting for a space in some
buffer to open up and eventually decides to remove the oldest 'thing' -
whatever that is.
A search of Google Groups shows that lots of people have had this
problem - is there nobody out there that knows the answer?
cheers
Jeremy

CRecordset.Close() causes exception

Hi,
I'm having problems with VC++ 6.0 reading an Access 2000 database.
CRecordset::Close() throws an exception. I've created a simple test
dialog which does an open and close of a CRecordset. The database has
one table called 'Addresses' in it. The CAddressSet was created by the
Class Wizard.
void CDatabaseDlg::OnOK()
{
CDatabase db;
db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
// Construct the snapshot object
CAddressSet rsAddr( NULL );
// Set options if desired, then open the recordset
rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
rsAddr.Close( );
db.Close( );
Dialog::OnOK();
}
The Close method throws on the AFX_SQP_SERVER line below:
RETCODE nRetCode;
if (m_hstmt != SQL_NULL_HSTMT)
{
AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
m_hstmt = SQL_NULL_HSTMT;
}
This program just couldn't get any simpler - and yet it doesn't work!
Anybody got any clue as to why'
Many thanks,
JeremyRecordset is not connected to database? Recordset is completely empty?
etc?
----
Free software - Baxter Codeworks www.baxcode.com
----
"Jezzer" <Jemtaylor@.yahoo.com> wrote in message
news:1115894836.839503.104060@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm having problems with VC++ 6.0 reading an Access 2000 database.
> CRecordset::Close() throws an exception. I've created a simple test
> dialog which does an open and close of a CRecordset. The database has
> one table called 'Addresses' in it. The CAddressSet was created by the
> Class Wizard.
> void CDatabaseDlg::OnOK()
> {
> CDatabase db;
> db.Open(NULL,FALSE,FALSE,"DSN=c:\\dev\\db1.mdb");
> // Construct the snapshot object
> CAddressSet rsAddr( NULL );
> // Set options if desired, then open the recordset
> rsAddr.Open(CRecordset::snapshot, NULL, CRecordset::none );
>
> rsAddr.Close( );
> db.Close( );
>
> Dialog::OnOK();
> }
> The Close method throws on the AFX_SQP_SERVER line below:
> RETCODE nRetCode;
> if (m_hstmt != SQL_NULL_HSTMT)
> {
> AFX_SQL_SYNC(::SQLFreeStmt(m_hstmt, SQL_DROP));
> m_hstmt = SQL_NULL_HSTMT;
> }
> This program just couldn't get any simpler - and yet it doesn't work!
> Anybody got any clue as to why'
> Many thanks,
> Jeremy
>|||Yes, I see why it might look like that with the NULL in the CRecordSet
declaration. I've defined my test database using ODBC manager and the
GetDefaultConnect() method looks like this:
CString CAddressSet::GetDefaultConnect()
{
return _T("ODBC;DSN=db1");
}
where 'db1' is my database with the Addresses table in it. This was all
put there by the wizard.
I have had this same test retrieving the data succesfully but I took
that stuff out to make the example less cluttered.
It's just the Close that crashes whether you retrieve stuff or not.
cheers anyway...
Jeremy|||Hi all,
I've made some progress with this problem now. I've discovered that
CRecordSet::Close() only crashes when running under Visual Studio -
when the exe is run stand-alone it works fine. I can remove the 'fudge'
and it loads the DB Ok.
The 'fudge' I use where I set m_hstmnt = SQL_NULL_HSTMT in the
::Close() method means that when debugging the method does not crash -
but it seems to cause the Access interface to run *extremely* slowly.
No CPU being used - just seems to be waiting an awful lot. So I suspect
that whatever is being freed by the 'SQLFreeStmt' call is getting
clogged. My hunch is that Access or ODBC is waiting for a space in some
buffer to open up and eventually decides to remove the oldest 'thing' -
whatever that is.
A search of Google Groups shows that lots of people have had this
problem - is there nobody out there that knows the answer?
cheers
Jeremy

Creating excel file from DTS package

Hi,

I have a created a DTS packges which is reading data from sql server table, manipulate this data as required and then create a text file with that data. I created the text file using FileSystemObject. I was writing one field at a time to the text file.

I need to same thing but instead of creating text file, I need to create a excel file with each column from database going to separate column in excel sheet. I tried to do this with FileSystemObject, but it was wrting all the columns from database to one cell in excel sheet. How can I fix this problem?

Thanks!Am I missing something or can't you just create a data transformation task going from your database to a excel spreadsheet?

Create a conenction to a spreadsheet and a connection to a database and build your transformation. DTS will take care of everything else. If you need to play with filesnames etc you can do that after the package has been created (using the filesystem object)|||is there any sample to show that how to ceate an excel file after reading data from sql server?

Thanks|||start>programs>Microsoft SQL Server>Import and Export Data

follow the wizard. select your db>Select your output file (excel)>hit next>run immediately>done|||Sorry, I didn't explain my question properly. I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.

Thanks!|||I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.

Thanks!|||I 've create a template with the functions for addign totals, etc already built in. using VB SCRIPT, copy the template and populate it every time you run the DTS package.|||Could you please explain how can I create this template and copy it every time I need this?

Thanks|||'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim objFSO, objFileTemplate, strFile

Set objFSO = CreateObject ( "Scripting.FileSystemObject" )
strFile = DTSGlobalVariables("strRootPath").Value & "Template.xls"
Set objFileTemplate = objFSO.GetFile ( strFile )
strMonth = Right ( "0" & DatePart ( "m", DTSGlobalVariables("dtStart").Value ), 2 )
strFile = DTSGlobalVariables("strPath").Value & _
DatePart ( "yyyy", DTSGlobalVariables("dtStart").Value ) & _
strMonth & _
" - " & _
DTSGlobalVariables("ID").Value & " " & DTSGlobalVariables("Id").Value & _
".xls"
DTSGlobalVariables("FileName").Value = strFile

If ( objFSO.FileExists ( strFile ) ) Then
Main = DTSTaskExecResult_Failure
Exit Function
End If

objFileTemplate.Copy strFile, 0

Main = DTSTaskExecResult_Success

End Function

Note: This is basically the code. You have a template in a directory. This vbscrip takes the template, copies it to a different directory. The name is obtained by using the 'Id' field and concatenating month and year.

If you want, we can take this offline, just let me know your email address.