2002-08-01 12:00:01,如何把这样的数据格式加入SQL中,加入的时间字段格式为datetime,已经想了两天还是想不到,原理是这样的,从SQL中导出时间数据到EXCEL中,再把EXCEL中的时间再导入到数据库中就提示从字符串转换到datetime时出现错误代码如下:Public sql1 As String, sql2 As String, sql3 As String
Public conn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rs1 As New ADODB.Recordset
Public rs2 As New ADODB.Recordset
Public rs3 As New ADODB.Recordset
Public rs4 As New ADODB.Recordset
Public exlapp As New Excel.Application
Public exlbook As Excel.Workbook
Public exlsheet As Excel.Worksheet
Public rows As Integer, rows2 As Integer
Public i As Integer, j As Integer, k As IntegerPrivate Sub Command1_Click() Dim cn As New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\12.xls;Extended Properties='Excel 8.0;HDR=Yes'"If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select min(id) from app_ponderation", conn, 1, 1
sql1 = "SELECT * FROM [Sheet1$]"
Set rs = cn.Execute(sql1)
While Not rs.EOF
sql = "insert into app_ponderation(ID,IDcode,Mnumber,Cuscode,Truck,Re,Mdriver,Pdriver,OverFlag,Mweight,Pweight,Krate,Kweight,Mtime,Ptime,BillDate,gradecode,exportcode,addresscode,Casecode) " _
& "values('" & ("-" & fixsql(rs(0))) & "', " _
& "'" & fixsql(rs(1)) & "' ," _
& "'" & ("00" & fixsql(rs(2))) & "' ," _
& "'" & ("00" & fixsql(rs(3))) & "' ," _
& "'" & fixsql(rs(4)) & "' ," _
& "'" & fixsql(rs(5)) & "' ," _
& "'" & fixsql(rs(6)) & "' ," _
& "'" & fixsql(rs(7)) & "' ," _
& "'" & fixsql(rs(8)) & "' ," _
& "'" & fixsql(rs(9)) & "' ," _
& "'" & fixsql(rs(10)) & "' ," _
& "'" & fixsql(rs(11)) & "' ," _
& "'" & fixsql(rs(12)) & "' ," _
& "'" & Left(fixsql(rs(14)), 9) & "' ," _
& "'" & Left(fixsql(rs(15)), 9) & "' ," _
& "'" & fixsql(rs(16)) & "' ," _
& "'" & ("00" & fixsql(rs(17))) & "' ," _
& "'" & ("00" & fixsql(rs(18))) & "' ," _
& "'" & ("00" & fixsql(rs(19))) & "' ," _
& "'" & ("00" & fixsql(rs(20))) & "')"
conn.Execute (sql)
rs.MoveNext
Wend
conn.Close
Set conn = Nothing
cn.Close
Set cn = Nothing 'addsqldata
End Sub
Private Sub Form_Load()If conn.State <> 1 Then
conn.CursorLocation = adUseClient
conn.Open LoginDB
End If
End Sub
Sub addsqldata()
'If rs.State = adStateOpen Then rs.Close
'rs1.Open "select max(id) from app_ponderation", conn, 1, 1End Sub
Function fixsql(str)
Dim newstr
newstr = str
If IsNull(newstr) Then
newstr = ""
Else
newstr = Replace(newstr, "'", "''")
End If
fixsql = newstr
End Function
Public conn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rs1 As New ADODB.Recordset
Public rs2 As New ADODB.Recordset
Public rs3 As New ADODB.Recordset
Public rs4 As New ADODB.Recordset
Public exlapp As New Excel.Application
Public exlbook As Excel.Workbook
Public exlsheet As Excel.Worksheet
Public rows As Integer, rows2 As Integer
Public i As Integer, j As Integer, k As IntegerPrivate Sub Command1_Click() Dim cn As New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\12.xls;Extended Properties='Excel 8.0;HDR=Yes'"If rs1.State = adStateOpen Then rs1.Close
rs1.Open "select min(id) from app_ponderation", conn, 1, 1
sql1 = "SELECT * FROM [Sheet1$]"
Set rs = cn.Execute(sql1)
While Not rs.EOF
sql = "insert into app_ponderation(ID,IDcode,Mnumber,Cuscode,Truck,Re,Mdriver,Pdriver,OverFlag,Mweight,Pweight,Krate,Kweight,Mtime,Ptime,BillDate,gradecode,exportcode,addresscode,Casecode) " _
& "values('" & ("-" & fixsql(rs(0))) & "', " _
& "'" & fixsql(rs(1)) & "' ," _
& "'" & ("00" & fixsql(rs(2))) & "' ," _
& "'" & ("00" & fixsql(rs(3))) & "' ," _
& "'" & fixsql(rs(4)) & "' ," _
& "'" & fixsql(rs(5)) & "' ," _
& "'" & fixsql(rs(6)) & "' ," _
& "'" & fixsql(rs(7)) & "' ," _
& "'" & fixsql(rs(8)) & "' ," _
& "'" & fixsql(rs(9)) & "' ," _
& "'" & fixsql(rs(10)) & "' ," _
& "'" & fixsql(rs(11)) & "' ," _
& "'" & fixsql(rs(12)) & "' ," _
& "'" & Left(fixsql(rs(14)), 9) & "' ," _
& "'" & Left(fixsql(rs(15)), 9) & "' ," _
& "'" & fixsql(rs(16)) & "' ," _
& "'" & ("00" & fixsql(rs(17))) & "' ," _
& "'" & ("00" & fixsql(rs(18))) & "' ," _
& "'" & ("00" & fixsql(rs(19))) & "' ," _
& "'" & ("00" & fixsql(rs(20))) & "')"
conn.Execute (sql)
rs.MoveNext
Wend
conn.Close
Set conn = Nothing
cn.Close
Set cn = Nothing 'addsqldata
End Sub
Private Sub Form_Load()If conn.State <> 1 Then
conn.CursorLocation = adUseClient
conn.Open LoginDB
End If
End Sub
Sub addsqldata()
'If rs.State = adStateOpen Then rs.Close
'rs1.Open "select max(id) from app_ponderation", conn, 1, 1End Sub
Function fixsql(str)
Dim newstr
newstr = str
If IsNull(newstr) Then
newstr = ""
Else
newstr = Replace(newstr, "'", "''")
End If
fixsql = newstr
End Function
把它导出EXCEL后,再导入SQL就不行
& "'" & Left(fixsql(rs(15)), 9) & "' ," _
这两个字段
如果取左边9为数或者取右边8位数都可以加进数据库,但是同时把年月日,时分秒一起加进数据库就提示出错
得到日期用 Convert(Varchar(50),ziduan,103)
得到時間用 Convert(Varchar(50),字段(或者是變量),108)
==================================
爲什麽不行啊?
查詢不可以 插入時候用總可以吧
Dim oCon As New ADODB.Connection
Dim eCon As New ADODB.Connection
Dim rs As New Recordset
oCon.ConnectionString = Me.Adodc1.ConnectionString--連接SQL的 我用的Adodc得到連接字符串
eCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=false;Data Source=C:\Documents and Settings\zhangshuai\My Documents\MyWork\Shit.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
eCon.Open
oCon.Open
' rs.Open "Select * From Change", eCon, adOpenDynamic, adLockBatchOptimistic
Set rs = eCon.Execute("Select * From [Sheet1$]")
' Debug.Print rs(0)
If rs.EOF Then
eCon.Close
oCon.Close
Exit Sub
End If
oCon.Execute "Select * From ZhangShTry"
While Not rs.EOF
oCon.Execute "Insert Into ZhangShTry(Code,Date,Time) Values('" & rs(0) & "',Convert(Varchar(50),'" & rs(1) & "',103),Convert(Varchar(50),'" & rs(2) & "',108))"
rs.MoveNext
Wend
oCon.Close
eCon.Close
Exit Sub
Click_Err:
Debug.Print Err.Source, Err.Description
eCon.Close
oCon.Close
我用這個可以得到正確的結果 也沒有出現你說的為未定義錯誤 不知道你吧Convert放到了哪裏
Up