如何编程实现将SqlServer的数据导出到access,求源码,在线等!
解决方案 »
- 关于Session 验证码的问题
- .net修改IP地址
- 在本机可以显示,到其它机子就显示不了(加了安全网页的) 求救啊?
- php 代码 转asp.net 代码
- 急需用ajaxpro将返回的dataset绑定到table的例子。
- 要做一大型社区网站,不知道起什么名字好?
- 新安装了vs2005,但打开网站时,出现了错误:访问 IIS 元数据库失败,用于运行 ASP.NET 的进程帐户必须具有对 IIS 元数据库(如 IIS://servern
- 如何在页面上生成拓扑图,上面的图标可能有成千上万,而且相互之间要有连接线,大家看有什么好办法吗?
- 回发或回调参数无效的问题
- DataGrid简单问题,关于页眉?
- 菜鸟关于Forms身份认证的问题!
- 请问 asp.net里 repeater dataList dataGrid 这三个控件有什么不同呢? 我看书看了很久还是不太明白,请指教,谢谢~~!!!
其实现在就是在从sqlserver到access复制一张同结构的表这不太好办,其它的都没什么问题:
字段名和字段类型可以取到,但是有一个类型转换的问题。
string connStr=ConfigurationSettings.AppSettings["connStr"];
SqlConnection myconn=new SqlConnection(connStr);
string sqlStr="insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','d:\test.mdb';;,test) select * from test";
SqlCommand mycomm=new SqlCommand(sqlStr,myconn);
mycomm.Connection.Open();
mycomm.ExecuteNonQuery();报错:
OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 报错。 [OLE/DB provider returned message: 文件名无效。] OLE DB 错误跟踪[OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]。
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
</head><body>
<form action="upload_bc.asp" method="post" enctype="multipart/form-data" name="form1" onSubmit="return mycheck()">
请上传ACCESS文件
<input type="file" name="file1">
<br>
是否删除原表
<input name="ifdelete" type="checkbox" value="1" checked>
<br>
<input type="Submit" name="bc" value="提交">
</form>
</body>
</html>
<script language="VBScript">
function mycheck()
if form1.file1.value = "" then
alert("还未选择上传文件名!")
mycheck = false
else
dim myfile
myfile = form1.file1.value
dim ary
ary = split(myfile,".")
if lcase(ary(ubound(ary))) <>"mdb" then
alert("选择的文件格式错误!")
mycheck = false
end if
end if
end function
</script>
<!--保存上传文件并拆解该ACCESS表格式,连表带数据导入ORACLE数据库中.--><%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%
dim cn,ocn'数据库连接开始
Sub Conn_Start()
Dim oraSession
On Error Resume Next
Set oraSession = CreateObject("oracleinprocserver.xorasession")
Set ocn = oraSession.dbopendatabase("skmis", "skinfo/skinfo", 0)
if err.Number<>0 then
response.write("数据库连接错误!")
response.end
end if
Set oraSession = Nothing
End Sub'打开ORACLE数据库连接
call Conn_Start()function rspend(sname)
response.write sname
response.end
end functiondim ifdelete '是否删除原表'将上传的ACCESS文件存放到本网站的UPLOADFILE文件夹下
function myupload()
Set Upload = Server.CreateObject("Persits.Upload")
Count = Upload.Save
ifdelete = Upload.form("ifdelete")Set File = Upload.Files("file1")dim myfilename,myfiletype
If Not File Is Nothing Then
'求取文件名及文件类型
myfiletype = file.ContentType
myfilename = file.Filename'如果类型正确,则写入文件夹下
if myfiletype <> "application/msaccess" then
rspend("<script>alert('上传文件类型错误!');history.back()</script>")
else
File.saveas server.mappath("uploadfile")&"\"&myfilename
end if
end if
myupload = myfilename
end function'取得数据库文件名,再求取该数据库内所有表名
sub get_mdbtablename()
'取得文件名
dim myfilename,tablename
myfilename = "F:\Ason_HPNNW_Bak\test\SKsoft\conn_str\SKSOFT.MDB"'打开数据库
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&myfilename'取出所有表名称
set rs=cn.openschema(20)
do while not rs.eof
if rs(3)="TABLE" then
'取出表名
tablename = rs(2)
call get_mdbtype(tablename)
call get_mdbdate(tablename)
end if
rs.movenext
loop
rs.close()
end subfunction get_onevalue(sqlstr)
on error resume next
Set rs0 = ocn.CreateDynaset(sqlstr,0)
if err<>"0" then
rspend "错误参数!SQL语句如下:"&sqlstr
end if
if not rs0.eof then
get_onevalue=rs0(0)
else
get_onevalue="未知"
end if
rs0.close
end function
'根据ACCESS数据库数据库表名称,求取该表所有字段名称/类型/长度,并创建ORACLE表
sub get_mdbtype(sname)
dim mycreatesql,mytype,dropsql
dropsql = "drop table "&sname&""
'先写创建语句前段
mycreatesql = "create table " & sname & "("dim sql
sql = "select * from "&sname&" where 1=2"set rs = cn.execute(sql)
for i=0 to rs.fields.count-1
columwidth = rs(i).DefinedSize
mytype = typename(rs(i).type,columwidth)
columname = rs(i).name
if columname = "from" then
columname = "sfrom"
end if
if columname = "lock" then
columname = "slock"
end if
if columname = "level" then
columname = "slevel"
end if
if columname = "date" then
columname = "sdate"
end if
if columname = "size" then
columname = "ssize"
end if
'注意:如果是自动编号字段,在写入插入语句的时候不能对它赋值
if mytype = "未知类型" then
rspend("<script>alert('数据库字段未知,请重写!');history.back()</script>")
else
mycreatesql = mycreatesql & columname & " " & mytype & ","
end if
nextrs.closemycreatesql = mid(mycreatesql,1,len(mycreatesql)-1) & ")"'生成ORACLE数据库表
'删除同名表
sql = "select count(*) from asm_tabcol a where a.col_tablename='"&ucase(sname)&"'"mycount = get_onevalue(sql)if mycount <> "0" then
ocn.executesql(dropsql)
end if'创建新表
'response.write mycreatesql
'response.end
on error resume next
ocn.executesql(mycreatesql)
if err.number<>0 then
response.write mycreatesql
response.end
end if
end sub
'根据ACESS表的字段类型代码判断其字段类型
function typename(t,a)
select case t
case 17
'typename="字节型数字("&a&")"
typename="number("&a&")"
case 2
'typename="整型数字("&a&")"
typename="number"
case 4
'typename="Single"
typename="number"
case 3
typename="number"
case 5
'typename="Double"
typename="number"
case 6
'typename="货币("&a&")"
typename="number("&a&")"
case 131
'typename="小数("&a&")"
typename="number("&a&")"
case 11
'typename="是、否("&a&")"
typename="VARCHAR2(1)"
case 202
'typename="text"
typename="VARCHAR2("&a&")"
case 203
'typename="memo"
typename="clob"
case 205
'typename="OLE"
typename="blob"
case 7
typename="date"
case else
typename="未知类型"
end select
end function
'将表的数据倒到ORACLE中
sub get_mdbdate(sname)
dim sql,oraclesql'打开access数据库表
sql = "select * from " & sname
set rs = cn.execute(sql)
'求取此表的列数
dim mycount
mycount = rs.fields.count-1'循环取值
do while not rs.eof
'形成数据导入SQL语句
oraclesql = "insert into " & sname & " values("
for i = 0 to mycount
oraclesql = oraclesql & get_oraclesql(rs(i).type,rs(i))
next
oraclesql = mid(oraclesql,1,len(oraclesql)-1)
oraclesql = oraclesql & ")"
'导入数据库
on error resume next
ocn.executesql(oraclesql)
if err.number<>0 then
rspend oraclesql
end if
rs.movenext
loop
rs.close()
end sub'根据各字段的类型形成ORACLE数据库的SQL语句
function get_oraclesql(stype,sdata)
if sdata <> "" or sdata <> null then
sdata = replace(sdata,"'","''")
end if
dim sql
select case stype
case "202"
sql = "'" & sdata & "',"
case "203"
sql = "empty_clob(),"
case "7"
sql = "to_date('" & sdata & "','yyyy-mm-dd hh24:mi:ss') ,"
case else
sql = "'" & sdata & "',"
end select
get_oraclesql = sql
end function'写入CLOB内容
function write_clob(clob_id,clb_value)
sql0="update asm_clob set clb_content=empty_clob() where clb_id='"&clob_id&"'"
ocn.executesql(sql0) sql0 = "select clb_content from asm_clob where clb_id = '" & clob_id & "'" Set rs0 = ocn.CreateDynaset(sql0, 0)
Set infoClob = rs0.Fields("clb_content").Value
chunk = clb_value
chunksize = Len(chunk) rs0.Edit
infoClob.offset = 1
amount_written = infoClob.Write(chunk, chunksize)
rs0.Update
rs0.close
Set infoClob = nothing
end functioncall get_mdbtablename()set cn = nothing
set ocn = nothingrspend("成功导入数据!<a href=upload.asp>重新导入</a>")
%>