Skip to content

��������ѯ

���ĵ���ϸ���� cDataBase ��IJ�������ѯ���ܣ����Ƿ�ֹ SQL ע�빥������Ҫ��ȫ���ԡ�


? Ŀ¼


��������ѯ����

ʲô�Dz�������ѯ

��������ѯ�ǽ� SQL ���Ͳ���ֵ�ֿ������ļ���������ֵͨ��ռλ����ͨ���� ?�����ݡ�

Ϊʲôʹ�ò�������ѯ

  1. ��ֹ SQL ע�� - ����ֵ�ᱻת�壬�޷�ִ�ж��� SQL ����
  2. �����Ż� - ���ݿ���Ի���ִ�мƻ�
  3. ���Ͱ�ȫ - �Զ�������������ת��
  4. �������� - SQL ���Ͳ���ֵ���룬����ά��

SQL ע��ʾ��

vb
' ? Σ�գ�ֱ��ƴ�� SQL������ SQL ע�룩
Dim sName As String
sName = "'; DROP TABLE users; --"
db.Sql("SELECT * FROM users WHERE name = '" & sName & "'").Query
' ʵ��ִ�е� SQL: SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
' �����users ����ɾ����

' ? ��ȫ��ʹ�ò�������ѯ
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", sName, VBMAN.adVarWChar) _
    .QueryParam
' ����ֵ�ᱻ��ȫ�������޷�ִ�ж������

Param ����

�﷨

vb
Function Param(ByVal ParamName As String, _
               ByVal ParamValue As Variant, _
               Optional ByVal ParamType As DataTypeEnum = adVarChar) As cDataBase

����˵��

��������˵��
ParamNameString�������ƣ����ڱ�ʶ��ʵ��ʹ�� ? ռλ����
ParamValueVariant����ֵ�����裩
ParamTypeDataTypeEnum�����������ͣ���ѡ��Ĭ�� adVarChar��

����ֵ

���� cDataBase ����֧����ʽ���á�

ʾ��

vb
' ���ӵ�������
db.Sql("SELECT * FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger)

' ���Ӷ����������ʽ���ã�
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
    .Param("name", "����", VBMAN.adVarWChar) _
    .Param("age", 18, VBMAN.adInteger)

ExecParam ����

�﷨

ExecParam ����ִ�д������� INSERT��UPDATE��DELETE ������

vb
Function ExecParam(Optional RecordsAffected) As Boolean

����˵��

��������˵��
RecordsAffectedVariant������Ӱ�����������ѡ��

����ֵ

  • True - ִ�гɹ�
  • False - ִ��ʧ�ܣ���ͨ�� LastErr �鿴������Ϣ��

ʾ��

vb
' ��������
If db.Sql("INSERT INTO users (name, age, email) VALUES (?, ?, ?)") _
    .Param("name", "����", VBMAN.adVarWChar) _
    .Param("age", 25, VBMAN.adInteger) _
    .Param("email", "zhangsan@example.com", VBMAN.adVarWChar) _
    .ExecParam Then
    
    Debug.Print "����ɹ���ID: " & db.LastInsertId
End If

' ��������
If db.Sql("UPDATE users SET age = ?, email = ? WHERE id = ?") _
    .Param("age", 26, VBMAN.adInteger) _
    .Param("email", "newemail@example.com", VBMAN.adVarWChar) _
    .Param("id", 1, VBMAN.adInteger) _
    .ExecParam Then
    
    Debug.Print "���³ɹ�"
End If

' ɾ������
If db.Sql("DELETE FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger) _
    .ExecParam Then
    
    Debug.Print "ɾ���ɹ�"
End If

QueryParam ����

�﷨

QueryParam ����ִ�д������� SELECT ��ѯ��

vb
Function QueryParam(Optional CurType As CursorTypeEnum = adOpenKeyset, _
                    Optional LockType As LockTypeEnum = adLockOptimistic) As Boolean

����˵��

��������˵��
CurTypeCursorTypeEnum�α����ͣ���ѡ��Ĭ�� adOpenKeyset��
LockTypeLockTypeEnum�������ͣ���ѡ��Ĭ�� adLockOptimistic��

����ֵ

  • True - ��ѯ�ɹ�������洢�� db.Rs ��
  • False - ��ѯʧ�ܣ���ͨ�� LastErr �鿴������Ϣ��

��Ҫ��QueryParam �������ص��Dz���ֵ������ Recordset����ѯ�����Ҫͨ�� db.Rs ���Է��ʡ�

ʾ��

vb
' ��ѯ������¼��ʹ�� db.Rs ���ʽ����
If db.Sql("SELECT * FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger) _
    .QueryParam Then
    
    ' ʹ�� db.Rs ���� Recordset
    If Not db.Rs.EOF Then
        Debug.Print db.Rs("name")
    End If
    db.Rs.Close
End If

' ��ѯ������¼��ʹ�� db.Rs ���ʽ����
If db.Sql("SELECT * FROM users WHERE age > ? AND status = ?") _
    .Param("age", 18, VBMAN.adInteger) _
    .Param("status", "active", VBMAN.adVarWChar) _
    .QueryParam Then
    
    ' ʹ�� db.Rs �������
    Do Until db.Rs.EOF
        Debug.Print db.Rs("name")
        db.Rs.MoveNext
    Loop
    db.Rs.Close
End If

' ���ʹ�� Fetch ���������Զ�ת��Ϊ db.Rows �� db.Row
If db.Sql("SELECT * FROM users WHERE id = ?") _
    .Param("id", 1, VBMAN.adInteger) _
    .QueryParam Then
    
    ' ʹ�� Fetch ����Է��� db.Rows �� db.Row
    If db.Fetch Then
        If db.Rows.Count > 0 Then
            Debug.Print db.Row("name")
        End If
    End If
End If

��������

������������

����ֵ˵��ʾ��
adVarChar200�ɱ䳤���ַ���"����"
adVarWChar202Unicode �ַ���"����"
adInteger332 ���25
adBigInt2064 ���1234567890
adDouble5˫���ȸ�����3.14
adDate7����ʱ��Now
adBoolean11����ֵTrue
adDecimal14��ȷ��ֵ99.99

��������ѡ��

vb
' �ַ���
db.Param("name", "����", VBMAN.adVarWChar)  ' Unicode �ַ������Ƽ���
db.Param("name", "����", VBMAN.adVarChar)   ' ANSI �ַ���

' ����
db.Param("age", 25, VBMAN.adInteger)        ' 32 ���
db.Param("id", 1234567890, VBMAN.adBigInt)   ' 64 ���

' ������
db.Param("price", 99.99, VBMAN.adDouble)    ' ˫����
db.Param("amount", 99.99, VBMAN.adDecimal)  ' ��ȷ��ֵ

' ����ʱ��
db.Param("created", Now, VBMAN.adDate)      ' ����ʱ��

' ����ֵ
db.Param("active", True, VBMAN.adBoolean)   ' ����ֵ

��ȫ����

SQL ע�����

vb
' ? Σ�գ�ֱ��ƴ��
Dim sInput As String
sInput = "'; DROP TABLE users; --"
db.Sql("SELECT * FROM users WHERE name = '" & sInput & "'").Query
' �����users ����ɾ��

' ? ��ȫ����������ѯ
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", sInput, VBMAN.adVarWChar) _
    .QueryParam
' ����ֵ����ȫ�������޷�ִ�ж������

�����ַ�����

vb
' ? Σ�գ������ַ����ܵ��´���
Dim sName As String
sName = "O'Brien"
db.Sql("SELECT * FROM users WHERE name = '" & sName & "'").Query
' SQL: SELECT * FROM users WHERE name = 'O'Brien'
' ���󣺵�����δת��

' ? ��ȫ����������ѯ�Զ�����
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", sName, VBMAN.adVarWChar) _
    .QueryParam
' �����ַ����Զ�ת��

ʹ��ʾ��

ʾ�� 1���û���¼

vb
Function UserLogin(sUsername As String, sPassword As String) As Boolean
    ' ʹ�ò�������ѯ��ֹ SQL ע��
    If db.Sql("SELECT * FROM users WHERE username = ? AND password = ?") _
        .Param("username", sUsername, VBMAN.adVarWChar) _
        .Param("password", sPassword, VBMAN.adVarWChar) _
        .QueryParam Then
        
        If db.Rows.Count > 0 Then
            UserLogin = True
        Else
            UserLogin = False
        End If
    Else
        UserLogin = False
    End If
End Function

ʾ�� 2����������

vb
Function SearchUsers(sKeyword As String) As Collection
    Set SearchUsers = New Collection
    
    ' ʹ�� LIKE �Ͳ�������ѯ
    Dim sSql As String
    sSql = "SELECT * FROM users WHERE name LIKE ? OR email LIKE ?"
    
    If db.Sql(sSql) _
        .Param("name", "%" & sKeyword & "%", VBMAN.adVarWChar) _
        .Param("email", "%" & sKeyword & "%", VBMAN.adVarWChar) _
        .QueryParam Then
        
        Set SearchUsers = db.Rows
    End If
End Function

ʾ�� 3����������

vb
Sub BatchInsertUsers(colUsers As Collection)
    db.TransBegin
    
    Dim i As Long
    For i = 1 To colUsers.Count
        Dim dictUser As Scripting.Dictionary
        Set dictUser = colUsers(i)
        
        If Not db.Sql("INSERT INTO users (name, age, email) VALUES (?, ?, ?)") _
            .Param("name", dictUser("name"), VBMAN.adVarWChar) _
            .Param("age", dictUser("age"), VBMAN.adInteger) _
            .Param("email", dictUser("email"), VBMAN.adVarWChar) _
            .ExecParam Then
            
            db.TransRollback
            Exit Sub
        End If
    Next
    
    db.TransCommit
End Sub

ʾ�� 4����̬��ѯ

vb
Function GetUsers(Optional sName As String = "", _
                  Optional lMinAge As Long = 0, _
                  Optional sStatus As String = "") As Collection
    Set GetUsers = New Collection
    
    Dim sSql As String
    sSql = "SELECT * FROM users WHERE 1=1"
    
    ' ��̬���� SQL �Ͳ���
    If sName <> "" Then
        sSql = sSql & " AND name LIKE ?"
    End If
    If lMinAge > 0 Then
        sSql = sSql & " AND age >= ?"
    End If
    If sStatus <> "" Then
        sSql = sSql & " AND status = ?"
    End If
    
    ' ���� SQL
    db.Sql(sSql)
    
    ' ���Ӳ���
    If sName <> "" Then
        db.Param("name", "%" & sName & "%", VBMAN.adVarWChar)
    End If
    If lMinAge > 0 Then
        db.Param("age", lMinAge, VBMAN.adInteger)
    End If
    If sStatus <> "" Then
        db.Param("status", sStatus, VBMAN.adVarWChar)
    End If
    
    ' ִ�в�ѯ
    If db.QueryParam Then
        Set GetUsers = db.Rows
    End If
End Function

���ʵ��

1. ʼ��ʹ�ò�������ѯ�����û�����

vb
' ? �Ƽ���ʹ�ò�������ѯ
db.Sql("SELECT * FROM users WHERE name = ?") _
    .Param("name", txtName.Text, VBMAN.adVarWChar) _
    .QueryParam

' ? ���Ƽ���ֱ��ƴ���û�����
db.Sql("SELECT * FROM users WHERE name = '" & txtName.Text & "'").Query

2. ѡ����ȷ����������

vb
' ? �Ƽ�����ȷָ����������
db.Param("age", 25, VBMAN.adInteger)
db.Param("name", "����", VBMAN.adVarWChar)
db.Param("price", 99.99, VBMAN.adDecimal)

' ? ���Ƽ���ʹ��Ĭ�����ͣ��������Ͳ�ƥ�䣩
db.Param("age", 25)  ' Ĭ�� adVarChar�����ܳ���

3. ʹ�� Unicode �ַ�������

vb
' ? �Ƽ���ʹ�� adVarWChar ֧������
db.Param("name", "����", VBMAN.adVarWChar)

' ? ���Ƽ���ʹ�� adVarChar�������������룩
db.Param("name", "����", VBMAN.adVarChar)

4. ����˳��Ҫ�� SQL �е�ռλ��˳��һ��

vb
' ? ��ȷ������˳���� ? ˳��һ��
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
    .Param("name", "����", VBMAN.adVarWChar) _
    .Param("age", 18, VBMAN.adInteger)

' ? ���󣺲���˳�����
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
    .Param("age", 18, VBMAN.adInteger) _
    .Param("name", "����", VBMAN.adVarWChar)

5. ��������

vb
' ע�⣺ExecParam �� QueryParam ���Զ���������
' ���ֻ���� Param ����ִ�У���Ҫ�ֶ�������ͨ��ִ�л����´�������

��������

Q1: ��������ѯ��ֱ��ƴ������

�ش�: ���ᣬ��������ѯͨ�����죬��Ϊ��

  • ���ݿ���Ի���ִ�мƻ�
  • ������ SQL ����ʱ��
  • �������ַ���ƴ�ӿ���

Q2: ����� LIKE ��ѯ��ʹ�ò�����

vb
' ? ��ȷ���ڲ���ֵ�а���ͨ���
db.Sql("SELECT * FROM users WHERE name LIKE ?") _
    .Param("name", "%" & sKeyword & "%", VBMAN.adVarWChar) _
    .QueryParam

' ? ������ SQL ��ʹ��ͨ���
db.Sql("SELECT * FROM users WHERE name LIKE '%?%'") _
    .Param("name", sKeyword, VBMAN.adVarWChar) _
    .QueryParam

Q3: ��δ��� NULL ֵ��

vb
' ʹ�� Null ֵ
db.Sql("SELECT * FROM users WHERE email = ?") _
    .Param("email", Null, VBMAN.adVarWChar) _
    .QueryParam

' ����ʹ�� IS NULL
db.Sql("SELECT * FROM users WHERE email IS NULL").Query

Q4: ��������ѯ֧�� IN �Ӿ���

vb
' ע�⣺ADO ��������ѯ�� IN �Ӿ�֧������
' ���飺ʹ�ö�� OR ������̬���� SQL

' ���� 1��ʹ�ö�� OR
db.Sql("SELECT * FROM users WHERE id = ? OR id = ? OR id = ?") _
    .Param("id1", 1, VBMAN.adInteger) _
    .Param("id2", 2, VBMAN.adInteger) _
    .Param("id3", 3, VBMAN.adInteger) _
    .QueryParam

' ���� 2����̬��������Ҫ��֤���룩
Dim sIds As String
sIds = "1,2,3"  ' ��Ҫ��֤��ʽ
db.Sql("SELECT * FROM users WHERE id IN (" & sIds & ")").Query

������: 2026-01-21

VB6及其LOGO版权为微软公司所有