Query Table and SQL Server
 

Hi All,

I am trying to populate a spreadsheet from SQL Server by creating a qyery
table and using a select statement in C# using Excel 11. My code looks like
this:

string sConn = @"Provider=SQLOLEDB.1;Password=pwd;Persist Security
Into=True;User ID=sa;Initial Catalog=DB1;Data Source=Server1";
ADODB.ConnectionClass oConn = new ADODB.ConnectionClass();
oConn.ConnectionString = sConn;

string sQuery = "SELECT * from MyTable";
Excel.Range oRange = oSheet.get_Range("A1",oMissing);

Excel.QueryTables oQryTbls = oSheet.QueryTables;
// I have tried
// Excel.QueryTable oQryTbl = oSheet.QueryTables.Add(oConn, oRange,sQuery);
// also

Excel.QueryTable oQryTbl = oQryTbls.Add(sConn, oRange,sQuery);
// I have tried
// Excel.QueryTable oQryTbl = oQryTbls.Add(oConn, oRange,sQuery);
// also
oQryTbl.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
oQryTbl.Refresh(false);


I get an unhandled exception of type
System.Runtime.InteropServices.COMException in mscorlib.dll
The HRESULT value is 0x800A03EC.

Can anyone point me in the right direction ?

TIA
Peter