EXCEL记录写入SQL表

按钮事件:

View Code

Private Sub CommandButton1_Click()
Dim CN As New ADODB.Connection
Dim recs As Integer
Dim i As Integer
Dim sql1, sql2 As String
Dim j As Integer

Const conn = "Provider = SQLOLEDB;" & _
"Data Source = SVR12\SQLEXPRESS;" & _
"Initial Catalog = LINEE;User ID =sa;Password = pw;"
    CN.Open conn

recs = ActiveSheet.Range("C65535").End(xlUp).Row
i = 3

Do While i <= recs

        sql1 = "select count(*) from kbproduct where pdCyNo = '" & Range("B" + Trim(Str(i))) & "' and pdSldNo = '" & Range("C" + Trim(Str(i))) & " '"
        j = CN.Execute(sql1)(0).Value

If j = 1 Then  'update the current record
           sql2 = "update kbproduct set pdName = '" & Range("D" + Trim(Str(i))) & "'," + _
"pdSpec = '" & Range("E" + Trim(Str(i))) & "'," + _
"pdJM   = '" & Range("F" + Trim(Str(i))) & "'," + _
"pdBZ   = '" & Range("G" + Trim(Str(i))) & "'," + _
"pdJT   = '" & Range("H" + Trim(Str(i))) & "'," + _
"pdtotal= '" & Range("I" + Trim(Str(i))) & "' " + _
"where pdCyNo = '" & Range("B" + Trim(Str(i))) & "' and pdSldNo = '" & Range("C" + Trim(Str(i))) & " ' "
           CN.Execute sql2


Else  'insert as new record
          sql2 = "insert kbproduct (pdHot,pdCyNo,pdSldNo,pdName,pdSpec,pdJM,pdBZ,pdJT,pdtotal) values " + _
"( '" & Range("A" + Trim(Str(i))) & " '," + _
"'" & Range("B" + Trim(Str(i))) & " '," + _
"'" & Range("C" + Trim(Str(i))) & " '," + _
"'" & Range("D" + Trim(Str(i))) & " '," + _
"'" & Range("E" + Trim(Str(i))) & " '," + _
"'" & Range("F" + Trim(Str(i))) & " '," + _
"'" & Range("G" + Trim(Str(i))) & " '," + _
"'" & Range("H" + Trim(Str(i))) & " '," + _
"'" & Range("I" + Trim(Str(i))) & " ')"
           CN.Execute sql2

End If

        i = i + 1


Loop

MsgBox "产品数据导入完成!", vbInformation, "提示信息:"


End Sub

 

转载于:https://www.cnblogs.com/linmf/archive/2012/03/13/2393706.html