-
Notifications
You must be signed in to change notification settings - Fork 6
/
SQLUpdate.cls
executable file
·160 lines (113 loc) · 4.35 KB
/
SQLUpdate.cls
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
Persistable = 0 'NotPersistable
DataBindingBehavior = 0 'vbNone
DataSourceBehavior = 0 'vbNone
MTSTransactionMode = 0 'NotAnMTSObject
END
Attribute VB_Name = "SQLUpdate"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
' ___________________________________________________
'
' © Hi-Integrity Systems 2007. All rights reserved.
' www.hisystems.com.au - Toby Wicks
' ___________________________________________________
'
Option Explicit
Implements ISQLStatement
Private pobjFields As SQLFieldValues
Private pobjCopyFields As SQLUpdateCopyFields
Private pobjConditions As SQLConditions
Public ConnectionType As ConnectionTypeEnum
Public TableName As String
Public Property Set Fields(ByVal objFields As SQLFieldValues)
If objFields Is Nothing Then
RaiseError dboErrorObjectIsNothing
End If
Set pobjFields = objFields
End Property
Public Property Get Fields() As SQLFieldValues
Set Fields = pobjFields
End Property
Public Property Get CopyFields() As SQLUpdateCopyFields
Set CopyFields = pobjCopyFields
End Property
Public Property Set CopyFields(ByVal objValue As SQLUpdateCopyFields)
If objValue Is Nothing Then
RaiseError dboErrorObjectIsNothing
End If
Set pobjCopyFields = objValue
End Property
Public Property Get Where() As SQLConditions
Set Where = pobjConditions
End Property
Public Property Set Where(ByVal objValue As SQLConditions)
Set pobjConditions = objValue
End Property
Public Property Get SQL() As String
Attribute SQL.VB_UserMemId = 0
Dim intIndex As Integer
Dim strSQL As String
Dim strFieldValues As String
Dim strConditions As String
If Trim$(TableName) = vbNullString Then
RaiseError dboErrorGeneral, "TableName property has not been set."
End If
If pobjFields.Count = 0 And pobjCopyFields.Count = 0 Then
RaiseError dboErrorGeneral, "Field values have not been set."
End If
For intIndex = 1 To pobjFields.Count
With pobjFields(intIndex)
'Check the field name has been set. Can't really check whether the value has been set or not.
If Trim$(.Name) = vbNullString Then
RaiseError dboErrorGeneral, "Field name not set"
End If
strFieldValues = strFieldValues & _
SQLConvertIdentifierName(.Name, Me.ConnectionType) & " = " & SQLConvertValue(.Value, Me.ConnectionType)
End With
If intIndex <> pobjFields.Count Then
strFieldValues = strFieldValues & ", "
End If
Next
If pobjFields.Count > 0 And pobjCopyFields.Count > 0 Then
strFieldValues = strFieldValues & ", "
End If
For intIndex = 1 To pobjCopyFields.Count
With pobjCopyFields(intIndex)
strFieldValues = strFieldValues & _
SQLConvertIdentifierName(.DestinationFieldName, Me.ConnectionType) & " = " & SQLConvertIdentifierName(.SourceFieldName, Me.ConnectionType)
End With
If intIndex <> pobjCopyFields.Count Then
strFieldValues = strFieldValues & ", "
End If
Next
strSQL = _
"UPDATE " & SQLConvertIdentifierName(Trim$(Me.TableName), Me.ConnectionType) & " " & _
"SET " & strFieldValues
If Not pobjConditions Is Nothing Then
strConditions = pobjConditions.SQL(Me.ConnectionType)
If strConditions <> vbNullString Then
strSQL = strSQL & " WHERE " & strConditions
End If
End If
SQL = strSQL
End Property
Private Sub Class_Initialize()
ConnectionType = modMisc.ConnectionType
Set pobjFields = New SQLFieldValues
Set pobjCopyFields = New SQLUpdateCopyFields
Set pobjConditions = New SQLConditions
End Sub
Private Property Get ISQLStatement_ConnectionType() As ConnectionTypeEnum
ISQLStatement_ConnectionType = Me.ConnectionType
End Property
Private Property Let ISQLStatement_ConnectionType(ByVal RHS As ConnectionTypeEnum)
Me.ConnectionType = RHS
End Property
Private Property Get ISQLStatement_SQL() As String
ISQLStatement_SQL = Me.SQL
End Property