Dim Cnn2 As ADODB.Connection
Set Cnn2 = New ADODB.Connection
Cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select * from [" & sheetname & "$]", Cnn2, adOpenKeyset, adLockOptimistic
While Not rs.EOF
sqlstr = "insert into [result]([ID],[姓名],[性别],[年龄]) values ('" & rs.Fields(0) & "','" & rs.Fields(1) & "','" & rs.Fields(2) & "','" & rs.Fields(3) & "','" & rs.Fields(4) ')"
Cnn.Execute (sqlstr)
rs.MoveNext
Wend
上面代码用于向已打开的cnn的表result,导入excel表,当excel表中数据完整时,导入没有问题,但当excel中年龄空缺时导入,出现错误“标准表达式中数据类型不匹配”,如何解决这个问题?谢谢!
sqlstr = "insert into [result]([ID],[姓名],[性别],[年龄]) values ('" & rs.Fields(0) & "','" & rs.Fields(1) & "','" & rs.Fields(2) & "','" & rs.Fields(3) &')"
Cnn.Execute (sqlstr)
rs.MoveNext
Wend
Set Cnn2 = New ADODB.Connection
Cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select * from [" & sheetname & "$]", Cnn2, adOpenKeyset, adLockOptimistic
While Not rs.EOF
sqlstr = "insert into [result]([ID],[姓名],[性别],[年龄]) values ('" & rs.Fields(0) & "','" & rs.Fields(1) & "','" & rs.Fields(2) & "','" & IIf(IsNull(rs.Fields(3)), "", rs.Fields(3)) & "')"
Cnn.Execute (sqlstr)
rs.MoveNext
Wend
还是“标准表达式中数据类型不匹配”,改成IIf(IsNull(rs.Fields(3)), null, rs.Fields(3))也不行,改成IIf(IsNull(rs.Fields(3)), 100, rs.Fields(3))可以通过,但没用。to of123:
实时错误94,无效使用null
Set Cnn2 = New ADODB.Connection
Cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select * from [" & sheetname & "$]", Cnn2, adOpenKeyset, adLockOptimistic
While Not rs.EOF
sqlstr = "insert into [result]([ID],[姓名],[性别],[年龄]) values ('" & rs.Fields(0) & "','" & rs.Fields(1) & "','" & rs.Fields(2) & "'," & IIf(IsNull(rs.Fields(3)), "null", rs.Fields(3)) & ")"
Cnn.Execute (sqlstr)
rs.MoveNext
Wend
还是不行呀