Question

vb中如何sql语句判断数据库是否存在列。。

c:\aaa.mdb 表abc 如果存在 kk 列，不添加，否则添加kk列。。。。
怎么判断是否存在列呢？

Answer 1

select * from syscolumns where name='kk' and id=object_id('abc')，返回记录大于零说明存在，否则不存在。

追问

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\1.mdb;Persist Security Info=False"
sql = "select * from syscolumns where name='kkk' and id=object_id('abc')"
rs.Open sql, cn, 0, 1
If rs.EOF Then
MsgBox "不存在"
Else
MsgBox "存在"
End If

出错啊？？？？

追答

没看仔细，呵呵，之前说的是mssql的，access的要这么用：
Dim tmpCon As New ADODB.Connection
tmpCon.CursorLocation = adUseClient
tmpCon.Open GConStr
Dim tmpAdo As New ADODB.Recordset
tmpAdo.CursorLocation = adUseClient
tmpAdo.CursorType = adOpenKeyset
tmpAdo.LockType = adLockOptimistic
Dim isExists As Boolean
'开户行
Set tmpAdo = tmpCon.OpenSchema(adSchemaColumns)
If tmpAdo.RecordCount > 0 Then
tmpAdo.MoveFirst
Do Until tmpAdo.EOF
If LCase(tmpAdo!TABLE_NAME) = LCase("abc") And LCase(tmpAdo!Column_NAME) = LCase("kk") Then
isExists = True
Exit Do
End If
tmpAdo.MoveNext
Loop
End If

Answer 2

和3楼的差不多,再加个判断,我用的表是users, 列名是user_no.
if exists (select 1 from syscolumns where 1=1 and name='user_no' and id=object_id('users'))
print 'The colum ''user_no'' has exists!!'
else
alter table users add user_no varchar(30)

Answer 3

同意以上回答

Answer 4

只要你连接数据库的语句只要是正确的,当你连不上SQL数据库时,就可能说明是服务器没有运行啦!