我用ado通过odbc连接sqlserver时碰到一个很奇怪的问题,如果我的查询sql语句不带任何条件判断如:select 项目名称 from 勘查项目受理则可以正常找到记录,但是如果把查询语句加上条件判断如:select 项目名称 from 勘查项目受理 where 申请序号=04530032,则怎么样也找不到记录,我到sqlserver数据库里面查是可以查到这条记录的,这是为什么呢?以下是我的程序:Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
cn.Open "FILE NAME=C:\teckhit\tksp.dsn", "guest", "guest"
strsql = "select 项目名称 from 勘查项目受理 where 申请序号=04530032"
Set rs = cn.Execute(strsql)
If Not rs.EOF Then
MsgBox rs.Fields("项目名称").Value
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
Dim rs As New ADODB.Recordset
Dim strsql As String
cn.Open "FILE NAME=C:\teckhit\tksp.dsn", "guest", "guest"
strsql = "select 项目名称 from 勘查项目受理 where 申请序号=04530032"
Set rs = cn.Execute(strsql)
If Not rs.EOF Then
MsgBox rs.Fields("项目名称").Value
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
这样看看
Dim rs As New ADODB.Recordset
Dim strsql As String
'这里改成连接字符串
cn.connectionstring="........."
'下边这句话去掉,改成cn.open
'cn.Open "FILE NAME=C:\teckhit\tksp.dsn", "guest", "guest"
strsql = "select 项目名称 from 勘查项目受理 where 申请序号=04530032"
'下边这句rs改成rs.Source
Set rs = cn.Execute(strsql)
If Not rs.EOF Then
MsgBox rs.Fields("项目名称").Value
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
改成
rs.open strsql,cn,adOpenDynamic,adLockOptimistic
试试
Dim rs As New ADODB.Recordset
Dim strsql As String
cn.Open "FILE NAME=C:\teckhit\tksp.dsn", "guest", "guest"
strsql = "select 项目名称 from 勘查项目受理" **************
Set rs = cn.Execute(strsql)
If Not rs.EOF Then
MsgBox rs.Fields("项目名称").Value
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
cn.Open "FILE NAME=C:\teckhit\tksp.dsn", "guest", "guest"
strsql = "select 项目名称 from 勘查项目受理 where 申请序号='04530032'"
rs.Open strsql, cn, adOpenDynamic, adLockPessimistic
If Not rs.EOF Then
MsgBox rs.Fields("项目名称").Value
End If
Set rs = Nothing
cn.Close
Set cn = Nothing
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then
MsgBox rs.Fields("项目名称")
End If
游标改为下面的试试。另外,字段名称建议改用英文(字母)。strsql = "select 项目名称 from 勘查项目受理 where 申请序号='04530032'"
rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
MsgBox rs.Fields("项目名称")
End If
strsql = "select 项目名称 from 勘查项目受理 where 申请序号= '" & "04530032'"
Set rs = cn.Execute(strsql)
If Not rs.EOF Then
MsgBox rs.Fields("项目名称").Value
End If
strsql = "select 项目名称 from 勘查项目受理 where 申请序号= '" & "04530032'"
rs.Open strsql, cn, adOpenStatic, adLockOptimistic, adCmdText
If Not rs.EOF Then
MsgBox rs.Fields("项目名称").Value
End If