Connect to remote xls via odbc and dsn

[Karl]

We have an application which checks users against a regularly updated
xls spreadsheet from our client. We need to be able to connect to this
xls on our remote server and so have decided to use a DSN. When I used
VB.Net to test the connection there were no errors, however when I use
asp.net to get the connection I do get one.

I cant use a relative path to the file as is normal in a
connectionstring as the environment is a shared one.

The code is shown below. Please help.

VB.NET CODE:
Try
Dim oEConn As New OdbcConnection
oEConn.ConnectionString = "DSN=members_bmf"
oEConn.Open()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

ASP.NET Code

Try
Dim oEConn As New OdbcConnection
oEConn.ConnectionString = "DSN=members_bmf"
oEConn.Open()
Catch ex As Exception
'errMessage is an asp.netlabel
errMessage.text =ex.ToString
End Try


Here is the error
System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver
Manager] Data source name not found and no default driver specified at
System.Data.Odbc.OdbcConnection.Open() at
ASP.bmf_validate_bmf_member_ascx.CheckMembership(Object sender,
EventArgs e) in \\TLBFS01\development\Projects\Tradecounter\Portal
Development\TCCPortal
Development\modules\bmf_validate_bmf_member.ascx:line 41

 

[Paul Clement]

¤ We have an application which checks users against a regularly updated
¤ xls spreadsheet from our client. We need to be able to connect to this
¤ xls on our remote server and so have decided to use a DSN. When I used
¤ VB.Net to test the connection there were no errors, however when I use
¤ asp.net to get the connection I do get one.
¤
¤ I cant use a relative path to the file as is normal in a
¤ connectionstring as the environment is a shared one.
¤
¤ The code is shown below. Please help.
¤
¤ VB.NET CODE:
¤ Try
¤ Dim oEConn As New OdbcConnection
¤ oEConn.ConnectionString = "DSN=members_bmf"
¤ oEConn.Open()
¤ Catch ex As Exception
¤ MsgBox(ex.ToString)
¤ End Try
¤
¤ ASP.NET Code
¤
¤ Try
¤ Dim oEConn As New OdbcConnection
¤ oEConn.ConnectionString = "DSN=members_bmf"
¤ oEConn.Open()
¤ Catch ex As Exception
¤ 'errMessage is an asp.netlabel
¤ errMessage.text =ex.ToString
¤ End Try
¤
¤
¤ Here is the error
¤ System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver
¤ Manager] Data source name not found and no default driver specified at
¤ System.Data.Odbc.OdbcConnection.Open() at
¤ ASP.bmf_validate_bmf_member_ascx.CheckMembership(Object sender,
¤ EventArgs e) in \\TLBFS01\development\Projects\Tradecounter\Portal
¤ Development\TCCPortal
¤ Development\modules\bmf_validate_bmf_member.ascx:line 41

You need to use a UNC path to the file. Personally I'd recommend a DSN-less connection using Jet
OLEDB and the Excel ISAM driver rather than the Excel ODBC driver.

Also, IIS and ASP.NET implement a difference security mechanism so you may experience a credential
delegation issue if the file is on a system that is remote from the web server.

ASP.NET Delegation
http://msdn.microsoft.com/library/d...y/en-us/vsent7/html/vxconaspnetdelegation.asp


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Karl]

I thought the whole purpose of DSN's is for exactly this kind of thing,
accessing data on a remote system and across platforms.

How do I use a unc path to the file if the file it hosted on an ISPs
webserver?

 

[Scott M.]

You would specify a relative path to the Server.MapPath() method and that
method would return the full physical path.

DSN-Less connections are defiantly the way to go.

 

[Paul Clement]

¤ I thought the whole purpose of DSN's is for exactly this kind of thing,
¤ accessing data on a remote system and across platforms.
¤

DSNs were designed for ODBC based connections, typically for server based databases (which typically
take into consideration remote access) and not file based data stores, although you can use them for
that.

¤ How do I use a unc path to the file if the file it hosted on an ISPs
¤ webserver?

If the file is located on the web server then you can use Server.MapPath as Scott mentioned.
However, the authenticated user must still have sufficient permissions to access the resource. If
your web application is not implementing impersonation, the authenticated user would be ASPNET
(under Windows 2000) and NetworkService (under Windows 2003 Server).


Paul
~~~~
Microsoft MVP (Visual Basic)