clsinsert.cls

visual basic 关于数据查询操作练习很好的工具

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsINSERT"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

'This class provides an easier and more consistent way to build a SQL INSERT statement
'Developed by Daniel Chirca

Private Const cnstINSERT = "INSERT"
Private Const cnstINTO = "INTO"
Private Const cnstVALUES = "VALUES"
Private Const cnstSep = " "

Private m_TableName As String
Private m_INSERT As String

Private Type tValueType
  vValue As Variant
  eComparedDataType As eDataType
  bCompareValueNotTranslated As Boolean
End Type

Private m_Columns() As String
Private m_Values() As tValueType

Public Sub AddTable(strTableName As String)
  If Len(strTableName) > 0 Then
    m_TableName = strTableName
  End If
End Sub

Public Sub AddValue(varVal As Variant, _
                    Optional eValDataType As eDataType = eString, _
                    Optional bDoNotTranslate As Boolean = False)

  If Len(CStr(varVal)) > 0 Then
    ReDim Preserve m_Values(0 To UBound(m_Values) + 1)
    
    m_Values(UBound(m_Values)).vValue = varVal
    
    If Not IsMissing(eValDataType) Then
      m_Values(UBound(m_Values)).eComparedDataType = eValDataType
    Else
      m_Values(UBound(m_Values)).eComparedDataType = eString
    End If
    
    If Not IsMissing(bDoNotTranslate) Then
      m_Values(UBound(m_Values)).bCompareValueNotTranslated = bDoNotTranslate
    Else
      m_Values(UBound(m_Values)).bCompareValueNotTranslated = False
    End If
  End If
    
End Sub

Public Sub AddColumn(strColumnName As String)

  If Len(strColumnName) > 0 Then
    ReDim Preserve m_Columns(0 To UBound(m_Columns) + 1)
    
    m_Columns(UBound(m_Columns)) = strColumnName
  End If
    
End Sub

Public Function BuildINSERT() As String
  On Error GoTo ErrorHandler
    
  Dim StrTemp As String
  Dim i As Integer
  
  StrTemp = StrTemp + cnstINSERT + cnstSep + cnstINTO + cnstSep
  StrTemp = StrTemp + m_TableName
  
  If UBound(m_Columns) > 0 Then
    StrTemp = StrTemp + "("
    For i = 1 To UBound(m_Columns)
      StrTemp = StrTemp + m_Columns(i)
      If i <> UBound(m_Columns) Then
        StrTemp = StrTemp + "," + cnstSep
      Else
        StrTemp = StrTemp + ")"
      End If
    Next i
  End If
  
  If UBound(m_Values) > 0 Then
    StrTemp = StrTemp + cnstSep + cnstVALUES + "("
    For i = 1 To UBound(m_Values)
      With m_Values(i)
        If .bCompareValueNotTranslated = True Then
          StrTemp = StrTemp + CStr(.vValue)
        Else
          If Len(CStr(.vValue)) > 0 Then
            Select Case .eComparedDataType
              Case eString
                StrTemp = StrTemp + "'" + CStr(.vValue) + "'"
              Case eInteger
                StrTemp = StrTemp + Format(CStr(.vValue), "General number")
              Case eDate
                StrTemp = StrTemp + "'" + Format(CStr(.vValue), "mm/dd/yyyy") + "'"
              Case eDecimal
                StrTemp = StrTemp + Format(CStr(.vValue), "Standard")
              Case eBool
                If CBool(.vValue) = True Then
                  StrTemp = StrTemp + CStr(1)
                Else
                  StrTemp = StrTemp + CStr(0)
                End If
            End Select
          End If
        End If
        
        If i <> UBound(m_Values) Then
          StrTemp = StrTemp + "," + cnstSep
        Else
          StrTemp = StrTemp + ")"
        End If
      End With
    Next
  End If
  
  BuildINSERT = StrTemp
  
  Exit Function
  
ErrorHandler:
  On Error Resume Next
End Function

Private Sub Class_Initialize()
  Debug.Print "clsINSERT class instance started."

  m_INSERT = ""

  m_TableName = ""
  
  ReDim m_Values(0)
  ReDim m_Columns(0)
End Sub

Public Sub ClearSQL()
  ReDim m_Columns(0)
  ReDim m_Values(0)
End Sub

Private Sub Class_Terminate()
  Debug.Print "clsINSERT class instance closed."
End Sub