问题是这样的:我在做一个三层结构的C/S,数据库中的表用类封装后在Client端用其中的AddNew添加新记录却出现错误。具体情况如下:
Option Explicit
Private mAccID As String
Private mAccSubID As Integer
Private mUserID As String
Private mIntime As Date
Private mOuttime As Date
Private mAccStation As String
Private mYear As String
Private mRunOption As String
Private mDisp As IntegerPrivate bInit As BooleanPublic Property Get cAcc_ID() As String
cAcc_ID = mAccID
End PropertyPublic Property Let cAcc_ID(ByVal NewVal As String)
NewVal = Trim(NewVal)
If NewVal = "0000" Then
mAccID = NewVal
Exit Property
Else
If Len(Trim(NewVal)) >= 4 Then
NewVal = Left$(NewVal, 4)
End If
If IsNumeric(NewVal) = False Then
Exit Property
End If
Dim lCol As Long
If Not oColAcc Is Nothing Then
For lCol = 1 To oColAcc.Count
If NewVal = oColAcc(lCol) Then
mAccID = NewVal
Exit For
End If
Next
End If
End If
End PropertyPublic Property Get cAcc_Sub_ID() As Integer
cAcc_Sub_ID = mAccSubID
End PropertyPublic Property Let cAcc_Sub_ID(ByVal NewVal As Integer)
mAccSubID = NewVal
End PropertyPublic Property Get cUser_ID() As String
cUser_ID = mUserID
End PropertyPublic Property Let cUser_ID(ByVal NewVal As String)
NewVal = Trim(NewVal)
If Ucase(NewVal) = "SA" Then
mUserID = NewVal
Exit Property
Else
If Len(NewVal) >= 12 Then
NewVal = Left$(NewVal, 12)
End If
Dim lCol As Long
If Not oColMas Is Nothing Then
For lCol = 1 To oColMas.Count
If NewVal = oColMas(lCol) Then
mUserID = NewVal
End If
Next
End If
End If
End PropertyPublic Property Get dIntime() As Date
dIntime = mIntime
End PropertyPublic Property Let dIntime(ByVal NewVal As Date)
mIntime = NewVal
End PropertyPublic Property Get dOuttime() As Date
dOuttime = mOuttime
End PropertyPublic Property Let dOuttime(ByVal NewVal As Date)
mOuttime = NewVal
End PropertyPublic Property Get cAcc_Station() As String
cAcc_Station = mAccStation
End PropertyPublic Property Let cAcc_Station(ByVal NewVal As String)
NewVal = Trim(NewVal)
If Len(NewVal) >= 255 Then
NewVal = Left$(NewVal, 255)
End If
mAccStation = NewVal
End PropertyPublic Property Get iYear() As String
iYear = mYear
End PropertyPublic Property Let iYear(ByVal NewVal As String)
NewVal = Trim(NewVal)
If Len(NewVal) >= 4 Then
NewVal = Left$(NewVal, 4)
End If
If IsNumeric(NewVal) = False Then
Exit Property
End If
If Val(NewVal) < 1900 Or Val(NewVal) > 2100 Then
Exit Property
End If
mYear = NewVal
End PropertyPublic Property Get cRunOption() As String
cRunOption = mRunOption
End PropertyPublic Property Let cRunOption(ByVal NewVal As String)
NewVal = Trim(NewVal)
If Len(NewVal) >= 1000 Then
NewVal = Left$(NewVal, 1000)
End If
mRunOption = NewVal
End PropertyPublic Property Get bDisp() As Integer
bDisp = mDisp
End PropertyPublic Property Let bDisp(ByVal NewVal As Integer)
If NewVal >= 1 Then
mDisp = 1
Else
mDisp = 0
End If
End PropertyPublic Function AddNew() As Boolean
Dim strSQL As String
If bInit Then
Select Case True
Case Trim(mAccID) = ""
GoTo errAdd
Case Trim(mAccStation) = ""
GoTo errAdd
Case Trim(mYear) = ""
GoTo errAdd
Case Trim(mRunOption) = ""
GoTo errAdd
Case Else
oADOConnect.BeginTrans
strSQL = "Insert Into AccountLog (cAcc_ID,cAcc_Sub_ID,cUser_ID,dIntime,dOuttime,cAcc_Station,iYear,cRunOption,bDisp) Values ("
strSQL = strSQL & Me.cAcc_ID & "," & Me.cAcc_Sub_ID & "," & Me.cUser_ID & ","
strSQL = strSQL & Me.dIntime & "," & Me.dOuttime & "," & Me.cAcc_Station & ","
strSQL = strSQL & Me.iYear & "," & Me.cRunOption & "," & Me.bDisp & ")"
oADOConnect.Execute strSQL
oADOConnect.CommitTrans
AddNew = (Err.Number = 0)
InitClass
End Select
Else
GoTo errAdd
End IfExit FunctionerrAdd:
AddNew = False
Exit Function
End FunctionPrivate Sub Class_Initialize()
If oADOConnect Is Nothing Then
bInit = False
Else
bInit = True
InitClass
End If
End SubPrivate Sub InitClass()
mAccID = ""
mAccSubID = 0
mUserID = ""
mIntime = Now()
mOuttime = Now()
mAccStation = ""
mYear = CStr(Year(Now()))
mRunOption = ""
mDisp = 1
End Sub
Option Explicit
Private mAccID As String
Private mAccSubID As Integer
Private mUserID As String
Private mIntime As Date
Private mOuttime As Date
Private mAccStation As String
Private mYear As String
Private mRunOption As String
Private mDisp As IntegerPrivate bInit As BooleanPublic Property Get cAcc_ID() As String
cAcc_ID = mAccID
End PropertyPublic Property Let cAcc_ID(ByVal NewVal As String)
NewVal = Trim(NewVal)
If NewVal = "0000" Then
mAccID = NewVal
Exit Property
Else
If Len(Trim(NewVal)) >= 4 Then
NewVal = Left$(NewVal, 4)
End If
If IsNumeric(NewVal) = False Then
Exit Property
End If
Dim lCol As Long
If Not oColAcc Is Nothing Then
For lCol = 1 To oColAcc.Count
If NewVal = oColAcc(lCol) Then
mAccID = NewVal
Exit For
End If
Next
End If
End If
End PropertyPublic Property Get cAcc_Sub_ID() As Integer
cAcc_Sub_ID = mAccSubID
End PropertyPublic Property Let cAcc_Sub_ID(ByVal NewVal As Integer)
mAccSubID = NewVal
End PropertyPublic Property Get cUser_ID() As String
cUser_ID = mUserID
End PropertyPublic Property Let cUser_ID(ByVal NewVal As String)
NewVal = Trim(NewVal)
If Ucase(NewVal) = "SA" Then
mUserID = NewVal
Exit Property
Else
If Len(NewVal) >= 12 Then
NewVal = Left$(NewVal, 12)
End If
Dim lCol As Long
If Not oColMas Is Nothing Then
For lCol = 1 To oColMas.Count
If NewVal = oColMas(lCol) Then
mUserID = NewVal
End If
Next
End If
End If
End PropertyPublic Property Get dIntime() As Date
dIntime = mIntime
End PropertyPublic Property Let dIntime(ByVal NewVal As Date)
mIntime = NewVal
End PropertyPublic Property Get dOuttime() As Date
dOuttime = mOuttime
End PropertyPublic Property Let dOuttime(ByVal NewVal As Date)
mOuttime = NewVal
End PropertyPublic Property Get cAcc_Station() As String
cAcc_Station = mAccStation
End PropertyPublic Property Let cAcc_Station(ByVal NewVal As String)
NewVal = Trim(NewVal)
If Len(NewVal) >= 255 Then
NewVal = Left$(NewVal, 255)
End If
mAccStation = NewVal
End PropertyPublic Property Get iYear() As String
iYear = mYear
End PropertyPublic Property Let iYear(ByVal NewVal As String)
NewVal = Trim(NewVal)
If Len(NewVal) >= 4 Then
NewVal = Left$(NewVal, 4)
End If
If IsNumeric(NewVal) = False Then
Exit Property
End If
If Val(NewVal) < 1900 Or Val(NewVal) > 2100 Then
Exit Property
End If
mYear = NewVal
End PropertyPublic Property Get cRunOption() As String
cRunOption = mRunOption
End PropertyPublic Property Let cRunOption(ByVal NewVal As String)
NewVal = Trim(NewVal)
If Len(NewVal) >= 1000 Then
NewVal = Left$(NewVal, 1000)
End If
mRunOption = NewVal
End PropertyPublic Property Get bDisp() As Integer
bDisp = mDisp
End PropertyPublic Property Let bDisp(ByVal NewVal As Integer)
If NewVal >= 1 Then
mDisp = 1
Else
mDisp = 0
End If
End PropertyPublic Function AddNew() As Boolean
Dim strSQL As String
If bInit Then
Select Case True
Case Trim(mAccID) = ""
GoTo errAdd
Case Trim(mAccStation) = ""
GoTo errAdd
Case Trim(mYear) = ""
GoTo errAdd
Case Trim(mRunOption) = ""
GoTo errAdd
Case Else
oADOConnect.BeginTrans
strSQL = "Insert Into AccountLog (cAcc_ID,cAcc_Sub_ID,cUser_ID,dIntime,dOuttime,cAcc_Station,iYear,cRunOption,bDisp) Values ("
strSQL = strSQL & Me.cAcc_ID & "," & Me.cAcc_Sub_ID & "," & Me.cUser_ID & ","
strSQL = strSQL & Me.dIntime & "," & Me.dOuttime & "," & Me.cAcc_Station & ","
strSQL = strSQL & Me.iYear & "," & Me.cRunOption & "," & Me.bDisp & ")"
oADOConnect.Execute strSQL
oADOConnect.CommitTrans
AddNew = (Err.Number = 0)
InitClass
End Select
Else
GoTo errAdd
End IfExit FunctionerrAdd:
AddNew = False
Exit Function
End FunctionPrivate Sub Class_Initialize()
If oADOConnect Is Nothing Then
bInit = False
Else
bInit = True
InitClass
End If
End SubPrivate Sub InitClass()
mAccID = ""
mAccSubID = 0
mUserID = ""
mIntime = Now()
mOuttime = Now()
mAccStation = ""
mYear = CStr(Year(Now()))
mRunOption = ""
mDisp = 1
End Sub
'数值型变量用:"& 变量名 & "
strSQL = "Insert Into AccountLog (cAcc_ID,cAcc_Sub_ID,cUser_ID,dIntime,dOuttime,cAcc_Station,iYear,cRunOption,bDisp) Values ("
strSQL = strSQL & "'" & Me.cAcc_ID & "'," & Me.cAcc_Sub_ID & ",'" & Me.cUser_ID & "',#"
strSQL = strSQL & Format(Me.dIntime, "yyyy-mm-dd") & "#,#" & Format(Me.dOuttime, "yyyy-mm-dd") & "#,'" & Me.cAcc_Station & "','"
strSQL = strSQL & Me.iYear & "','" & Me.cRunOption & "'," & Me.bDisp & ")"
数据库acess用
字符型: '"& 变量名 &"'
日期型变量用 #"& 变量名 &"#'数值型变量用:"& 变量名 & "sql_server
全是'"& 变量名 &"'
不过都要注意,日期类型在插入前一定要判断是日期的数据