Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 23rd, 2003, 02:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Connection String with Excel file

I am use following coding for the Excel file connection with ASP.
It give following error in connection string.
I check Connection string "test Connection successful " . But when I use this string in the asp file it give following error message.

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/101/xl_data.asp, line 43, column 20
cnnExcel.Open "DBQ="D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls;Defa ultDir=D:\DCIL\DOWNLOAD\101SAMPLES;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\matdsn2.dsn;MaxScanRows=8;PageTimeout=5;Re adOnly=0;SafeTransactions=0;Threads=3;UID=admin;Us erCommitSync=Yes;"
-------------------^


ASP file connection string coding
----------------------------------
Const adOpenStatic = 3
Const adLockPessimistic = 2

Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols

Set cnnExcel = Server.CreateObject("ADODB.Connection")

cnnExcel.Open "DBQ="D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls;Defa ultDir=D:\DCIL\DOWNLOAD\101SAMPLES;Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;FIL=excel 8.0;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\matdsn2.dsn;MaxScanRows=8;PageTimeout=5;Re adOnly=0;SafeTransactions=0;Threads=3;UID=admin;Us erCommitSync=Yes;"



Mateen
 
Old October 23rd, 2003, 03:10 AM
joefawcett's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

There are a couple of problems, especially with nested quotes, however I'd advise switching to oledb rather than use odbc. Under this link click the Excel icon and oledb for an easier and better connection string:

http://www.connectionstrings.com/

Joe

--

Joe
 
Old October 23rd, 2003, 04:02 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

First problem (as per joefawcett):

"DBQ="D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls
-----^ (extra ")

Second problem: Is there a path:
D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls
on the server? When you test a connection, it is tested locally. When you publish, the paths must be relative to the server.

Recommendation: Publish xl_data.xls to your root web folder then use Server.MapPath() as:

cnnExcel.Open "Driver={Driver do Microsoft Excel(*.xls)};" & "DBQ=" & Server.MapPath("xl_data.xls") & ";"

 
Old October 25th, 2003, 05:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am working on localhost.
I use following coding. but now it give this error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
/mtp/myxl_data.asp, line 44


asp coding
----------
<%
Response.ContentType = "application/vnd.ms-excel"
Const adOpenStatic = 3
Const adLockPessimistic = 2

Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols

Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "Driver={Driver do Microsoft Excel(*.xls)};" & "DBQ=" & Server.MapPath("xl_data.xls") & ";"

Set rstExcel = Server.CreateObject("ADODB.Recordset")
rstExcel.Open "SELECT * FROM TestData;", cnnExcel, _
    adOpenStatic, adLockPessimistic // line 44
%>


what is the problem ?
how I can use excel file in ASP page ?

Please help

Mateen


Quote:
quote:Originally posted by U.N.C.L.E.
 First problem (as per joefawcett):

"DBQ="D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls
-----^ (extra ")

Second problem: Is there a path:
D:\DCIL\DOWNLOAD\101SAMPLES\xl_data.xls
on the server? When you test a connection, it is tested locally. When you publish, the paths must be relative to the server.

Recommendation: Publish xl_data.xls to your root web folder then use Server.MapPath() as:

cnnExcel.Open "Driver={Driver do Microsoft Excel(*.xls)};" & "DBQ=" & Server.MapPath("xl_data.xls") & ";"

 
Old October 26th, 2003, 06:13 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Do you have a named range in your Excel file called "TestData"?
Are adOpenStatic and adLockPessimistic defined?

adOpenStatic and adLockPessimistic (and adCmdText) are defined in a file called adovbs.inc. If you can't find the file then use these:

Const adCmdText = 1
Const adOpenStatic = 3
Const adLockPessimistic = 2

rstExcel.Open "SELECT * FROM TestData;", cnnExcel, _
    adOpenStatic, adLockPessimistic, adCmdText

 
Old October 27th, 2003, 05:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for your response.
now it give this error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] External table is not in the expected format.
/mtp/xl_data3.asp, line 32

I could not understand
"Do you have a named range in your Excel file called "TestData"? "

how I can do named range in excel file ?
in the excel file how can defined range of data ?

I am using following coding.
--------------------------------
Const adCmdText = 1
Const adOpenStatic = 3
Const adLockPessimistic = 2

Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols

Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "DBQ=" & Server.MapPath("xl_data.xls") & ";" & "DRIVER={Microsoft Excel Driver (*.xls)};"


Set rstExcel = Server.CreateObject("ADODB.Recordset")

rstExcel.Open "SELECT * FROM testdata;", cnnExcel, adOpenStatic, adLockPessimistic

iCols = rstExcel.Fields.Count
%>
<table border="1">
    <thead>
        <%

        For I = 0 To iCols - 1
            Response.Write "<th>"
            Response.Write rstExcel.Fields.Item(I).Name
            Response.Write "</th>" & vbCrLf
        Next 'I
        %>
    </thead>
    <%
    rstExcel.MoveFirst

    Do While Not rstExcel.EOF
        Response.Write "<tr>" & vbCrLf
        For I = 0 To iCols - 1
            Response.Write "<td>"
            Response.Write rstExcel.Fields.Item(I).Value
            Response.Write "</td>" & vbCrLf
        Next 'I
        Response.Write "</tr>" & vbCrLf

        rstExcel.MoveNext
    Loop
    %>
</table>

<%
rstExcel.Close
Set rstExcel = Nothing

cnnExcel.Close
Set cnnExcel = Nothing
%>



Mateen





Quote:
quote:Originally posted by U.N.C.L.E.
 Do you have a named range in your Excel file called "TestData"?
Are adOpenStatic and adLockPessimistic defined?

adOpenStatic and adLockPessimistic (and adCmdText) are defined in a file called adovbs.inc. If you can't find the file then use these:

Const adCmdText = 1
Const adOpenStatic = 3
Const adLockPessimistic = 2

rstExcel.Open "SELECT * FROM TestData;", cnnExcel, _
    adOpenStatic, adLockPessimistic, adCmdText

 
Old October 27th, 2003, 02:45 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When you say "SELECT * FROM TestData;" it means: get the data from the range (named range) called "TestData".

Select your cells which contain your data (your range). Then select menu Insert|Name|Define and called it TestData.

I just created an Excel file with some data and a named range called TestData and tested your code. It works. You just have to create the named range in your Excel spreadsheet before the select query can retrieve the data.


 
Old November 4th, 2003, 12:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for your response.
I am trying but it is not retrieve the data from the Excel file.
Error is same

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] External table is not in the expected format.
/mtp/xl_data3.asp, line 32

any alternate suggestion (help) ?

regards.

Mateen



Quote:
quote:Originally posted by U.N.C.L.E.
 When you say "SELECT * FROM TestData;" it means: get the data from the range (named range) called "TestData".

Select your cells which contain your data (your range). Then select menu Insert|Name|Define and called it TestData.

I just created an Excel file with some data and a named range called TestData and tested your code. It works. You just have to create the named range in your Excel spreadsheet before the select query can retrieve the data.


 
Old January 25th, 2013, 02:49 AM
Registered User
 
Join Date: Jan 2013
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default How To Query and Update Excel Data Using ADO From ASP

Hai,

Im new to this forum,
i need u guys help in something.
Im very new to ASP...very very new and im given a task to query excel
from ASP. I found an example on
http://support.microsoft.com/default...NoWebContent=1

but im having some problem with it.
I've done all the steps but im getting the error 'ODBC driver does not support the requested properties'

plz do help...thanks in advance...





Similar Threads
Thread Thread Starter Forum Replies Last Post
connection string issues, web.config file issues kaliaparijat ASP.NET 2.0 Professional 1 June 12th, 2008 08:07 AM
connection string in excel sharcfinz SQL Language 2 May 7th, 2007 01:13 AM
Connection String jmss66 Oracle 4 August 8th, 2006 03:19 AM
Connection String phungleon Classic ASP Basics 1 March 18th, 2005 05:51 AM
Connection string and file include trouble cramerjj Classic ASP Databases 2 February 26th, 2005 04:46 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.