|
Hi Sangeetha,
You cannot use CDatabase::ExecuteSQL for SELECT query. It is only for action query like UPDATE, DELETE and INSERT.
You need a CRecordset object to get the result of a SELECT query.
|
|
|
|
|
Hi Ismail,
Can you tell how to use it..?
CRecordset* record;
record = new CRecordset(&database);
record -> m_strSort = "DUT_PIN ASC";
record -> Open(CRecordset::snapshot, "Pin_List");
recset -> Close();
I used the codings above. But error is coming..
With Regards,
Sangeetha.
|
|
|
|
|
|
WhiteSky, the error is coming while the control goes to BuildSelectSQL() function of CRecordset class. While its coming to the ASSERT(m_nFields != 0) line, its showing the Abort, Retry, Cancel message.
With Regards,
Sangeetha.
|
|
|
|
|
CFileDialog MyFileDialog( FALSE, _T("xls"), NULL, OFN_ALLOWMULTISELECT | OFN_HIDEREADONLY, _T("Access Files (*.xls)|*.xls|(*.xls)|*.xls|all files(*.*)|*.xls||"),this);
MyFileDialog.m_ofn.lpstrTitle = "Save As";
if(MyFileDialog.DoModal() == IDOK)
{
FileName = MyFileDialog.GetPathName();
}
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager
CString sExcelFile = FileName;
CString sSql;
CString str1;
TRY
{
// Build the creation string for access without DSN
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", sDriver,sExcelFile,sExcelFile);
// Create the database (i.e. Excel sheet)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
// Create table structure
float j;
CPinListSet1* recset;
recset = new CPinListSet1(&database);
recset->Open(NULL,NULL,NULL,"'TABLE','SYSTEM TABLE'",CRecordset::snapshot);
CString TableName, sSql;
TableName = "\0";
if(!recset -> IsEOF())
{
recset->MoveFirst();
while(!recset->IsEOF())
{
TableName = "[" + recset->m_strTableName + "]";
recset->MoveNext();
}
}
recset->Close();
if(TableName == "[Pin_List]")
{
sSql = "DROP TABLE Pin_List";
database.ExecuteSQL(sSql);
}
sSql = "CREATE TABLE Pin_List (NET_NAME TEXT,DUT_PIN TEXT,TESTER_CHANNEL TEXT,TRACE_LENGTH float)";
database.ExecuteSQL(sSql);
int ResultSize = aResult_Array.GetSize();
for(int i = 0; i < ResultSize; i++)
{
CResult* Result = (CResult*) aResult_Array.GetAt(i);
// Insert data
CString Sql("INSERT INTO Pin_List (NET_NAME,DUT_PIN,TESTER_CHANNEL,TRACE_LENGTH) VALUES (");
Sql = Sql;
str1 = Result -> NetName;
Sql = Sql + "'" + str1 + "',";
str1 = Result -> DutName;
Sql = Sql + "'" + str1 + "',";
str1 = Result -> TesterName;
if( (ExportUnit == INCH) || (ExportUnit == MM) )
Sql = Sql + "'" + str1 + "'," + "%.4f)";
else
Sql = Sql + "'" + str1 + "'," + "%.2f)";
j = (float) (Result -> TraceLength * Factor);
sSql.Format(Sql,j);
database.ExecuteSQL(sSql);
}
CRecordset* record;
record = new CRecordset(&database);
record -> Close();
record -> m_strSort = "DUT_PIN ASC";
record -> Open(CRecordset::snapshot, "Pin_List");
UpdateData(FALSE);
}
// Close database
if(database.IsOpen())
database.Close();
}
CATCH_ALL(e)
{
TRACE1("Driver not installed: %s",sDriver);
}
END_CATCH_ALL;
The above are the coding lines i used to create the excel file.
With Regards,
Sangeetha.
|
|
|
|
|
Hi Whitesky,
Why no reply from you..?
Sangeetha.
|
|
|
|
|
Your error is in this line recset->Open(NULL,NULL,NULL,"'TABLE','SYSTEM TABLE'",CRecordset::snapshot);,right?
|
|
|
|
|
No Whitesky.. That recset instance is of one more derived class's instance.. My error is in the line,
record -> Open(CRecordset::snapshot, "Pin_List");
No problem in creating the excel sheet.. Its getting created.. But when i am trying to sort through the CRecorset's object, i am getting the error, Whitesky.
Thank you a lot for finding out this thread and replying..
Thank you very much.
With Regards,
Sangeetha.
|
|
|
|
|
Expecting the reply..
With Regards,
Sangeetha.
|
|
|
|
|
You dont need to say Thank you.I am here for help to people .I run your code and I saw that your problem was in the line that you want to open recset->Open(NULL,...) and this error was on two parameter that needs to a statement sql for example recset->Open(NULL,"Select * from a"); in previous thread you said you got ASSERT(m_nFields != 0) and I checked for it but for this problem did you use of debugger for see error
|
|
|
|
|
Whitesky,
We derived the class named CPinList1 from CRecordset and we wrote the function Open(). Thats what i said there is no error in the recset->Open(NULL,NULL,NULL,"'TABLE','SYSTEM TABLE'",CRecordset::snapshot);. And thats why its possible to open an excel file and write the data into it. IF there is an error means, i could not open the excel file. But if i comment that record -> Open() line means, i can open the excel file and see the contents into it.. But the contents wont be in sorted order.. So as to sort that, i wrote the following lines,
CRecordset* record;
record = new CRecordset(&database);
record -> Close();
record -> m_strSort = "DUT_PIN ASC";
record -> Open(CRecordset::snapshot, "Pin_List");
UpdateData(FALSE);
So i thought the error is from the above lines. I had debugged and seen it.
The thing is the value of the m_nFields is 0. Thats the error.
With Regards,
Sangeetha.
|
|
|
|
|
Whitesky, you checked with my post..?
With Regards,
Sangeetha.
|
|
|
|
|
Have you seen my, reply whitesky..?
With Regards,
Sangeetha.
|
|
|
|
|
Yes I saw you can open database but your problem is on sort did you use of debugger for see detaily
|
|
|
|
|
Yes WhiteSky, i used the debugger and saw detaily.. When the control comes to the line, record -> Open(CRecordset::snapshot, "Pin_List"); it goes into the CRecordset::Open function and when it reaches the line BuildSQL() line it goes into that function, and then when it reaches the line BuildSelectSQL() line it goes into it . In that BuildSelectSQL() function, there is a line ASSERT(m_nFields!=0); When the control reaches this line, it displaying the Abort message box.. Its telling that the error is in the file dbCore.cpp and in the line 3282. You please analyse this. Or please suggest me some other way, WhiteSky.. Thank You.
With Regards,
Sangeetha.
|
|
|
|
|
That assertion error is happening since the m_nFields value is 0. The excel sheet have been created already. I didnt know how come the no of fields value equals zero.
With Regards,
Sangeetha.
|
|
|
|
|
|
Whitesky,
I go through the link referred by you, and tried as following,
void CPinListGeneratorDlg::ExcelOutPut()
{
CFileDialog MyFileDialog( FALSE, _T("xls"), NULL, OFN_ALLOWMULTISELECT | OFN_HIDEREADONLY, _T("Access Files (*.xls)|*.xls|(*.xls)|*.xls|all files(*.*)|*.xls||"),this);
MyFileDialog.m_ofn.lpstrTitle = "Save As";
if(MyFileDialog.DoModal() == IDOK)
{
FileName = MyFileDialog.GetPathName();
}
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager
CString sExcelFile = FileName;
CStr
ing sSql;
CString str1;
TRY
{
// Build the creation string for access without DSN
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", sDriver,sExcelFile,sExcelFile);
// Create the database (i.e. Excel sheet)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
// Create table structure
float j;
CPinListSet1* recset;
recset = new CPinListSet1(&database);
recset->Open(NULL,NULL,NULL,"'TABLE','SYSTEM TABLE'",CRecordset::snapshot);
CString TableName, sSql;
TableName = "\0";
if(!recset -> IsEOF())
{
recset->MoveFirst();
while(!recset->IsEOF())
{
TableName = "[" + recset->m_strTableName + "]";
recset->MoveNext();
}
}
recset->Close();
if(TableName == "[Pin_List]")
{
sSql = "DROP TABLE Pin_List";
database.ExecuteSQL(sSql);
}
sSql = "CREATE TABLE Pin_List (NET_NAME TEXT,DUT_PIN TEXT,TESTER_CHANNEL TEXT,TRACE_LENGTH float)";
database.ExecuteSQL(sSql);
int ResultSize = aResult_Array.GetSize();
for(int i = 0; i < ResultSize; i++)
{
CResult* Result = (CResult*) aResult_Array.GetAt(i);
// Insert data
CString Sql("INSERT INTO Pin_List (NET_NAME,DUT_PIN,TESTER_CHANNEL,TRACE_LENGTH) VALUES (");
Sql = Sql;
str1 = Result -> NetName;
Sql = Sql + "'" + str1 + "',";
str1 = Result -> DutName;
Sql = Sql + "'" + str1 + "',";
str1 = Result -> TesterName;
if( (ExportUnit == INCH) || (ExportUnit == MM) )
Sql = Sql + "'" + str1 + "'," + "%.4f)";
else
Sql = Sql + "'" + str1 + "'," + "%.2f)";
j = (float) (Result -> TraceLength * Factor);
sSql.Format(Sql,j);
database.ExecuteSQL(sSql);
}
}
// Close database
if(database.IsOpen())
database.Close();
}
CATCH_ALL(e)
{
TRACE1("Driver not installed: %s",sDriver);
}
END_CATCH_ALL;
sSql.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, sExcelFile);
TRY
{
// Open the database using the former created pseudo DSN
database.Open(NULL, false, false, sSql);
// Allocate the recordset
// Allocate the recordset
CRecordset record(&database);
// Build the SQL string
// Remember to name a section of data in the Excel sheet using
// "Insert->Names" to be able to work with the data like you would
// with a table in a "real" database. There may be more than one table
// contained in a worksheet.
sSql = "SELECT NET_NAME, DUT_PIN, TESTER_CHANNEL, TRACE_LENGTH "
"FROM Pin_List "
"ORDER BY DUT_PIN";
// Execute that query (implicitly by opening the recordset)
record.Open(CRecordset::snapshot, sSql);
database.Close();
}
CATCH(CDBException, e)
{
// A database exception occured. Pop out the details...
AfxMessageBox("Database error: " + e->m_strError);
}
END_CATCH;
}
Am getting the error message "No columns were bound to prior calling SQLFetchScroll/SQLExtendedFetch".
With Regards,
Sangeetha.
|
|
|
|
|
I think your problem maybe of excel file if you run this example it work did you try this article with itself example?I think in your excel file you you have row and col but do you have table in this file.
|
|
|
|
|
Whitesky,
You please once again go through my Coding.. In that, i have created a table using the SQL Query "CREATE TABLE".. As well as i opened the Excel File created through my project and check with that.. I went to the Menu "Insert -- Name -- Define" and go through it.. There is a table created by the name "Pin_List", which representing all the rows and columns that are created..
Sorry for you troubling you again,
Sorry..
With Regards,
Sangeetha.
|
|
|
|
|
I think "Insert -- Name -- Define" on excel is for grouping,Right?I suggest on the excel see Menu Data->Table or Data-PivotTable.I guess your problem is in excel file excel if you saw previous link on excel file he declare columns with Table name.
|
|
|
|
|
Whitesky,
Even in there exe, the sorted result is coming only in the listbox of the dialog. And it is not getting updated in that "Readexcel.xls" file.. You please run that exe and check it out.
With Regards,
Sangeetha.
|
|
|
|
|
yes I saw did you see excel file in this project its strucutre is same with your excel
|
|
|
|
|
Yes.. But they are sorting the file that is created already.. But in my case, in that function am creating the excel file.. then i need to sort..
With Regards,
Sangeetha.
|
|
|
|
|
Is it correct? (1)you have a excel file(you have items and also table on it)(2)you can open this file(3)you can read of it(4)but you can sort items (or better you get runtime error) if you dont use of m_recordset.m_strSort what happens it work or doesnt work
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
