问一个利用ASP将EXCEL导入ACCESS的问题
读取EXCEL文件内容的代码如下：
<style>
td,input,select,textarea,body{font-size:9pt}
a{color:blue}
a:hover{color:green}
</style>
<%
if session("xibu")="" then
If Request.QueryString("action")="do" Then
Dim conn
Dim StrConn
Dim Rs
Dim Sql
Dim i
Dim ExName
ExName = Request.Form("ExName")
ExTName = Request.Form("ExTName")
Set conn =Server.CreateObject("ADODB.Connection")
StrConn="Driver={Microsoft Excel Driver (*.xls)};DBQ="& Server.MapPath("Excel/"&ExName)
conn.Open StrConn
Set rs = Server.CreateObject("ADODB.Recordset")
Sql="select * from ["&ExTName&"$]"
rs.Open Sql,conn,1,1
%>
<br>
<form method=post action="JaAcc_Save.Asp" name=form1 onSubmit="return chk(this)">
<table width="800" border="0" align=center cellspacing=1 bgcolor=#CCCCCC>
<tr align=center height=20 bgcolor=#6699CC style=color:white>
<td colspan="4">导入数据列表(请确保字段没有错误)</td>
</tr>
<tr>
<td align="center">
<input name=Count type=hidden value="<%=rs.Fields.Count%>">
<%
for i=0 to rs.Fields.Count-1
%>
<input name=ExFName<%=i%> value="<%=Rs(i).Name%>" size="10">
<%
Next
Response.Write "</td></tr></table><table width=500 border=0 align=center cellspacing=1 bgcolor=#CCCCCC>"
Response.Write "<tr bgcolor=white><td align=center>"
Dim a
a=0
do while not rs.eof
for i=0 to rs.Fields.Count-1
if i mod rs.Fields.Count = 0 then
Response.Write "<br>"
End if
%>
<input name=ExCName<%=a%> value="<%=Rs(i)%>" size="10">
<%
a = a +1
next
rs.MoveNext
Loop
Response.Write "</td></tr><tr><td align=center><input name='A' type=hidden value="&a&"><input type=submit value=导入数据></td></tr></table>"
Response.Write "</form>"
rs.close
set rs=nothing
conn.close
set StrConn=nothing
Response.End
End if
%>
<style>
td,input,select,textarea,body{font-size:9pt}
a{color:blue}
a:hover{color:green}
</style>
<script language="JavaScript" type="text/JavaScript">
function chk()
{
if (document.form1.ExName.value=="")
{
alert("Excel文件名称不能为空！");
document.form1.ExName.focus();
return false;
}
if (document.form1.ExTname.value=="")
{
alert("Excel数据表文件名称不能为空！");
document.form1.ExTname.focus();
return false;
}
}
</script>
<br>
<table width="500" align=center cellpadding=5 cellspacing=1 bgcolor=#006699>
<tr bgcolor=#6699CC style=color:white align=center>
<td>
<b>导入数据注意事项</b><br>
1:请确保你清楚Excel文件内容字段与导入数据库的字段相同<p>
2:请确保你清楚Excel文件的表名正确 如 Sheet1<p>
3:请确保服务器上有该Excel文件存在于Excel文件夹里，如没有，请上传并记下文件名称<p>
</td>
</tr>
</table>
<form method="post" action="upload2.asp" enctype="multipart/form-data" name="form2">
<table width="500" align=center cellpadding=5 cellspacing=1 bgcolor=#006699>
<tr bgcolor=#6699CC style=color:white align=center>
<td>
<input type=file name="sf_upfile" size="30" class=box>
<input type="submit" name="submit" value="上 传" class="box">
</td>
</tr>
</table>
</form>
</body></html>
<form method=post action="?action=do" name=form1 onSubmit="return chk(this)">
<table width="500" align=center cellpadding=5 cellspacing=1 bgcolor=#006699>
<tr bgcolor=#6699CC style=color:white align=center>
<td width="183">Excel地址(如:JaStudio.xls)</td>
<td width="217">Excel导入数据表名 (如:Sheet1)</td>
<td width="64"></td>
</tr>
<tr bgcolor=white>
<td><input name=ExName value="" size="30"></td>
<td><input name=ExTname value="" size="30">
<td><input type=submit value=导入数据> </tr>
</table>
</form>
<meta http-equiv="content-type" content="text/html;charset=gb2312">
<style>
td,input,select,body{font-size:9pt}
</style>
<script>
function backup()
{
window.open("admin_backupdata.asp","","Width=400,Height=300")
}
</script>
<%
Else
Response.Redirect "1.asp"
End If
%>
此页面可以成功的将EXCEL的内容读出来，下面是向数据库进行存储的过程
<!--#include file=common.asp-->
<%
Dim a
Dim FCount
Dim Fname
Dim Cname
Dim i
Dim ccc
Dim b
a = Cint(Request.Form("A"))
Fcount = Cint(Trim(Request.Form("Count")))
for i=0 to Fcount-1
if i=Fcount-1 Then
Fname = Fname & Request.Form("ExFName"&i&"")
else
Fname = Fname & Request.Form("ExFName"&i&"") & ","
end if
next
for i=0 to a
Cname = Cname & Request.Form("ExCName"&i&"") & "|"
next
Cname = split(Cname,"|")
for i=0 to a
if i>0 and i mod Fcount = 0 and i<a then
Response.Write "<br>"
for b=0 to Fcount-1
if b<> Fcount-1 Then
ccc = ccc & "'" & cname(i+b) & "',"
Else
ccc = ccc & "'" & cname(i+b) & "'"
End if
next
Sql ="Insert into SingUp("&Fname&")values('"&ccc&"')"
'Response.Write Sql
ccc =""
Conn.ExeCute(Sql)
Response.Write "<br>"
End If
next
Response.write "<script language='javascript'>" & chr(13)
Response.write "alert('记录导入成功!');" & Chr(13)
Response.write "window.document.location.href='JaExcel_Acc.Asp';"&Chr(13)
Response.write "</script>" & Chr(13)
Response.End
erase Cname
%>
以下是我数据库的驱动代码：
<%@ LANGUAGE = VBScript CodePage = 936%>
<%
Dim IsSqlDataBase
Dim datafile
Dim conn
Dim connstr
set conn=server.createobject("adodb.connection")
IsSqlDataBase=0 '定义数据库类别，0为Access数据库，1为SQL数据库
If IsSqlDataBase=0 Then
'''''''''''''''''''''''''''''' Access数据库 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
datafile ="data/zjdata.mdb" '数据库的文件名
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(datafile)
conn.open connstr
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Else
Dim SqlLocalName
Dim SqlUserName
Dim SqlPassword
Dim SqlDatabaseName
'''''''''''''''''''''''''''''' SQL数据库 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SqlLocalName ="(local)" '连接IP [ 本地用 (local) 外地用IP ]
SqlUsername ="sa" '用户名
SqlPassword ="jingan" '用户密码
SqlDatabaseName="JaZone" '数据库名
conn.Open "Provider=Sqloledb; User ID=" & SqlUsername & "; Password=" & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source=" & SqlLocalName & ";"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End If
On Error Resume Next
%>
在向数据库添加纪录的地方执行，页面提示说已经成功提交数据，但是到ACCESS数据库里看，表里却没有数据，还是提交前的数据，为高手指点一下
问题点数：100、回复次数：7Top
1 楼s1ihome（My hometown is a beautiful village）回复于 2006-03-20 13:51:40 得分 15
把这个On Error Resume Next先去掉显示详细错误好调试
Top
2 楼renwenbin1982（）回复于 2006-03-20 14:04:03 得分 0
有提示了：
Microsoft JET Database Engine (0x80040E37)
找不到输出表 'SingUp' 。
Top
3 楼s1ihome（My hometown is a beautiful village）回复于 2006-03-20 15:22:22 得分 5
那就在你的数据库里面先建立signUp这个表吧Top
4 楼boyd1985（波伊德乐园->http://www.boyd.cn）回复于 2006-03-20 15:33:58 得分 70
先确保数据库里存在SignUp这个表，
然后将添加的东西改动一下：
Sql ="Insert into SingUp values('"&ccc&"')"
Top
5 楼jspadmin（阿笨狗http://www.pifoo.com域名空间专卖cn20、com50元）回复于 2006-03-20 15:37:03 得分 10
检查数据库中是否有这个表，并且注意sql语句每个词间加个空格Top
6 楼renwenbin1982（）回复于 2006-03-20 21:52:22 得分 0
谢谢楼上的达人，结贴喽Top
7 楼hreoghost（Challenge oneself，break through limit！）回复于 2006-03-22 08:45:41 得分 0
做个记号````
学习中...Top
