Re: Thanks, but it doesn't work with .Net

From: Geoff Pennington (penningtong_at_stic2.com.NO!SPAM!)
Date: 03/03/04 

Date: Wed, 3 Mar 2004 09:39:11 -0500

Hi Val -
Here is a "log" of my attempts and the results. Since this is a .Net
application I have an app.config file. Thanks for your help.

*****************************
Here is my original connection string in my app.config file. Provided for
reference.
     <add key="Input_Connection"
value="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data
source="/>
You might immediately think "No data source! There's the problem!" But "Data
source" is provided in the program; the user specifies the path and file
name, which is concatenated onto this string. It works, but with the bug
that this post is about.

Here was my first attempt, after reading Paul's reply.
     <add key="Input_Connection"
value="Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel
8.0;HDR=NO;IMEX=1;Data source="/>
Message: "Could not find installable ISAM"

Second attempt, when I realized I'd left out the double quotes in Paul's
suggestion.
     <add key="Input_Connection"
value="Provider=Microsoft.Jet.OLEDB.4.0;""Extended Properties=Excel
8.0;HDR=NO;IMEX=1;""Data source="/>
Message: An unhandled exception of type
'System.Configuration.ConfigurationException' occurred in system.dll
Additional information: This is an unexpected token. Expected 'NAME'. Line
6, position 76.

Note that line 6, position 76 is the "E" on Extended Properties.

Finally, thinking it might like single quotes better than the "doubled"
double quotes, I tried
     <add key="Input_Connection"
value="Provider=Microsoft.Jet.OLEDB.4.0;'Extended Properties=Excel
8.0;HDR=NO;IMEX=1;'Data source="/>
which got me back to the "Could not find installable ISAM" message.
************************

All of the strings shown above are pasted directly from my app.config file,
so there is no chance of me having made a typo when posting them here.

Can you see what I am doing wrong? Much obliged.

Geoff.

"Val Mazur" <group51a@hotmail.com> wrote in message
news:%23c2VJWNAEHA.712@tk2msftngp13.phx.gbl...
> Hi Geoff,
>
> IMEX should work with ADO.NET as well. Actually it is not ADO.NET
property,
> but provider's one and ADO.NET does not know anything about it and cannot
> complain. I think you have incorrect syntax in your connection string.
Could
> you post your connection string with IMEX here?
>
> --
> Val Mazur
> Microsoft MVP
>
> "Geoff Pennington" <penningtong@stic2.com.NO!SPAM!> wrote in message
> news:OAAZQRJAEHA.3256@TK2MSFTNGP09.phx.gbl...
> > Thanks, but that does not work for me. I get the message "Could not find
> > installable ISAM". I figure the problem is that I am using VB.Net and
> > ADO.Net (oops - my original post just said ADO), but the article you
> > referenced was for DAO. I did a little hunting but couldn't find a
> > specific
> > .Net solution.
> >
> > We are trying to solve the problem by ensuring the data is always
> > alphanumeric, but a .Net solution would still be welcome.
> >
> > Much obliged.
> >
> >
> >
> > "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in
message
> > news:c0o94097ruepleilokcd7n09unm0nmajec@4ax.com...
> >> On Tue, 2 Mar 2004 12:10:12 -0500, "Geoff Pennington"
> > <penningtong@stic2.com.NO!SPAM!> wrote:
> >>
> >> ¤ I have written an app which reads an Excel spread*** and loads the
> > data
> >> ¤ into a spread***. I establish a connection to the spread***, open
> > it,
> >> ¤ and fill a dataset pretty much the same as if it were a database. For
> > the
> >> ¤ most part it works fine.
> >> ¤
> >> ¤ Most of the spread*** columns contain either all numeric data
(money
> >> ¤ amounts, zip codes) or data that always have at least some character
> > data in
> >> ¤ each cell (such as names, addresses). None of these columns give me
any
> >> ¤ trouble. In one column ("policy_number") the cells can be any
> > combination of
> >> ¤ alpha and numeric data. So, "ABCDE", "12345", and "A1" are valid
policy
> >> ¤ numbers. However, the value "12345" does not "get read", and the
> > expression
> >> ¤ pRow.Item("policy_number")
> >> ¤
> >> ¤ evaluates as DBNull when the policy number is "12345". If I edit the
> > data so
> >> ¤ all cells in the column are numeric or all contain at least some
alpha
> >> ¤ characters, there is no problem.
> >>
> >> Documented issue with the Excel ISAM. You need to specify the IMEX
> > property in your connection
> >> string:
> >>
> >> cnn.Open _
> >> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> >> "Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel
> > 8.0;HDR=NO;IMEX=1;"""
> >>
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;194124
> >>
> >>
> >> Paul ~~~ pclement@ameritech.net
> >> Microsoft MVP (Visual Basic)
> >
> >
>
>