下面是我写的第一个类,有几个方法可以添加数据记录,修改删除等,我想让大家看看,指教批评一番,谢谢! '保持属性值的局部变量 Private ConnG As New ADODB.Connection Private mvarW_server As String '局部复制 Private mvarW_user As String '局部复制 Private mvarw_psw As String '局部复制 Private mvarW_bach As Variant '局部复制 Public Function Job_Add(Job_name As String, Job_date As Variant, Job_Time As String, Job_Synopsis As String, Job_Trust As String, Job_Re As String) As Boolean Dim Sql As String '对应部门添加的方法 Dim Rs As New ADODB.Recordset On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) Sql = "INSERT INTO JOB (JobName, JobTime, JobStart, JobSynopsis, JobTrust, JobRe) VALUES ('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')" Set Rs = opentable(ConnG, Sql) Job_Add = True Exit Function err: End Function Public Function User_Add(Us_ID As String, Us_Name As String, Us_Job As String, Us_Txt As String, Us_All As String) As Boolean Dim Sql As String '对应人事添加的方法 Dim Rs As New ADODB.Recordset On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) Sql = "INSERT INTO [User] (UserID, UserName, UserJOB, UserText, UserAll) VALUES ('" & Us_ID & "', '" & Us_Name & "', '" & Us_Job & "', '" & Us_Txt & "', '" & Us_All & "')" Set Rs = opentable(ConnG, Sql) User_Add = True Exit Function err: End Function Public Function Enrol_Add(E_Unit As String, E_Date As Variant, E_Name As String, E_Text As String, E_User As String) As Boolean Dim Sql As String '对应部门添加的方法 Dim Rs As New ADODB.Recordset On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) Sql = "INSERT INTO Enrol (EnrolUnit, EnrolDate, EnrolName, EnrolText, EnrolUser) VALUES ('" & E_Unit & "', '" & E_Date & "', '" & E_Name & "', '" & E_Text & "', '" & E_User & "')" Set Rs = opentable(ConnG, Sql) Enrol_Add = True Exit Function err: End Function Public Function Job_edit(Job_ID As Integer, Job_name As String, Job_date As Variant, Job_Time As String, Job_Synopsis As String, Job_Trust As String, Job_Re As String) As Boolean Dim Sql As String '对应部门添加的方法 Dim Rs As New ADODB.Recordset On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) 'Sql = "INSERT INTO JOB (JobName, JobTime, JobStart, JobSynopsis, JobTrust, JobRe) VALUES ('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')" '('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')" Sql = "SELECT * FROM JOB WHERE id ='" & Job_ID & "'" Set Rs = opentable(ConnG, Sql) Rs!JobName = Job_name Rs!JobTime = Job_Time Rs!JobStart = Job_date Rs!JobSynopsis = Job_Synopsis Rs!JobTrust = Job_Trust Rs!JobRe = Job_Re Rs.Update Rs.Close Job_edit = True Exit Function err: End Function Public Function Enrol_Edit(UpdataId As Integer, E_Unit As Single, E_Date As Date, E_Name As String, E_Text As String, E_User As String) As Boolean Dim Sql As String '对应部门添加的方法 Dim Rs As New ADODB.Recordset 'EnrolUnit 类型 'EnrolDate 时间 'EnrolName 事件名称 'EnrolText 事件说明 'EnrolUser 事件发生人 On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) Sql = "SELECT * FROM Enrol WHERE id ='" & UpdataId & "'" Set Rs = opentable(ConnG, Sql) Rs!EnrolUnit = E_Unit Rs!EnrolDate = E_Date Rs!EnrolName = E_Name Rs!EnrolText = E_Text Rs!EnrolUser = E_User Rs.Update Rs.Close Enrol_Edit = True Exit Function err: End Function Public Function User_edit(Ka_ID As Integer, Us_ID As String, Us_Name As String, Us_Job As String, Us_Txt As String, Us_All As String) As Boolean Dim Sql As String '对应部门添加的方法 Dim Rs As New ADODB.Recordset On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) 'Sql = "INSERT INTO JOB (JobName, JobTime, JobStart, JobSynopsis, JobTrust, JobRe) VALUES ('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')" '('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')" Sql = "SELECT * FROM [User] WHERE id ='" & Ka_ID & "'" Set Rs = opentable(ConnG, Sql) Rs!UserID = Us_ID Rs!UserName = Us_Name Rs!UserJOB = Us_Job Rs!UserText = Us_Txt Rs!UserAll = Us_All Rs.Update Rs.Close User_edit = True Exit Function err: End Function Public Function Job_Dell(Job_name) As Boolean Dim Sql As String '对应部门删除的方法 Dim Rs As New ADODB.Recordset On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) Sql = "DELETE FROM JOB WHERE JobName='" & Job_name & "'" Set Rs = opentable(ConnG, Sql) Job_Dell = True Exit Function err: End Function Public Function Enrol_Dell(Dell_ID) As Boolean Dim Sql As String '对应部门删除的方法 Dim Rs As New ADODB.Recordset On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) Sql = "DELETE FROM JOB WHERE id='" & Dell_ID & "'" Set Rs = opentable(ConnG, Sql) Enrol_Dell = True Exit Function err: End Function Public Function User_Dell(User_ID As String) As Boolean Dim Sql As String '对应部门删除的方法 Dim Rs As New ADODB.Recordset On Error GoTo err: Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach) Sql = "DELETE FROM [User] WHERE UserID='" & User_ID & "'" Set Rs = opentable(ConnG, Sql) User_Dell = True Exit Function err: End Function Public Property Let W_bach(ByVal vData As Variant) '向属性指派值时使用,位于赋值语句的左边。 'Syntax: X.W_bach = 5 mvarW_bach = vData End Property Public Property Set W_bach(ByVal vData As Variant) '向属性指派对象时使用,位于 Set 语句的左边。 'Syntax: Set x.W_bach = Form1 Set mvarW_bach = vData End Property Public Property Get W_bach() As Variant '检索属性值时使用,位于赋值语句的右边。 'Syntax: Debug.Print X.W_bach If IsObject(mvarW_bach) Then Set W_bach = mvarW_bach Else W_bach = mvarW_bach End If End PropertyPublic Property Let w_psw(ByVal vData As String) '向属性指派值时使用,位于赋值语句的左边。 'Syntax: X.w_psw = 5 mvarw_psw = vData End Property Public Property Get w_psw() As String '检索属性值时使用,位于赋值语句的右边。 'Syntax: Debug.Print X.w_psw w_psw = mvarw_psw End PropertyPublic Property Let W_user(ByVal vData As String) '向属性指派值时使用,位于赋值语句的左边。 'Syntax: X.W_user = 5 mvarW_user = vData End Property Public Property Get W_user() As String '检索属性值时使用,位于赋值语句的右边。 'Syntax: Debug.Print X.W_user W_user = mvarW_user End PropertyPublic Property Let W_server(ByVal vData As String) '向属性指派值时使用,位于赋值语句的左边。 'Syntax: X.W_server = 5 mvarW_server = vData End Property Public Property Get W_server() As String '检索属性值时使用,位于赋值语句的右边。 'Syntax: Debug.Print X.W_server W_server = mvarW_server End Property Private Function ConnServer(ByVal server As String, ByVal user As String, ByVal psw As String, ByVal bach As String) As ADODB.Connection Dim mvarConntion As ADODB.Connection Dim temp As String mvarHaveErr = False Set mvarConntion = New ADODB.Connection LinkString = "PROVIDER=MSDASQL;driver=SQL Server;" & _ "server=" & server & ";uid=" & user & _ ";pwd=" & psw & ";database=" & bach & ";" temp = LinkString On Error GoTo err mvarConntion.Open temp Set ConnServer = mvarConntion Exit Function err: MsgBox "无法连接到服务器!" + vbCrLf + err.Description, vbOKOnly + vbCritical, "系统错误" & err.Number err.Clear End Function Private Function opentable(Conn As ADODB.Connection, ByVal SQLCommand As String) As ADODB.Recordset Dim cmd As New ADODB.Command Dim Rs As New ADODB.Recordset On Error GoTo Endme Rs.CursorLocation = adUseClient Rs.CursorType = adOpenKeyset Rs.LockType = adLockOptimistic '**************************** Rs.Open SQLCommand, Conn, , , adCmdText Set opentable = Rs Exit Function Endme: MsgBox "无法打开数据库!" & vbCrLf & err.Description, vbOKOnly + vbCritical, "系统错误" & err.Number err.Clear End Function
'保持属性值的局部变量
Private ConnG As New ADODB.Connection
Private mvarW_server As String '局部复制
Private mvarW_user As String '局部复制
Private mvarw_psw As String '局部复制
Private mvarW_bach As Variant '局部复制
Public Function Job_Add(Job_name As String, Job_date As Variant, Job_Time As String, Job_Synopsis As String, Job_Trust As String, Job_Re As String) As Boolean
Dim Sql As String '对应部门添加的方法
Dim Rs As New ADODB.Recordset
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
Sql = "INSERT INTO JOB (JobName, JobTime, JobStart, JobSynopsis, JobTrust, JobRe) VALUES ('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')"
Set Rs = opentable(ConnG, Sql)
Job_Add = True
Exit Function
err:
End Function
Public Function User_Add(Us_ID As String, Us_Name As String, Us_Job As String, Us_Txt As String, Us_All As String) As Boolean
Dim Sql As String '对应人事添加的方法
Dim Rs As New ADODB.Recordset
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
Sql = "INSERT INTO [User] (UserID, UserName, UserJOB, UserText, UserAll) VALUES ('" & Us_ID & "', '" & Us_Name & "', '" & Us_Job & "', '" & Us_Txt & "', '" & Us_All & "')"
Set Rs = opentable(ConnG, Sql)
User_Add = True
Exit Function
err:
End Function
Public Function Enrol_Add(E_Unit As String, E_Date As Variant, E_Name As String, E_Text As String, E_User As String) As Boolean
Dim Sql As String '对应部门添加的方法
Dim Rs As New ADODB.Recordset
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
Sql = "INSERT INTO Enrol (EnrolUnit, EnrolDate, EnrolName, EnrolText, EnrolUser) VALUES ('" & E_Unit & "', '" & E_Date & "', '" & E_Name & "', '" & E_Text & "', '" & E_User & "')"
Set Rs = opentable(ConnG, Sql)
Enrol_Add = True
Exit Function
err:
End Function
Public Function Job_edit(Job_ID As Integer, Job_name As String, Job_date As Variant, Job_Time As String, Job_Synopsis As String, Job_Trust As String, Job_Re As String) As Boolean
Dim Sql As String '对应部门添加的方法
Dim Rs As New ADODB.Recordset
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
'Sql = "INSERT INTO JOB (JobName, JobTime, JobStart, JobSynopsis, JobTrust, JobRe) VALUES ('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')"
'('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')"
Sql = "SELECT * FROM JOB WHERE id ='" & Job_ID & "'"
Set Rs = opentable(ConnG, Sql)
Rs!JobName = Job_name
Rs!JobTime = Job_Time
Rs!JobStart = Job_date
Rs!JobSynopsis = Job_Synopsis
Rs!JobTrust = Job_Trust
Rs!JobRe = Job_Re
Rs.Update
Rs.Close
Job_edit = True
Exit Function
err:
End Function
Public Function Enrol_Edit(UpdataId As Integer, E_Unit As Single, E_Date As Date, E_Name As String, E_Text As String, E_User As String) As Boolean
Dim Sql As String '对应部门添加的方法
Dim Rs As New ADODB.Recordset
'EnrolUnit 类型
'EnrolDate 时间
'EnrolName 事件名称
'EnrolText 事件说明
'EnrolUser 事件发生人
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
Sql = "SELECT * FROM Enrol WHERE id ='" & UpdataId & "'"
Set Rs = opentable(ConnG, Sql)
Rs!EnrolUnit = E_Unit
Rs!EnrolDate = E_Date
Rs!EnrolName = E_Name
Rs!EnrolText = E_Text
Rs!EnrolUser = E_User
Rs.Update
Rs.Close
Enrol_Edit = True
Exit Function
err:
End Function
Public Function User_edit(Ka_ID As Integer, Us_ID As String, Us_Name As String, Us_Job As String, Us_Txt As String, Us_All As String) As Boolean
Dim Sql As String '对应部门添加的方法
Dim Rs As New ADODB.Recordset
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
'Sql = "INSERT INTO JOB (JobName, JobTime, JobStart, JobSynopsis, JobTrust, JobRe) VALUES ('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')"
'('" & Job_name & "', '" & Job_Time & "', '" & Job_date & "', '" & Job_Synopsis & "', '" & Job_Trust & "', '" & Job_Re & "')"
Sql = "SELECT * FROM [User] WHERE id ='" & Ka_ID & "'"
Set Rs = opentable(ConnG, Sql)
Rs!UserID = Us_ID
Rs!UserName = Us_Name
Rs!UserJOB = Us_Job
Rs!UserText = Us_Txt
Rs!UserAll = Us_All
Rs.Update
Rs.Close
User_edit = True
Exit Function
err:
End Function
Public Function Job_Dell(Job_name) As Boolean
Dim Sql As String '对应部门删除的方法
Dim Rs As New ADODB.Recordset
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
Sql = "DELETE FROM JOB WHERE JobName='" & Job_name & "'"
Set Rs = opentable(ConnG, Sql)
Job_Dell = True
Exit Function
err:
End Function
Public Function Enrol_Dell(Dell_ID) As Boolean
Dim Sql As String '对应部门删除的方法
Dim Rs As New ADODB.Recordset
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
Sql = "DELETE FROM JOB WHERE id='" & Dell_ID & "'"
Set Rs = opentable(ConnG, Sql)
Enrol_Dell = True
Exit Function
err:
End Function
Public Function User_Dell(User_ID As String) As Boolean
Dim Sql As String '对应部门删除的方法
Dim Rs As New ADODB.Recordset
On Error GoTo err:
Set ConnG = ConnServer(mvarW_server, mvarW_user, mvarw_psw, mvarW_bach)
Sql = "DELETE FROM [User] WHERE UserID='" & User_ID & "'"
Set Rs = opentable(ConnG, Sql)
User_Dell = True
Exit Function
err:
End Function
Public Property Let W_bach(ByVal vData As Variant)
'向属性指派值时使用,位于赋值语句的左边。
'Syntax: X.W_bach = 5
mvarW_bach = vData
End Property
Public Property Set W_bach(ByVal vData As Variant)
'向属性指派对象时使用,位于 Set 语句的左边。
'Syntax: Set x.W_bach = Form1
Set mvarW_bach = vData
End Property
Public Property Get W_bach() As Variant
'检索属性值时使用,位于赋值语句的右边。
'Syntax: Debug.Print X.W_bach
If IsObject(mvarW_bach) Then
Set W_bach = mvarW_bach
Else
W_bach = mvarW_bach
End If
End PropertyPublic Property Let w_psw(ByVal vData As String)
'向属性指派值时使用,位于赋值语句的左边。
'Syntax: X.w_psw = 5
mvarw_psw = vData
End Property
Public Property Get w_psw() As String
'检索属性值时使用,位于赋值语句的右边。
'Syntax: Debug.Print X.w_psw
w_psw = mvarw_psw
End PropertyPublic Property Let W_user(ByVal vData As String)
'向属性指派值时使用,位于赋值语句的左边。
'Syntax: X.W_user = 5
mvarW_user = vData
End Property
Public Property Get W_user() As String
'检索属性值时使用,位于赋值语句的右边。
'Syntax: Debug.Print X.W_user
W_user = mvarW_user
End PropertyPublic Property Let W_server(ByVal vData As String)
'向属性指派值时使用,位于赋值语句的左边。
'Syntax: X.W_server = 5
mvarW_server = vData
End Property
Public Property Get W_server() As String
'检索属性值时使用,位于赋值语句的右边。
'Syntax: Debug.Print X.W_server
W_server = mvarW_server
End Property
Private Function ConnServer(ByVal server As String, ByVal user As String, ByVal psw As String, ByVal bach As String) As ADODB.Connection
Dim mvarConntion As ADODB.Connection
Dim temp As String
mvarHaveErr = False
Set mvarConntion = New ADODB.Connection
LinkString = "PROVIDER=MSDASQL;driver=SQL Server;" & _
"server=" & server & ";uid=" & user & _
";pwd=" & psw & ";database=" & bach & ";"
temp = LinkString
On Error GoTo err
mvarConntion.Open temp
Set ConnServer = mvarConntion
Exit Function
err:
MsgBox "无法连接到服务器!" + vbCrLf + err.Description, vbOKOnly + vbCritical, "系统错误" & err.Number
err.Clear
End Function
Private Function opentable(Conn As ADODB.Connection, ByVal SQLCommand As String) As ADODB.Recordset
Dim cmd As New ADODB.Command
Dim Rs As New ADODB.Recordset
On Error GoTo Endme
Rs.CursorLocation = adUseClient
Rs.CursorType = adOpenKeyset
Rs.LockType = adLockOptimistic '****************************
Rs.Open SQLCommand, Conn, , , adCmdText
Set opentable = Rs
Exit Function
Endme:
MsgBox "无法打开数据库!" & vbCrLf & err.Description, vbOKOnly + vbCritical, "系统错误" & err.Number
err.Clear
End Function