尝试使用变量作为值 vba 到 sql [英] Trying to use variables as values vba to sql
问题描述
Dim connection As New adodb.connection
Dim sql As String
Dim years As Integer
years = 2011
所以我这里有一些很好的声明变量.现在我将展示完美运行的语句,没有问题:
So I've got some nice declared variables here. Now I'm going to show the statement that works perfectly, no problems:
sql = "insert into crimeData(reportYear) values(2011)"
connection.Execute sql
注意我在这一年进行了硬编码.现在,如果我尝试:
Notice I hardcoded in the year. Now, if I try:
sql = "insert into crimeData(reportYear) values(years)"
connection.Execute sql
将变量名替换为一个值,出现错误:No value given for an or more required parameters
Substituting the variable name in as a value, I get the error: No value given for one or more required parameters
无可否认,我假设一定是一个简单的语法问题,或者我缺少一些带有变量的元数据.请赐教.我如何在这里正确地交换变量(因此我可以以编程方式使用它,而不是在值中进行硬编码)
Admittedly, I'm assuming there's got to be a simple syntax issue, or some meta with variables I'm missing. Please enlighten me. How can I correctly swap in a variable here (so I can use this programatically, as opposed to hard coding in values)
推荐答案
如果您对连接 SQL 有更高要求的需求,您可能会受益于此功能:
If you have a more demanding need for concatenating SQL, you may benefit from this function:
' Converts a value of any type to its string representation.
' The function can be concatenated into an SQL expression as is
' without any delimiters or leading/trailing white-space.
'
' Examples:
' SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
' SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
'
' SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
' SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
'
' Trims text variables for leading/trailing Space and secures single quotes.
' Replaces zero length strings with Null.
' Formats date/time variables as safe string expressions.
' Uses Str to format decimal values to string expressions.
' Returns Null for values that cannot be expressed with a string expression.
'
' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
' 2019-12-31. Modified to compile in both 32- and 64-bit VBA.
'
Public Function CSql( _
ByVal Value As Variant) _
As String
#If Win32 Then
' Serves only to make the code compile unmodified in 32-bit VBA
' which misses the constant VBA.vbLongLong.
Const vbLongLong As Integer = 20
#End If
Const SqlNull As String = " Null"
Dim Sql As String
Select Case VarType(Value)
Case vbEmpty ' 0 Empty (uninitialized).
Sql = SqlNull
Case vbNull ' 1 Null (no valid data).
Sql = SqlNull
Case vbInteger ' 2 Integer.
Sql = Str(Value)
Case vbLong ' 3 Long integer.
Sql = Str(Value)
Case vbSingle ' 4 Single-precision floating-point number.
Sql = Str(Value)
Case vbDouble ' 5 Double-precision floating-point number.
Sql = Str(Value)
Case vbCurrency ' 6 Currency.
Sql = Str(Value)
Case vbDate ' 7 Date.
Sql = Format(Value, " #yyyy/mm/dd hh:nn:ss#")
Case vbString ' 8 String.
Sql = Replace(Trim(Value), "'", "''")
If Sql = "" Then
Sql = SqlNull
Else
Sql = " '" & Sql & "'"
End If
Case vbObject ' 9 Object.
Sql = SqlNull
Case vbError ' 10 Error.
Sql = SqlNull
Case vbBoolean ' 11 Boolean.
Sql = Str(Abs(Value))
Case vbVariant ' 12 Variant (used only with arrays of variants).
Sql = SqlNull
Case vbDataObject ' 13 A data access object.
Sql = SqlNull
Case vbDecimal ' 14 Decimal.
Sql = Str(Value)
Case vbByte ' 17 Byte.
Sql = Str(Value)
Case vbLongLong ' 20 LongLong integer (Relevant in 64-bit VBA only).
Sql = Str(Value)
Case vbUserDefinedType ' 36 Variants that contain user-defined types.
Sql = SqlNull
Case vbArray ' 8192 Array. Ignored.
Sql = SqlNull
Case Else ' Should not happen.
Sql = SqlNull
End Select
CSql = Sql & " "
End Function
这篇关于尝试使用变量作为值 vba 到 sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!