发现只要EXCEL列中的内容大于256的就不能导如sql server中,后来在网上查到说,不能导如大于255的数据.请问有什么解决办法吗?
dim cn As New ADODB.Connection 'connection excel
dim con As New ADODB.Connection 'connection sql server
con.ConnectionString = "Provider=SQLOLEDB;Data Source=Testsqls01;Initial Catalog=EIR;User Id=EIRadmin;Password=eiradmin;"
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source='" & txt & "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"cn.Open
con.Open Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.Workbooks.Open("c:\a.xls")
For i = 1 To xlbook.Sheets.Count
If i = xlbook.Sheets.Count Then
rs.Open "select * From [" & xlbook.Sheets(i).Name & "$]", cn, adOpenDynamic, adLockPessimistic
While Not rs.EOF
If rs(0) <> "" And rs(1) <> "" Then
If rs(0) = "OFU-EN0013" Then
MsgBox (rs(1))
End If
tmpLocation = "Insert Into RULocation(RULabel,OPZoneGroupName,[Year],Week,LastUpdateDate,LastUpdateBy)Values('" & rs(0) & "','" & rs(1) & "','" & txtYear.Text & "','" & txtWeek.Text & "','" & Now() & "','david')" ','" & Now() & "'
con.Execute tmpLocation
End If
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
xlbook.Close False
xlApp.Quit
Set xlbook = Nothing
Set xlApp = Nothing
cn.Close
con.Close
dim cn As New ADODB.Connection 'connection excel
dim con As New ADODB.Connection 'connection sql server
con.ConnectionString = "Provider=SQLOLEDB;Data Source=Testsqls01;Initial Catalog=EIR;User Id=EIRadmin;Password=eiradmin;"
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source='" & txt & "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"cn.Open
con.Open Set xlApp = CreateObject("Excel.Application")
Set xlbook = xlApp.Workbooks.Open("c:\a.xls")
For i = 1 To xlbook.Sheets.Count
If i = xlbook.Sheets.Count Then
rs.Open "select * From [" & xlbook.Sheets(i).Name & "$]", cn, adOpenDynamic, adLockPessimistic
While Not rs.EOF
If rs(0) <> "" And rs(1) <> "" Then
If rs(0) = "OFU-EN0013" Then
MsgBox (rs(1))
End If
tmpLocation = "Insert Into RULocation(RULabel,OPZoneGroupName,[Year],Week,LastUpdateDate,LastUpdateBy)Values('" & rs(0) & "','" & rs(1) & "','" & txtYear.Text & "','" & txtWeek.Text & "','" & Now() & "','david')" ','" & Now() & "'
con.Execute tmpLocation
End If
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
xlbook.Close False
xlApp.Quit
Set xlbook = Nothing
Set xlApp = Nothing
cn.Close
con.Close
解决方案 »
- mscomm的inputmode属性
- 如何在VB6.0中快速读取剪贴板上的内容
- 50分,再问Datareport!!!(对熟悉的可能很简单)
- 如何实现条形码的打印和扫描?急!!!给分不吝啬。
- 无论是ado,还是dao,如何通过对象连接foxpro的数据库文件?连接字符串应该如何写?很急!!!
- 为什么我用activeskin后~~ 不能放其他控件的~ 例如Shape 还有 media player这些控件啊
- 请教iso是什么文件的扩展名,可以用什么文件打开?
- 请问SQL Server中自动递增的字段是什么?
- 各位哥哥来帮忙拉~~
- "Select 日期,SUM(金额) AS 总额 from M_money Group by 日期 "如何快速找到最大总额,我一时想不进去了!太累了!!
- 帮忙看看这个筛选的代码为什么不行``
- 如何在VB6.0里横向打印转换成纵向打印,急!!
rs.open str ,conn,3,1rs.addnewrs("")=excel'execel中对应的数据
.....
.....
rs.update
就如下所示,
rs.Fields.Append "Rulable", adVarChar, 2000
rs.Fields.Append "RuGroup", adVarChar, 2000
rs.Open "select * From [" & xlbook.Sheets(i).Name & "$]", cn, adOpenDynamic, adLockPessimistic
但我发现好象不可以.