excel|odbc| Data http://www.chinaasp.com/
The way in which data is called in Excel in ASP and the normal way to call a database through ODBC is
A few different places. The following describes how to use Excel's data in ASP using ODBC.
In the process of using, be aware that you need to use the ODBC driver: Microsoft ODBC Driver for Excel.
Here's an example to specify how to invoke the procedure:
1. Create a new form named range in Excel's spreadsheet
(a) Enter the following test data randomly in the Sheet1 in Excel file Adotest.xls:
Column1 Column2 Column3
RR this 15
BB Test 20
EE Works 25
Attention Matters：
If you have a column in your Excel spreadsheet that contains both text and numbers, then Excel's ODBC driver will not be able to properly
To handle the data type of this line, you must ensure that the data type of the column is consistent. ：
I.microsoft OLE DB Provider for ODBC Drivers error ' 80040e21 '
The request properties can not is supported by this ODBC Driver.
Ii. Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '
The "query is not" updateable because it contains no searchable columns to use as a
Hopeful key.
Iii. Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '
Query based update failed. The row to update could is found.
b Use the mouse to select all rows and columns where your data resides
(c) Select the Insert\name\define command from the menu.
d) Enter myRange1 and click OK to exit
Attention Matters：
i) ADO assumes the first behavior field name in Excel. So the scope you define must include the first line of content
ii The row headers (that is, field names) in Excel cannot contain numbers. Excel drivers can make mistakes when encountering this problem.
For example, your row header is named "F1."
E Create a new ODBC system DSN point to this Adotest.xls file: (This process I don't say in detail)
Attention Matters：
i) to select the ACCESS97 version in DSN
(ii) Set up an anonymous account in NT to access the spreadsheet file appropriately
III If your permissions are not set correctly, you may get the following error message:
Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '
[Microsoft] [ODBC Excel Driver] The Microsoft Jet Database engine
Cannot open the file ' (unknown) '. It is already opened exclusively
By another user, or your need permission to view its data.
f) The final step is to call this ODBC in ASP, the code is as follows:
<%@ language= "VBSCRIPT"%>
<%
Set objconn = Server.CreateObject ("ADODB. Connection ")
objConn.Open "Adoexcel"
Set objRS = Server.CreateObject ("ADODB. Recordset ")
Objrs.activeconnection = objconn
Objrs.cursortype = 3
Objrs.locktype = 2
Objrs.source = "SELECT * FROM MyRange1"
Objrs.open
%>
<br>
<%
Response.Write ("Original Data")
Response.Write ("<TABLE><TR>")
For X = 0 to Objrs.fields.count-1
Response.Write ("<TD>" & ObjRS.Fields.Item (X). Name & "</TD>")
Next
Response.Write ("</TR>")
Objrs.movefirst
While Not objrs.eof
Response.Write ("<TR>")
For X = 0 to Objrs.fields.count-1
Response.Write ("<TD>" & ObjRS.Fields.Item (X). Value)
Next
Objrs.movenext
Response.Write ("</TR>")
Wend
Response.Write ("</TABLE>")
Objrs.movefirst
Objrs.fields (0). Value = "Change"
Objrs.fields (1). Value = "Look"
Objrs.fields (2). Value = "30"
Objrs.update
Response.Write ("<br>data after the update")
Response.Write ("<TABLE><TR>")
For X = 0 to Objrs.fields.count-1
Response.Write ("<TD>" & ObjRS.Fields.Item (X). Name & "</TD>")
Next
Response.Write ("</TR>")
Objrs.movefirst
While Not objrs.eof
Response.Write ("<TR>")
For X = 0 to Objrs.fields.count-1
Response.Write ("<TD>" & ObjRS.Fields.Item (X). Value)
Next
Objrs.movenext
Response.Write ("</TR>")
Wend
Response.Write ("</TABLE>")
Objrs.close
Set objRS = Nothing
Objconn.close
