在VB程序中格式化SQL字符串
發(fā)表時(shí)間：2023-07-30 來(lái)源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]在寫(xiě)SQL語(yǔ)句時(shí),需要對(duì)不同類型的數(shù)據(jù)分別加上#號(hào),""號(hào)等來(lái)表示,用以下函數(shù),就可以實(shí)現(xiàn)操作的簡(jiǎn)化.不管是什么類型,只需用這個(gè)Q函數(shù)轉(zhuǎn)化一下,不需動(dòng)手加格式化符號(hào),就OK了.實(shí)...
在寫(xiě)SQL語(yǔ)句時(shí),需要對(duì)不同類型的數(shù)據(jù)分別加上#號(hào),""號(hào)等來(lái)表示,用以下函數(shù),就可以實(shí)現(xiàn)操作的簡(jiǎn)化.不管是什么類型,只需用這個(gè)Q函數(shù)轉(zhuǎn)化一下,不需動(dòng)手加格式化符號(hào),就OK了.實(shí)在是方便.本人一直在用它,實(shí)在是方便.
Function Q(ByVal SqlVariable As Variant) As String
'-----------------------------------------
' Notes: Useful in creating properly formatted SQL statements
' Usage: sql="select * from table where name= " & Q(vntName)
' 這個(gè)版本格式化適用于Access的變量，若支持其它數(shù)據(jù)庫(kù)或許需要對(duì)其進(jìn)行修改
'-----------------------------------------
On Error GoTo ErrTrap
Q = SqlVariable
'format the string
Select Case VarType(SqlVariable)
Case vbNull, vbEmpty
Q = "NULL"
Case vbString
Q = "'" & Replace(SqlVariable, "'", "''") & "'"
'date variable
Case vbDate
'format and enclose in pounds signs for Access
Q = "#" & Format$(SqlVariable, "general date") & "#"
'otherwise treat as numeric
Case Else
On Error Resume Next
Q = CStr(SqlVariable)
If Err.Number <> 0 Then Q = SqlVariable
End Select
Exit Function
ErrTrap:
On Error GoTo 0
End Function
