Yes,
had problem in setting .source. I don't know if it's a bug or not and honestly didn't even think to use ADODB in .Net until seeing your question.
Cetin
>Cetin, thanks.
>So, you too have a problem with setting the recordset.Source .
>What do you think, is it a bug in VB.NET, or we are missing here something?
>
>
>>Yuri,
>>Still I think that string is a typo (I can use a single quote as Note,no?). However there is a problem with .Source. This works:
>>
>> Dim ExcelFile As String
>> Dim cnnExcel As ADODB.Connection
>> Dim rstExcel As ADODB.Recordset
>>
>> ExcelFile = "c:\temp\customer.xls"
>> cnnExcel = CreateObject("adodb.Connection")
>> cnnExcel.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _
>> Excelfile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';")
>>
>> ' Open recordsets
>> rstExcel = New ADODB.Recordset
>>
>> rstExcel.ActiveConnection = cnnExcel
>> rstExcel.CursorLocation = ADODB.CursorLocationEnum.adUseClient
>> rstExcel.CursorType = ADODB.CursorTypeEnum.adOpenStatic
>> rstExcel.LockType = ADODB.LockTypeEnum.adLockReadOnly
>> rstExcel.Open("select * from [customer$]")
>>
>> 'rstExcel.Open(strSelect, cnnExcel, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, -1)
>>
>>>>
>>
>>>Cetin,
>>>
>>>"+" may be used here in VB, though "&" is recommended. Anyway, for both syntaxes there are no errors when open connection, but still the error when open the recordset.
>>>
>>>
>>>
>>>>Yuri,
>>>>I'm really confused about what's not working.
>>>>I don't know VB but wouldn't it be a & instead of + for concatenation? Quotes do match - sounds like there is an extra one before ExcelFile.
>>>>Cetin
>>>>
>>>>>Cetin,
>>>>>
>>>>>The problem is not with the Adapter, it works. I would prefer to follow the existing working code in VB/VFP where ADO recordset is used. If it works there, then why it does not work in VB.NET?
>>>>>
>>>>>cnnExcel = CreateObject("adodb.Connection")
>>>>>cnnExcel.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _
>>>>> ' Excelfile + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';")
>>>>>
>>>>>....
>>>>>' Open recordsets
>>>>>Dim rstExcel As ADODB.Recordset
>>>>>rstExcel = New ADODB.Recordset
>>>>> With rstExcel
>>>>> .ActiveConnection = cnnExcel
>>>>> .CursorLocation = ADODB.CursorLocationEnum.adUseClient
>>>>> .CursorType = ADODB.CursorTypeEnum.adOpenStatic
>>>>> .LockType = ADODB.LockTypeEnum.adLockReadOnly
>>>>>
>>>>>* the line below gives an error
>>>>> .Source = "SELECT * from [Matched$]"
>>>>>....
>>>>>
>>>>>
>>>>>
>>>>>The error is:
>>>>>VB Error # 3001 was generated by ADODB.Recordset
>>>>>Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
>>>>>
>>>>>Cannot figure out why it works in VB and VFP, but does not work in VB NET, and what kind of arguments are not accepted.
>>>>>
>>>>>Any ideas of where to look into?
>>>>>
>>>>>
>>>>>>
>>>>>>Yuri,
>>>>>>Both versions are working for me:
>>>>>>
>>>>>> string strSQL = "select * from [customer$]";
>>>>>> string strCon = @"Provider=Microsoft.Jet.OLEDB.4.0;
>>>>>> Data Source=c:\temp\customer.xls;
>>>>>> Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
>>>>>>
>>>>>>// OleDbConnection con = new OleDbConnection(strCon);
>>>>>>// OleDbDataAdapter da = new OleDbDataAdapter(strSQL, con);
>>>>>>
>>>>>> OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strCon);
>>>>>> DataSet ds = new DataSet();
>>>>>> da.Fill(ds);
>>>>>> foreach(DataRow dr in ds.Tables[0].Rows)
>>>>>> {
>>>>>> Console.WriteLine( "{0} {1}",dr["cust_id"], dr["contact"] );
>>>>>> }
>>>>>>
>>>>>>Cetin
