vb连接数据库问题,求帮忙查找错误原因!
模块代码:
Public dbConn As ADODB.Connection '定义一个ADODB连接
Public stuRs As ADODB.Recordset '定义Recordset对象
Public SqlFindStr As String
Public Function Conn2DB() As Boolean
'公共Access数据库连函数
Dim connStr As String
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\DB.mdb;"
Set dbConn = New ADODB.Connection '定义一个ADODB连接对象
If dbConn.State <> adStateOpen Then '判断ADODB连接是否打开
dbConn.Open connStr '如果不是打开状态 就打开连接
End If
Conn2DB = True
End Function
Public Function ExcSQL(ByVal sql As String) As ADODB.Recordset
Dim rst As ADODB.Recordset
Call Conn2DB
'执行一条SQL查询,得到一个记录集
Set rst = New ADODB.Recordset
rst.Open Trim$(sql), dbConn adOpenKeyset, adLockOptimistic '得到记录
Set ExcSQL = rst
End Function
Public Function CloseDb() As Boolean
If dbConn.State = adStateOpen Then
dbConn.Close: Set dbConn = Nothing
End If
End Function
Function max(a()) As String
Dim p, i
Dim a() As Integer
p = a(1)
For i = 2 To UBound(a)
If p <= a(i) Then p = a(i)
Next i
max = p
End Function
窗体代码:
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim s As String
Dim boxt
boxt = InputBox("请输入数据", , 0)
s = "select max(height) as maxV from b where exists (select * from pd where pd.fsc =b.fsc and no = " + boxt + ")"
Set rs = ExcSQL(s)
Text1.Text = rs!maxV & ""
Call CloseDb
End Sub
窗体控件为一个text控件和一个commandbutton控件
数据库中有两个表如下:
我输入数据查询时,只有输入0查询才有结果,但是数据库没有满足条件为0的数据,很头疼,求各位大大帮帮忙啊!
数据库 vb
[解决办法]
s = "select max(height) as maxV from b where exists (select * from pd where pd.fsc =b.fsc and no = " + boxt + ")"
如果确认是字符串,要用 ''<二个单引号>
s = "select max(height) as maxV from b where exists (select * from pd where pd.fsc =b.fsc and no = '" & boxt & "')"
[解决办法]
try:
s = "select max(height) as maxV from b where exists (select 1 from pd where pd.fsc =b.fsc and no = '" & boxt & "')"
或:
s = "select max(height) as maxV from b inner join pd on b.fsc=pd.fsc where pd.no='" & boxt & "'"
[解决办法]
http://download.csdn.net/detail/veron_04/3827200
http://download.csdn.net/detail/veron_04/1644211
[解决办法]
Access 的 SQL 解析有点怪,多表 SQL 最好给所有字段加上表名前缀
s = "select max(b.height) as maxV from b where exists (select * from pd where pd.fsc =b.fsc and pd.no=" & boxt & ")"