Skip to content

ִ�в���

���ĵ���ϸ���� cDataBase ��������޸IJ��������� INSERT��UPDATE��DELETE �ȡ�


? Ŀ¼


Exec ����

�����﷨

Exec ��������ִ�� INSERT��UPDATE��DELETE �ȷDz�ѯ SQL ��䡣

�﷨

vb
Function Exec(Optional RecordsAffected, Optional Options As Long = -1) As Boolean

����˵��

��������˵��
RecordsAffectedVariant������Ӱ�����������ѡ��
OptionsLongִ��ѡ���ѡ��Ĭ�� -1��

����ֵ

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

ʾ��

vb
' ִ�� INSERT ���
If db.Sql("INSERT INTO users (name, age) VALUES ('����', 25)").Exec Then
    Debug.Print "����ɹ�"
Else
    Debug.Print "����ʧ��: " & db.LastErr
End If

INSERT ����

��������

vb
' ���뵥����¼
If db.Sql("INSERT INTO users (name, age, email) VALUES ('����', 25, 'zhangsan@example.com')").Exec Then
    Debug.Print "����ɹ�"
End If

���������¼

vb
' ʹ�� VALUES �Ӿ�������
Dim sSql As String
sSql = "INSERT INTO users (name, age) VALUES " & _
       "('����', 25), " & _
       "('����', 30), " & _
       "('����', 28)"
If db.Sql(sSql).Exec Then
    Debug.Print "��������ɹ�"
End If

ʹ�ò���������

vb
' ʹ�ò�������ѯ���Ƽ�����ֹ SQL ע�룩
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

INSERT SELECT

vb
' ����������������
If db.Sql("INSERT INTO users_backup SELECT * FROM users WHERE age > 30").Exec Then
    Debug.Print "���ݸ��Ƴɹ�"
End If

UPDATE ����

��������

vb
' ���µ�����¼
If db.Sql("UPDATE users SET age = 26 WHERE id = 1").Exec Then
    Debug.Print "���³ɹ�"
End If

���¶�����¼

vb
' ��������
If db.Sql("UPDATE users SET status = 'active' WHERE age > 18").Exec Then
    Debug.Print "�������³ɹ�"
End If

ʹ�ò���������

vb
' ʹ�ò�������ѯ
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

��������

vb
' ������������
Dim sSql As String
sSql = "UPDATE users SET status = 'inactive' " & _
       "WHERE last_login < DATEADD(day, -30, GETDATE())"
If db.Sql(sSql).Exec Then
    Debug.Print "�����û��ѱ��"
End If

DELETE ����

����ɾ��

vb
' ɾ��������¼
If db.Sql("DELETE FROM users WHERE id = 1").Exec Then
    Debug.Print "ɾ���ɹ�"
End If

����ɾ��

vb
' ɾ��������¼
If db.Sql("DELETE FROM users WHERE age < 18").Exec Then
    Debug.Print "����ɾ���ɹ�"
End If

ʹ�ò�����ɾ��

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

��ձ�

vb
' ��ձ���ע�⣺��ɾ���������ݣ�
If db.Sql("DELETE FROM users").Exec Then
    Debug.Print "�������"
End If

' ����ʹ�� TRUNCATE�����죬�����ɻع���
If db.Sql("TRUNCATE TABLE users").Exec Then
    Debug.Print "�������"
End If

��ȡӰ������

RecordsAffected ����

vb
' ��ȡ��Ӱ�������
Dim lAffected As Long
If db.Sql("UPDATE users SET status = 'active' WHERE age > 18").Exec(lAffected) Then
    Debug.Print "������ " & lAffected & " ����¼"
End If

ʾ��

vb
' INSERT ����
Dim lAffected As Long
If db.Sql("INSERT INTO users (name) VALUES ('����')").Exec(lAffected) Then
    Debug.Print "������ " & lAffected & " ����¼"
End If

' UPDATE ����
If db.Sql("UPDATE users SET status = 'active'").Exec(lAffected) Then
    Debug.Print "������ " & lAffected & " ����¼"
End If

' DELETE ����
If db.Sql("DELETE FROM users WHERE age < 18").Exec(lAffected) Then
    Debug.Print "ɾ���� " & lAffected & " ����¼"
End If

��ȡ������ID

LastInsertId ����

LastInsertId ������ȡ��������������� ID��

�﷨

vb
Function LastInsertId() As Variant

֧�ֵ����ݿ�

  • SQL Server - ʹ�� SCOPE_IDENTITY()
  • MySQL - ʹ�� LAST_INSERT_ID()
  • Access - ʹ�� @@IDENTITY

ʾ��

vb
' ��������
If db.Sql("INSERT INTO users (name, age) VALUES ('����', 25)").Exec Then
    ' ��ȡ������� ID
    Dim lId As Variant
    lId = db.LastInsertId
    Debug.Print "���û� ID: " & lId
End If

ע������

  1. ������ INSERT ����������

    vb
    ' ? ��ȷ
    db.Sql("INSERT INTO users (name) VALUES ('����')").Exec
    Dim lId As Variant
    lId = db.LastInsertId
    
    ' ? ���������������
    db.Sql("INSERT INTO users (name) VALUES ('����')").Exec
    db.Sql("SELECT * FROM users").Query  ' ������� ID
    Dim lId As Variant
    lId = db.LastInsertId  ' ���ܻ�ȡ������ȷ�� ID
  2. ��֧����������

    vb
    ' ����������������
    CREATE TABLE users (
        id INT IDENTITY(1,1) PRIMARY KEY,  -- SQL Server
        name NVARCHAR(50)
    )

��������

BatchInsert ����

BatchInsert ���������������ݣ�ʹ������֤����һ���ԡ�

�﷨

vb
Function BatchInsert(ByVal TableName As String, ByVal Data As Collection) As Boolean

����˵��

��������˵��
TableNameString���������裩
DataCollection���ݼ��ϣ�ÿ��Ԫ���� Dictionary�����裩

ʾ��

vb
' ׼������
Dim colData As New Collection
Dim dictRow As Scripting.Dictionary

' ��һ��
Set dictRow = New Scripting.Dictionary
dictRow.Add "name", "����"
dictRow.Add "age", 25
dictRow.Add "email", "zhangsan@example.com"
colData.Add dictRow

' �ڶ���
Set dictRow = New Scripting.Dictionary
dictRow.Add "name", "����"
dictRow.Add "age", 30
dictRow.Add "email", "lisi@example.com"
colData.Add dictRow

' ��������
If db.BatchInsert("users", colData) Then
    Debug.Print "��������ɹ�"
Else
    Debug.Print "��������ʧ��: " & db.LastErr
End If

��������

vb
' ʹ��������������
db.TransBegin

Dim i As Long
For i = 1 To 100
    Dim sSql As String
    sSql = "UPDATE users SET status = 'active' WHERE id = " & i
    If Not db.Sql(sSql).Exec Then
        db.TransRollback
        Exit For
    End If
Next

If db.TransCommit Then
    Debug.Print "�������³ɹ�"
End If

�첽ִ��

Async ����

ʹ�� Async ���Կ����첽ִ�� SQL ��䡣

vb
' �첽ִ��
db.Sql("INSERT INTO users (name) VALUES ('����')").Async.Exec

' ��������¼�
Private Sub db_AsyncExecuteComplete(ByVal RecordsAffected As Long, _
                                    ByVal pError As ADODB.Error, _
                                    adStatus As ADODB.EventStatusEnum, _
                                    ByVal pCommand As ADODB.Command, _
                                    ByVal pRecordset As ADODB.Recordset, _
                                    ByVal pConnection As ADODB.Connection)
    If pError Is Nothing Then
        Debug.Print "�첽ִ�гɹ���Ӱ������: " & RecordsAffected
    Else
        Debug.Print "�첽ִ��ʧ��: " & pError.Description
    End If
End Sub

������

vb
' ִ�д�����
If Not db.Sql("INSERT INTO users (name) VALUES ('����')").Exec Then
    Debug.Print "ִ��ʧ��"
    Debug.Print "�������: " & db.LastErrNumber
    Debug.Print "��������: " & db.LastErrDescription
    Debug.Print "������Ϣ: " & db.LastErr
    Exit Sub
End If

���ʵ��

1. ʹ�ò�������ѯ

vb
' ? �Ƽ���ʹ�ò�������ѯ
db.Sql("INSERT INTO users (name, age) VALUES (?, ?)") _
    .Param("name", txtName.Text, VBMAN.adVarWChar) _
    .Param("age", CLng(txtAge.Text), VBMAN.adInteger) _
    .ExecParam

' ? ���Ƽ���ֱ��ƴ�� SQL������ SQL ע�룩
db.Sql("INSERT INTO users (name, age) VALUES ('" & txtName.Text & "', " & txtAge.Text & ")").Exec

2. ʹ������֤һ����

vb
' ? �Ƽ���ʹ������
db.TransBegin
db.Sql("INSERT INTO users (name) VALUES ('����')").Exec
db.Sql("INSERT INTO posts (user_id, title) VALUES (1, '����')").Exec
If Not db.TransCommit Then
    Debug.Print "����ʧ�ܣ��ѻع�"
End If

3. ��鷵��ֵ

vb
' ? �Ƽ���ʼ�ռ�鷵��ֵ
If db.Sql("INSERT INTO users (name) VALUES ('����')").Exec Then
    ' �ɹ�����
Else
    ' ������
End If

4. ��ȡӰ������

vb
' ? �Ƽ������Ӱ������
Dim lAffected As Long
If db.Sql("UPDATE users SET status = 'active'").Exec(lAffected) Then
    If lAffected > 0 Then
        Debug.Print "������ " & lAffected & " ����¼"
    Else
        Debug.Print "û�м�¼������"
    End If
End If

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

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