vba 如何编写从 Excel 工作表的单元格中获取输入的 sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15497742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to write sql queries which take input from cell of an excel sheet
提问by 565
I want to write a sql query which take an input from cell of an excel sheet.
我想编写一个 sql 查询，它从 Excel 工作表的单元格中获取输入。
For example if there are 3 columns like EmpID,EmpName,Salary,Address in the database and I would like to connect with the sql server database and update the database using a button in the excel sheet.
例如，如果数据库中有 3 列，如 EmpID、EmpName、Salary、Address，我想连接 sql server 数据库并使用 Excel 表中的按钮更新数据库。
I wrote a code like this
我写了这样的代码
Dim rst As ADODB.Recordset
Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim strSQL As String
' Open connection
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=SQLOLEDB;" & _
"Data Source=ABC\SQLEXPRESS;" & _
"Initial Catalog=Trail;" & _
"Integrated Security=SSPI;" & _
"Application Name=MyExcelFile"
Cnxn.Open strCnxn
Set rst = New ADODB.Recordset
Dim Ename As Variant
Ename = Worksheets("DBSheet").Cells(2, 2).Value
Dim Eno As Variant
Eno = Int(Worksheets("DBSheet").Cells(2, 1).Value)
strSQL = "UPDATE Employee SET EmpName='Micro' WHERE EmpID=1"
rst.Open strSQL, Cnxn, adOpenDynamic, adLockOptimistic, adCmdText
Cnxn.Close
Set rst = Nothing
Set Cnxn = Nothing
Here in the above code I Wrote a Update query like SET EmpName='Micro' WHERE EmpID=1, here i mentioned the EmpName and EmpID manually rather than that I would like to populate the update query with the cell values of an excel sheet like
在上面的代码中，我写了一个更新查询，如 SET EmpName='Micro' WHERE EmpID=1，这里我手动提到了 EmpName 和 EmpID，而不是我想用 Excel 表的单元格值填充更新查询，例如
strSQL = "UPDATE Employee SET EmpName=Worksheets("AB').Cells(2,2).Value WHERE EmpID=Worksheets("AB').Cells(2,1).Value"
But this is not working because , the EmpName and EmpID values are coming in the format of ""(double quotes)to the query but sqlquery would not accept any thing in that format in order to execute it should in the format of ''(single quotes).
但这不起作用，因为 EmpName 和 EmpID 值以“”（双引号）格式进入查询，但 sqlquery 不接受该格式的任何内容，以便执行它应该以 ''(单引号）。
So If any one know how to populate cell values of an excel sheet to the sql query please suggest me how to do that.
因此，如果有人知道如何将 Excel 工作表的单元格值填充到 sql 查询中，请建议我如何执行此操作。
回答by
Use This Code
使用此代码
strSQl="UPDATE Employee SET EmpName='" & sheets("AB").Cells(2,2).Value & "' WHERE EmpID='" & sheets("AB").Cells(2,1).Value & "'"
回答by user1644564
Use Parameters:
使用参数：
1) create a command object and initialise it.
1）创建一个命令对象并初始化它。
Dim cmd as adodb.command
set cmd = new adodb.command
2) change your SQL:
2）改变你的SQL：
strSQL = "UPDATE Employee SET EmpName= ? WHERE EmpID = ? "
3) create your parameters from your command object using the .createParameter function in the command object.
3) 使用命令对象中的 .createParameter 函数从命令对象创建参数。
dim parId as adodb.parameter
dim parEmp as adodb.parameter
set parId = new adodb.parameter
set parEmp = new adodb.parameter
set parId = cmd.createParameter(...)
set parEmp = cmd.createParameter(...)
4) add the parameter to the parameters collection in the command object.
4）将参数添加到命令对象中的参数集合中。
cmd.parameters.append(parId)
cmd.parameters.append(parEmp)
Note: add the parameters in the same order they are in the SQL statement.
注意：按照在 SQL 语句中的相同顺序添加参数。
5) open your recordset
5）打开你的记录集
rst.open cmd
6) loop and stuff
6）循环和东西
7) close and de-initialise everything.
7）关闭并取消初始化所有内容。
