asp批量导入问题

Fiftyfive 2009-08-06 11:50:01

从excel中批量导入数据到数据库，其中有一个字段为手机号码，数据库设置的格式为varchar，长度为20。现在的问题是，如果excel中的号码全部为数字格式或者全部为以文本格式存储的数字，导入正常。但是如果号码的格式既有数字格式，又有文本格式时，数字格式的号码导入到数据库后，显示为空（NULL），文本格式的号码导入正常。我的数据库为sql2000，请教各位大大。
导入的执行源码如下：
<%
dim upload,tempCls,formPath,path,folder
dim banliqudao,riqi
'===============================================================================
set Upload=new AnUpLoad '创建类实例
Upload.SingleSize=1024*1024*1024 '设置单个文件最大上传限制,按字节计；默认为不限制
Upload.MaxSize=1024*1024*1024 '设置最大上传限制,按字节计；默认为不限制
Upload.Exe="xls" '设置合法扩展名,以|分割,忽略大小写
Upload.Charset="gb2312" '设置文本编码，默认为gb2312
Upload.openProcesser=false '禁止进度条功能，如果启用，需配合客户端程序
Upload.GetData() '获取并保存数据,必须调用本方法
'===============================================================================
function MakedownName()
dim fname
fname = now()
fname = replace(fname,"-","")
fname = replace(fname," ","")
fname = replace(fname,":","")
fname = replace(fname,"PM","")
fname = replace(fname,"AM","")
fname = replace(fname,"上午","")
fname = replace(fname,"下午","")
fname = int(fname) + int((10-1+1)*Rnd + 1)
MakedownName=fname
end function
'===============================================================================
banliqudao=trim(upload.forms("banliqudao"))
riqi=trim(upload.forms("riqi"))
if banliqudao = "0" then
set upload=nothing
Response.Write("<script language=JavaScript> alert('请选择办理渠道');document.location.href='javascript:window.history.go(-1);';</script>")
Response.end
end if
if riqi = "0" then
set upload=nothing
Response.Write("<script language=JavaScript>alert('请选择办理月份');document.location.href='javascript:window.history.go(-1);';</script>")
Response.end
end if

if Upload.files(-1).count>0 then '这里判断你是否选择了文件
path=server.mappath("file/ywtj")
set fso=server.CreateObject("Scripting.FileSystemObject")
if fso.FolderExists(path)=false then
fso.CreateFolder(path)
end if
set tempCls=Upload.files("file1")
tempCls.SaveToFile path,0
fname=tempCls.FileName
lname=tempCls.localName
set tempCls=nothing
else
Response.Write("<script language=JavaScript>alert('请选择文件');document.location.href='javascript:window.history.go(-1);';</script>")
Response.end
end if
set upload=nothing
%>
<%
'上传XLS文件结束，下面从上传的XLS文件中读取数据写入到SQL数据库
strAddr=server.MapPath("file/ywtj/"&fname)
set excelconn=server.createobject("adodb.connection")
excelconn.open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source= "+strAddr+";"
dim success
success=0
set rs1=server.CreateObject("adodb.recordset")
sql1="select * from [Sheet1$]"
rs1.open sql1,excelconn,1,1
set rs2=server.CreateObject("adodb.recordset")
sql2="select * from dbo.yyywtj"
rs2.open sql2,conn,1,3
if rs1.bof and rs1.eof then
else
do while not rs1.EOF
rs2.addnew
rs2("ywtjlb")=rs1(0)
rs2("haoma")=rs1(1)
rs2("bishu")=rs1(2)
rs2("banliqudao")=banliqudao
rs2("riqi")=riqi
rs2("username")=session("username")
rs2("userbanzu")=session("userbanzu")
rs2("rdate")=now()
rs2.update
success=success+1
rs1.movenext
loop
end if
rs1.close()
rs2.close()
set rs1=nothing
set rs2=nothing
conn.close

...全文