excel - Excel VBA 分割代码行 - ADO 连接
问题描述
我有一些代码从 xl 工作表中获取数据并将其发送到 SQL 服务器表。
在我尝试将代码拆分为单独的行之前,一切正常。尝试了各种各样的东西,但似乎没有任何效果。
我试图分割的那条线
"values ('" & strFirstName & "', '" & strLastName & " ')"
谢谢。
代码如下:
Sub TEST_UPLOAD() 'TRUSTED CONNECTION On Error GoTo errH
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strPath As String
Dim intImportRow As Integer
Dim strFirstName, strLastName As String
Dim server, username, password, table, database As String
With Sheets("Sheet1")
server = "QQQQQQ"
table = "test1"
database = "QQQQDB"
If con.State <> 1 Then
con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
'con.Open
End If
'this is the TRUSTED connection string
Set rs.ActiveConnection = con
'delete all records first if checkbox checked
'If .CheckBox1 Then
con.Execute "delete from test1"
'End If
'set first row with records to import
'you could also just loop thru a range if you want.
intImportRow = 2
Do Until .Cells(intImportRow, 1) = ""
strFirstName = .Cells(intImportRow, 1)
strLastName = .Cells(intImportRow, 2)
'insert row into database
con.Execute "insert into test1 (firstname, lastname) " & _
"values ('" & strFirstName & "', '" & strLastName & " ')"
intImportRow = intImportRow + 1
Loop
MsgBox "Done importing", vbInformation
con.Close
Set con = Nothing
End With
退出子
解决方案
您可以通过使用参数化查询来避免将数据与代码混合。
Sub TEST_UPLOAD()
Const server = "QQQQQQ"
Const database = "QQQQDB"
Const table = "test1"
' sql with placeholders ? for values
Const SQL = " INSERT INTO " & table & _
" (firstname, lastname) " & _
" VALUES (?,?)"
Dim con As New ADODB.Connection, sCon As String
Dim intImportRow As Integer
sCon = "Provider=SQLOLEDB;Data Source=" & server & _
";Initial Catalog=" & database & _
";Integrated Security=SSPI;"
If con.State <> 1 Then
con.Open sCon
End If
' prepare sql with parameters
Dim cmd As New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = SQL
.Parameters.Append .CreateParameter("P1", adVarChar, adParamInput, 50) ' varchar(50)
.Parameters.Append .CreateParameter("P2", adVarChar, adParamInput, 50)
End With
' clear table
con.Execute "DELETE FROM " & table
intImportRow = 2
With Sheets("Sheet1")
' execute sql with parameter values
Do Until .Cells(intImportRow, 1) = ""
cmd.Parameters(0) = .Cells(intImportRow, 1) ' firstname
cmd.Parameters(1) = .Cells(intImportRow, 2) ' lastname
cmd.Execute
intImportRow = intImportRow + 1
Loop
End With
MsgBox intImportRow - 2 & " rows imported", vbInformation
con.Close
Set con = Nothing
End Sub
推荐阅读
- python - 使用python永久更改浏览器(chrome、firefox和edge)的下载路径
- java - 生成不包含重复项的一维随机 int 数组固定范围
- javascript - 无法使用 babel-loader 编译符号链接包的 TypeScript
- python-3.x - 不允许用户在输入中输入字符串
- vue.js - 如何修复生产中找不到的 vue 动态异步组件?
- python-3.x - 从 URL Python 从 XML 中获取特定元素
- python - 使用 Pythons Scikit-Learn lib 在调查中检测异常值
- python - 有没有办法在类中的函数之间进行转换?
- javascript - 在 React Redux 中将之前的状态与新的状态进行比较
- php - 将简短描述内容添加到 Hook