在EXCEL导入ACCESS中如何判断表格中是否存在数据
<!--#include virtual="/include/dbconn.inc"--><%
'[ZJXM_ZYGL] (C) 2009-2010在建项目资源录入管理系统 Inc.
'$Id: producit_add.asp 2009-08-17 $
if request.Cookies("shiwei_username")="" then
%>
<script language="javascript">
top.location.href="../index.asp"
</script>
<%
response.end
end if
%>
<!--#include file="uploadx.asp"-->
<%
'on error resume next
Dim filePath
Dim fileName
Dim fileExt
Dim file_subject
Dim Sql
Dim msg
Dim errflag
Dim errnumber
Dim SavePath
Dim maxfilesize
SavePath = "file" '虚拟路径(后面不要加"/"符号)
maxfilesize = 50*1024 '大小为50M
Errflag=false
filePath = SavePath '使用虚拟路径进行赋值,如"/www"或"www"等
filePath = Server.MapPath(filePath) '将虚拟路径转换为磁盘路径
file_subject = GetFormVal("tables1") '取得文件标题
fileext = GetFormVal("bank") '取得文件介绍
errnumber = GetFormVal("errnumber") '取得报错方式
filename = SaveFile("fruit",filePath,maxfilesize,2,1) '保存并取得文件名
' 0,1 唯一文件名方式，如果有同名则自动改名；
' 1,1 报错方式，如果有同名则出错；
' 2,[0|1] 覆盖方式，如果有同名则覆盖原来的文件
sheet = file_subject
bank = fileext
dim filename_2
dim count_num
filename_2 = Split(filename,"|")
'On Error Resume Next
db="\produit\file\"&filename_2(0)
Server.ScriptTimeOut = 999999
'set conn=CreateObject("ADODB.Connection")
'conn.Open "driver={SQL Server};server=server2003;uid=sa;pwd=;database=exceltest;" '导入的数据库名称
set conn=Server.CreateObject("ADODB.Connection")
conn.open "driver={microsoft access driver (*.mdb)};dbq="&server.mappath("/data/!##jxc##!.mdb") '建立数据库连接
Set conne = Server.CreateObject("ADODB.Connection")
'Driver = "Driver={Microsoft Excel Driver (*.xls)};"
'DBPath = "DBQ=" &Server.MapPath(""&db&"")
' 调用Open 方法打开excel
'response.Write Driver & DBPath &"<br>"
'myConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath(""&db&"") &";Extended Properties=Excel 8.0;IMEX=2;"
myConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(""&db&"") & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
response.Write myConn_Xsl
'response.End()
conne.Open myConn_Xsl
Set rse = Server.CreateObject("ADODB.Recordset")
' 打开Sheet，参数二为Connection对象,因为Excel ODBC驱动程序无法直接用'sheet名来打开sheet,所以请注意以下的select语句
rse.Open "Select * From ["&sheet&"$]", conne
'response.Write "Select * From ["&sheet&"$]"&"<br>"
sql="select * from produit where huohao='"& fixsql(rse(10)) &"' or title='"& fixsql(rse(0)) &"'"
set rs=conn.execute(sql)
if rs.eof=false then
%>
<script language="javascript">
alert("您导入的项目已经存在，请重新导入！")
window.history.go(-1)
</script>
<%
response.end
end if
while not rse.eof
sql = "insert into produit(title,id_bigclass,id_smallclass,id_danwei,id_ku,price,price2,guige,id_gys,gys,huohao,duihuan,beizhu) values('"& fixsql(rse(0)) &"',"& fixsql(rse(1)) &","& fixsql(rse(2)) &","& fixsql(rse(3)) &","& fixsql(rse(4)) &","& fixsql(rse(5)) &","& fixsql(rse(6)) &",'"& fixsql(rse(7)) &"',"& fixsql(rse(8)) &",'"& fixsql(rse(9)) &"','"& fixsql(rse(10)) &"',#"& fixsql(rse(11)) &"#,'"& fixsql(rse(12)) &"')"
conn.execute(sql)
rse.movenext
Response.Write "正在插入 "&sql&"<Br>"
count_num=count_num+1
response.Write count_num&"<br>"
Response.Flush
wend
conne.close
set conne = Nothing
If Err = 0 Then
Response.Write "成功导入"&count_num&"条记录"
Else
Response.Write "导入失败!"
End If
function fixsql(str)
dim newstr
newstr = str
if isnull(newstr) then
newstr = ""
else
newstr = replace(newstr,"'","''")
end if
fixsql = newstr
end Function
Call update_insert(fileext,count_num)
%>
<html>
<head>
<title>导入成功</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<link href="/css/sport_style.css" rel="stylesheet" type="text/css">
<style type="text/css">
<!--
body,td,th {
font-size: 12px;
}
a {
font-size: 12px;
color: #000000;
}
a:link {
text-decoration: none;
}
a:visited {
text-decoration: none;
color: #000000;
}
a:hover {
text-decoration: underline;
color: #000000;
}
a:active {
text-decoration: none;
color: #000000;
}
.style1 {color: #FF0000}
-->
</style></head>
<body class="h1">
<table width="700" border="0" cellspacing="0" cellpadding="1" align="center">
<tr>
<td> <table width="100%" border="0" cellspacing="0" cellpadding="0" >
<tr>
<td> </td>
</tr>
<tr>
<td> <div align="center">
<table width="600" border="0" cellpadding="0" cellspacing="0" bgcolor="#CBDFF3">
<tr>
<td width="15" height="15"> </td>
<td width="570"></td>
<td width="15" height="15"> </td>
</tr>
<tr>
<td colspan="3"> <div align="center">
<table width="599" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="P093"> <div align="center">
<center>
</center>
</div></td>
</tr>
<tr>
<td class="P093"> <div align="center">
<hr width=500>
</div></td>
</tr>
<tr>
<td class="P093"> <div align="center">
<p><font size="1"><%response.Write "数据导入完毕,共导入"&count_num&"条记录"%></font><br><br>
<span class="style1"><font size="1">切不可重复导入以免出现大量无用数据</font></span></p>
<script language="javascript">window.location.href="piliang.asp"</script>
<p> </p>
</div></td>
</tr>
<tr>
<td class="P093"> <div align="center">
<hr width=500>
</div></td>
</tr>
<tr>
<td class="P093"> <div align="center"> <font color="#FFFFFF">
</font> </div></td>
</tr>
</table>
</div></td>
</tr>
<tr>
<td width="15" height="15"> </td>
<td width="570"></td>
<td width="15" height="15"> </td>
</tr>
</table>
</div></td>
</tr>
<tr>
<td> </td>
</tr>
</table></td>
</tr>
</table>
</body>
</html>
