7,763
社区成员
发帖
与我相关
我的任务
分享如何用代码判断一个数据库中字段类型是否为NVARCHAR类型，且其字符宽度为40
如何用代码判断一个数据库中字段类型是否为NVARCHAR类型，且其字符宽度为40
...全文
请发表友善的回复…
发表回复
elvaiiso 2016-05-03
- 打赏
- 举报
非常感谢，但是，好象没有 nvarchar的类型呀。
Tiger_Zhao 2016-05-03
- 打赏
- 举报
更正：可以用 OpenSchema 直接取得定义，适合多种数据库。
Tiger_Zhao 2016-05-03
- 打赏
- 举报
可以用 OpenSchema 自己取得定义，适合多种数据库。
'引用 Microsoft ActiveX Data Objects 2.5 Library'
Option Explicit
Sub Main()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=db1.mdb;Uid=Admin;Pwd=;"
Debug.Print GetColumnType(cn, Empty, "成绩", "学号")
Debug.Print GetColumnType(cn, Empty, "成绩", "姓名")
End Sub
Function GetColumnType(ByVal cn As ADODB.Connection, _
ByVal TableSchema As Variant, _
ByVal TableName As String, _
ByVal ColumnName As String _
) As String
Dim sLength As String
Dim sType As String
Dim lDataType As Long
Dim rs As ADODB.Recordset
Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, TableSchema, TableName, ColumnName))
lDataType = rs!DATA_TYPE
If Not IsNull(rs!CHARACTER_MAXIMUM_LENGTH) Then
sLength = "(" & rs!CHARACTER_MAXIMUM_LENGTH & ")"
End If
Set rs = cn.OpenSchema(adSchemaProviderTypes, Array(Empty, Empty))
rs.Filter = "DATA_TYPE=" & lDataType
sType = rs!TYPE_NAME
GetColumnType = sType & sLength
End FunctionINTEGER
LONGCHAR(10)
of123 2016-05-03
- 打赏
- 举报
Private Sub Command1_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test.mdb"
Set rs = New ADODB.Recordset
rs.Open "select * from 表1", cn
Debug.Print "Type = " & Get_Type(rs.Fields("名称").Type),
Debug.Print "Length = " & rs.Fields("名称").DefinedSize
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Subof123 2016-05-03
- 打赏
- 举报
一个例子
Option Explicit
Private Sub Command1_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test.mdb"
Set rs = New ADODB.Recordset
rs.Open "select * from ±ía", cn
Debug.Print "Type = " & Get_Type(rs.Fields("Ãû³Æ").Type),
Debug.Print "Length = " & rs.Fields("Ãû³Æ").DefinedSize
End Sub
Private Function Get_Type(typeid As Integer) As String
Select Case (typeid)
Case 0
Get_Type = "adEmpty"
Case 2
Get_Type = "adSmallInt"
Case 3
Get_Type = "adInteger"
Case 4
Get_Type = "adSingle"
Case 5
Get_Type = "adDouble"
Case 6
Get_Type = "adCurrency"
Case 7
Get_Type = "adDate"
Case 8
Get_Type = "adBSTR"
Case 9
Get_Type = "adIDispatch"
Case 10
Get_Type = "adError"
Case 11
Get_Type = "adBoolean"
Case 12
Get_Type = "adVariant"
Case 13
Get_Type = "adIUnknown"
Case 14
Get_Type = "adDecimal"
Case 16
Get_Type = "adTinyInt"
Case 17
Get_Type = "adUnsignedTinyInt"
Case 18
Get_Type = "adUnsignedSmallInt"
Case 19
Get_Type = "adUnsignedInt"
Case 20
Get_Type = "adBigInt"
Case 21
Get_Type = "adUnsignedBigInt"
Case 72
Get_Type = "adGUID"
Case 128
Get_Type = "adBinary"
Case 129
Get_Type = "adChar"
Case 130
Get_Type = "adWChar"
Case 131
Get_Type = "adNumeric"
Case 132
Get_Type = "adUserDefined"
Case 133
Get_Type = "adDBDate"
Case 134
Get_Type = "adDBTime"
Case 135
Get_Type = "adDBTimeStamp"
Case 200
Get_Type = "adVarChar"
Case 201
Get_Type = "adLongVarChar"
Case 202
Get_Type = "adVarWChar"
Case 203
Get_Type = "adLongVarWChar"
Case 204
Get_Type = "adVarBinary"
Case 205
Get_Type = "adLongVarBinary"
Case Else
Get_Type = "Unkhown"
End Select
End Function
elvaiiso 2016-05-03
- 打赏
- 举报
ACCESS
of123 2016-05-03
- 打赏
- 举报
是什么数据库？
of123 2016-05-03
- 打赏
- 举报
nvarchar 是 SQL Server 的术语，指可变长度 Unicode 字符数据。相当于 VB 的 adVarWChar。
Type 常量 值 说明
adVarWChar 202 Unicode 字符串值。