ִ�в���
���ĵ���ϸ���� cDataBase ��������IJ��������� INSERT��UPDATE��DELETE �ȡ�
? Ŀ¼
Exec ����
�����
Exec ��������ִ�� INSERT��UPDATE��DELETE �ȷDz�ѯ SQL ��䡣
�
vb
Function Exec(Optional RecordsAffected, Optional Options As Long = -1) As Boolean����˵��
| ���� | ���� | ˵�� |
|---|---|---|
RecordsAffected | Variant | ������Ӱ�����������ѡ�� |
Options | Long | ִ��ѡ���ѡ��Ĭ�� -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 IfINSERT ����
��������
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 IfINSERT SELECT
vb
' ����������������
If db.Sql("INSERT INTO users_backup SELECT * FROM users WHERE age > 30").Exec Then
Debug.Print "���ݸ��Ƴɹ�"
End IfUPDATE ����
��������
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 IfDELETE ����
����ɾ��
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ע������
������ 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��֧����������
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����˵��
| ���� | ���� | ˵�� |
|---|---|---|
TableName | String | ���������裩 |
Data | Collection | ���ݼ��ϣ�ÿ��Ԫ���� 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 & ")").Exec2. ʹ������֤һ����
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 If3. ��鷵��ֵ
vb
' ? �Ƽ���ʼ�ռ�鷵��ֵ
If db.Sql("INSERT INTO users (name) VALUES ('����')").Exec Then
' �ɹ�����
Else
' ������
End If4. ��ȡӰ������
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