vba sql inset 疏失！

2013-01-05

vba sql inset 出错！！代码如下：Sub 按钮7_Click()Dim i As Integer, ii As Integer, j As Integer, sht As

vba sql inset 出错！！
代码如下：

Sub 按钮7_Click()

Dim i As Integer, ii As Integer, j As Integer, sht As Worksheet 'i,j为整数变量；sht 为excel工作表对象变量，指向某一工作表

Dim cn As New ADODB.Connection '定义数据链接对象，保存连接数据库信息；请先添加ADO引用

Dim rs As New ADODB.Recordset  '定义记录集对象，保存数据表

Dim strCn As String, strSQL As String, strSQL2 As String, strSQL3 As String '字符串变量

Dim ObjTmp As Object

strCn = "Provider=sqloledb;Server=192.168.0.5;Database=sinppa;Uid=sa;Pwd=;"    '定义数据库链接字符串

'下面的语句将读取数据表数据，并将它保存到excel工作表中：画两张表想像一下，工作表为一张两维表，记录集也是一张两维表

strSQL = "select MB002,MB003 FROM INVMB " _
         & "  WHERE MB001='" & Range("B5 ") & "' "

cn.Open strCn   '与数据库建立连接，如果成功，返回连接对象cn

rs.Open strSQL, cn  '执行strSQL所含的SQL命令，结果保存在rs记录集对象中

Set sht = ThisWorkbook.Worksheets("SHEET1")

strSQL = ""  '清空上面定义的变量

For i = 5 To 20 '循环开始，i从5到20

If sht.Cells(i, 11) = "" And sht.Cells(i, 2) <> "" Then

    strSQL = strSQL & "insert into [INVMB] ([MB001],[MB002]) values(" & sht.Cells(i, 2) & "," & sht.Cells(i, 3) & ") ;"
Else


End If

Next

'至此生成一串SQL命令串，保存的内容大概为：insert into 表名(字段) values(数值1);insert into 表名(字段) values(数值2);

cn.Execute strSQL '执行该SQL命令串，如果SQL命令没有错误，将在数据库中添加501个记录;也可以用rs.open strSQL,cn 执行

cn.Close  '关闭数据库链接,释放资源

错误提示：

[解决办法]
For i = 5 To 20 '循环开始，i从5到20
    If sht.Cells(i, 11) = "" And sht.Cells(i, 2) <> "" Then
        strSQL = "insert into [INVMB] ([MB001],[MB002]) values(" & sht.Cells(i, 2) & "," & sht.Cells(i, 3) & ")"
    cn.execute strSql
    End If
Next i

[解决办法]
请参阅

热点排行

VB

vba sql inset 疏失！