结果如题。代码如下
adoPrimaryRS.Open "select id as ID,Name as 姓名,BirthDate as 出生日期,PersonalId AS 身份证号码,Nationality AS 国籍,Birthplace AS 户口所在地,case Gender when 0 then '男' when 1 then '女' end as 性别,CriminalRecord AS 犯罪记录,img AS 照片 from Face Order by Name", db, adOpenStatic, adLockOptimistic请各位兄弟帮忙分析一下
adoPrimaryRS.Open "select id as ID,Name as 姓名,BirthDate as 出生日期,PersonalId AS 身份证号码,Nationality AS 国籍,Birthplace AS 户口所在地,case Gender when 0 then '男' when 1 then '女' end as 性别,CriminalRecord AS 犯罪记录,img AS 照片 from Face Order by Name", db, adOpenStatic, adLockOptimistic请各位兄弟帮忙分析一下
1 张三 1973/02/03 …… 0 电梯里放屁
2 李丽 1967/08/23 …… -1 抢小孩糖吃在打开数据表一看,性别那一栏里的值除了0就是1,根本没有-1
DataGrid把数据库的值(Bit型)取出来显示0或-1就正常了
解决方法:case cast(Gender as char(1)) when 0 then '男' when 1 then '女' end as 性别
解决方法:
1.不用绑定,而用代码处理,速度稍慢;
2.改变数据库中表的格式。
Option ExplicitDim WithEvents adoPrimaryRS As Recordset
Private DoingRequery As Boolean
Public Event MoveComplete()Private Sub Class_Initialize()
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
' db.Open "PROVIDER=MSDASQL;dsn=hxxdusa;uid=;pwd=;"
db.Open ConfigSettings.ConnectionString
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select id as ID,Name as 姓名,BirthDate as 出生日期,PersonalId AS 身份证号码,Nationality AS 国籍,Birthplace AS 户口所在地,case cast(Gender as char(1)) when 0 then '男' when 1 then '女' end as 性别,CriminalRecord AS 犯罪记录,img AS 照片 from Face Order by Name", db, adOpenStatic, adLockOptimistic
'adoPrimaryRS.Open "select id as ID,Name as 姓名,BirthDate as 出生日期,PersonalId AS 身份证号码,Nationality AS 国籍,Birthplace AS 户口所在地,Gender as 性别,CriminalRecord AS 犯罪记录,img AS 照片 from Face Order by Name", db, adOpenStatic, adLockOptimistic DataMembers.Add "Primary"
End SubPrivate Sub Class_GetDataMember(DataMember As String, Data As Object)
Select Case DataMember
Case "Primary"
Set Data = adoPrimaryRS
End Select
End SubPrivate Sub adoPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
RaiseEvent MoveComplete
End SubPrivate Sub adoPrimaryRS_WillChangeRecord(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
'This is where you put validation code
'This event gets called when the following actions occur
Dim bCancel As Boolean
Select Case adReason
Case adRsnAddNew
Case adRsnClose
Case adRsnDelete
Case adRsnFirstChange
Case adRsnMove
Case adRsnRequery
Case adRsnResynch
Case adRsnUndoAddNew
Case adRsnUndoDelete
Case adRsnUndoUpdate
Case adRsnUpdate
End Select If bCancel Then adStatus = adStatusCancel
End SubPublic Property Get EditingRecord() As Boolean
EditingRecord = (adoPrimaryRS.EditMode <> adEditNone)
End PropertyPublic Property Get AbsolutePosition() As Long
AbsolutePosition = adoPrimaryRS.AbsolutePosition
End PropertyPublic Sub MoveFirst()
adoPrimaryRS.MoveFirst
End SubPublic Sub MoveLast()
adoPrimaryRS.MoveLast
End SubPublic Sub MoveNext()
If Not adoPrimaryRS.EOF Then adoPrimaryRS.MoveNext
If adoPrimaryRS.EOF And adoPrimaryRS.RecordCount > 0 Then
Beep
'moved off the end so go back
adoPrimaryRS.MoveLast
End If
End SubPublic Sub MovePrevious()
If Not adoPrimaryRS.BOF Then adoPrimaryRS.MovePrevious
If adoPrimaryRS.BOF And adoPrimaryRS.RecordCount > 0 Then
Beep
'moved off the end so go back
adoPrimaryRS.MoveFirst
End If
End SubPublic Function GetImage() As String
If Not (adoPrimaryRS.EOF Or adoPrimaryRS.BOF) Then
GetImage = adoPrimaryRS.Fields(8)
If GetImage = "" Then GetImage = "noimage.jpg"
Else
GetImage = "noimage.jpg"
' MsgBox "Record Image does not exist."
End IfEnd FunctionPublic Sub FindRecord(RecordName As String)
' Search by Name
Dim str As String
adoPrimaryRS.MoveFirst
str = "姓名 like "
str = str + "'" + CStr(RecordName) + "%'"
adoPrimaryRS.Find str
End Sub在窗体中关于DATAGRID的代码如下
Private WithEvents PrimaryCLS As clsFace1 ‘就是上边的那个类
Private Sub Form_Load()
Dim i As Integer
Dim res As New ResourceHelper
res.LoadFormStrings Me, ConfigSettings.Language
Set PrimaryCLS = New clsFace1
grdDataGrid.DataMember = "Primary"
Set grdDataGrid.DataSource = PrimaryCLS
picImage.Clear
picImage.Read ConfigSettings.PicturePath + PrimaryCLS.GetImage
End Sub