🚀 go-pugleaf

I'm sorry for asking before looking into help, I've managed to do it via 
  ADOCommand, but now I've got other question regarding my previous post 
about inserting excel file into Table.

Since I'm using:

INSERT INTO MyTable(Col1, Col2)
SELECT Col1, Col2
FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]

and that works fine when I enter it into SQL property of ADOQuery, but 
I'll be inserting lets say 10 or more excel files into table, I'm 
thinking of using loop that will add

...""[Excel 8.0;Database=c:\myxls1.xls]
...""[Excel 8.0;Database=c:\myxls2.xls]
...""[Excel 8.0;Database=c:\myxls3.xls]
...

since there are two quatation marks in front of the brackets, I don't 
know how to apply them to my string correctly.

String SQL = "INSERT INTO MyTable(Col1, Col2)SELECT Col1, Col2 FROM 
[Sheet1$] IN \"\"";

for (int i= 0; i < someNumber; i++){
	ADOCommand1->CommandText= SQL + "[Excel 8.0;Database=c:\myxls" + 
IntToStr(i) +".xls]";
	ADOCommand1->Execute();
}

or

for (int i= 0; i < someNumber; i++){
	ADOQuer1->SQL->Add(SQL + "[Excel 8.0;Database=c:\myxls" + IntToStr(i) 
+".xls]";
	ADOQuery1->ExecSQL();
}


Both of them throw me an exception "Microsoft Jet database engine could 
not find object Sheet1..." is there some other way to solve this.

Again, thanks in advance,
Tomislav.

Tomislav Hundric' wrote:
> Hi,
> I've got a question regarding insert into table, I'm using access 
> database, ADOConnection and ADOQuery:
> 
> 
> I have SQL like this:
> 
> Insert into Table1 (Field1)
> Select distinct Field2
>  From Table2
> 
> Executing this query throws me this error
> 
> "current provider does not not support returning multiple recordsets 
> from simple execution"
> 
> When I look into my table I can see that records are added even though 
> an error was thrown. Basically what I'm trying to do is to fill 
> relational tables with data from one temp table. Is my approach ok, or 
> should I do this in some other way.
> 
> Thanks in advance,
> Tomislav.

Inside C++: ... Database=c:\\myxls ... . I.e. do you escape '\' character in 
path? Are you sure that enumeration starts from 0, not from 1, i.e. do you 
have file c:\myxls0.xls?

> ADOQuer1->SQL->Add(...)

Do you clear ADOQuery1->SQL before calling Add()?

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
���������: news:46ebc074$1@newsgroups.borland.com...
> I'm sorry for asking before looking into help, I've managed to do it via 
> ADOCommand, but now I've got other question regarding my previous post 
> about inserting excel file into Table.
>
> Since I'm using:
>
> INSERT INTO MyTable(Col1, Col2)
> SELECT Col1, Col2
> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>
> and that works fine when I enter it into SQL property of ADOQuery, but 
> I'll be inserting lets say 10 or more excel files into table, I'm thinking 
> of using loop that will add
>
> ...""[Excel 8.0;Database=c:\myxls1.xls]
> ...""[Excel 8.0;Database=c:\myxls2.xls]
> ...""[Excel 8.0;Database=c:\myxls3.xls]
> ...
>
> since there are two quatation marks in front of the brackets, I don't know 
> how to apply them to my string correctly.
>
> String SQL = "INSERT INTO MyTable(Col1, Col2)SELECT Col1, Col2 FROM 
> [Sheet1$] IN \"\"";
>
> for (int i= 0; i < someNumber; i++){
> ADOCommand1->CommandText= SQL + "[Excel 8.0;Database=c:\myxls" + 
> IntToStr(i) +".xls]";
> ADOCommand1->Execute();
> }
>
> or
>
> for (int i= 0; i < someNumber; i++){
> ADOQuer1->SQL->Add(SQL + "[Excel 8.0;Database=c:\myxls" + IntToStr(i) 
> +".xls]";
> ADOQuery1->ExecSQL();
> }
>
>
> Both of them throw me an exception "Microsoft Jet database engine could 
> not find object Sheet1..." is there some other way to solve this.
>
> Again, thanks in advance,
> Tomislav.
>
> Tomislav Hundric' wrote:
>> Hi,
>> I've got a question regarding insert into table, I'm using access 
>> database, ADOConnection and ADOQuery:
>>
>>
>> I have SQL like this:
>>
>> Insert into Table1 (Field1)
>> Select distinct Field2
>>  From Table2
>>
>> Executing this query throws me this error
>>
>> "current provider does not not support returning multiple recordsets from 
>> simple execution"
>>
>> When I look into my table I can see that records are added even though an 
>> error was thrown. Basically what I'm trying to do is to fill relational 
>> tables with data from one temp table. Is my approach ok, or should I do 
>> this in some other way.
>>
>> Thanks in advance,
>> Tomislav. 

Thanks Vassilev, I'm very distracted today, seems I didn't check for 
escape character in my path... so as you can guess it solved problem. I 
apologize for asking before I double checked my code...

Thanks,
Tomisalv


Vassiliev V. V. wrote:
> Inside C++: ... Database=c:\\myxls ... . I.e. do you escape '\' character in 
> path? Are you sure that enumeration starts from 0, not from 1, i.e. do you 
> have file c:\myxls0.xls?
> 
>> ADOQuer1->SQL->Add(...)
> 
> Do you clear ADOQuery1->SQL before calling Add()?
> 
> //------------------------------------------
> Regards,
> Vassiliev V. V.
> http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
> ADO.Net
> http://www.oledbdirect.com - The fastest way to access MS SQL Server,
> MS Jet (Access) and Interbase (through OLEDB)
> 
> 
> "Tomislav Hundric'" <rifnet@gmail.com> �������/�������� � �������� 
> ���������: news:46ebc074$1@newsgroups.borland.com...
>> I'm sorry for asking before looking into help, I've managed to do it via 
>> ADOCommand, but now I've got other question regarding my previous post 
>> about inserting excel file into Table.
>>
>> Since I'm using:
>>
>> INSERT INTO MyTable(Col1, Col2)
>> SELECT Col1, Col2
>> FROM [Sheet1$] IN ""[Excel 8.0;Database=c:\myxls.xls]
>>
>> and that works fine when I enter it into SQL property of ADOQuery, but 
>> I'll be inserting lets say 10 or more excel files into table, I'm thinking 
>> of using loop that will add
>>
>> ...""[Excel 8.0;Database=c:\myxls1.xls]
>> ...""[Excel 8.0;Database=c:\myxls2.xls]
>> ...""[Excel 8.0;Database=c:\myxls3.xls]
>> ...
>>
>> since there are two quatation marks in front of the brackets, I don't know 
>> how to apply them to my string correctly.
>>
>> String SQL = "INSERT INTO MyTable(Col1, Col2)SELECT Col1, Col2 FROM 
>> [Sheet1$] IN \"\"";
>>
>> for (int i= 0; i < someNumber; i++){
>> ADOCommand1->CommandText= SQL + "[Excel 8.0;Database=c:\myxls" + 
>> IntToStr(i) +".xls]";
>> ADOCommand1->Execute();
>> }
>>
>> or
>>
>> for (int i= 0; i < someNumber; i++){
>> ADOQuer1->SQL->Add(SQL + "[Excel 8.0;Database=c:\myxls" + IntToStr(i) 
>> +".xls]";
>> ADOQuery1->ExecSQL();
>> }
>>
>>
>> Both of them throw me an exception "Microsoft Jet database engine could 
>> not find object Sheet1..." is there some other way to solve this.
>>
>> Again, thanks in advance,
>> Tomislav.
>>
>> Tomislav Hundric' wrote:
>>> Hi,
>>> I've got a question regarding insert into table, I'm using access 
>>> database, ADOConnection and ADOQuery:
>>>
>>>
>>> I have SQL like this:
>>>
>>> Insert into Table1 (Field1)
>>> Select distinct Field2
>>>  From Table2
>>>
>>> Executing this query throws me this error
>>>
>>> "current provider does not not support returning multiple recordsets from 
>>> simple execution"
>>>
>>> When I look into my table I can see that records are added even though an 
>>> error was thrown. Basically what I'm trying to do is to fill relational 
>>> tables with data from one temp table. Is my approach ok, or should I do 
>>> this in some other way.
>>>
>>> Thanks in advance,
>>> Tomislav. 
> 
>