Public Function ConnectString() _
As String
'returns a DB ConnectString
ConnectString = "FileDSN=hotel.dsn;UID=sa;PWD="
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
Dim i As Integer
Dim j As Integer
Dim sSql As String
Dim txtSQL As String
Dim MsgText As String
Dim mrc As ADODB.Recordset
txtSQL = "select distinct datepart(yy,indate) from bookin where ammount = '0' "
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF = False Then
With mrc
Do While Not .EOF
cboYear(0).AddItem .Fields(0)
.MoveNext
Loop
End With
cboYear(0).ListIndex = 0
For j = 1 To 12
cboMonth(0).AddItem j
Next j
cboMonth(0).Text = Month(Now())
For j = 1 To 31
cboDay.AddItem j
Next j
cboDay.Text = Day(Now())
Else
cmdOk.Enabled = False
End If
mrc.Close
在mrc.EOF = False 处出错,说什么对象未定义。可是我明明定义了。
在前面加上dim mrc as adodb.recordset
As String
'returns a DB ConnectString
ConnectString = "FileDSN=hotel.dsn;UID=sa;PWD="
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
Dim i As Integer
Dim j As Integer
Dim sSql As String
Dim txtSQL As String
Dim MsgText As String
Dim mrc As ADODB.Recordset
txtSQL = "select distinct datepart(yy,indate) from bookin where ammount = '0' "
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF = False Then
With mrc
Do While Not .EOF
cboYear(0).AddItem .Fields(0)
.MoveNext
Loop
End With
cboYear(0).ListIndex = 0
For j = 1 To 12
cboMonth(0).AddItem j
Next j
cboMonth(0).Text = Month(Now())
For j = 1 To 31
cboDay.AddItem j
Next j
cboDay.Text = Day(Now())
Else
cmdOk.Enabled = False
End If
mrc.Close
在mrc.EOF = False 处出错,说什么对象未定义。可是我明明定义了。
在前面加上dim mrc as adodb.recordset
解决方案 »
- 如何改变这种阵列的大小
- 关于InternetGetConnectedState
- 如何解决使用doevents导致计算机堆栈空间溢出问题?
- 如何将多次输入的数字,在每次之间抽出相同数字?谢高手指点!
- 如何使ComboBox控件内的列表值只读?
- TextBox 变灰怎么实现?
- 利用上班时间做了一个本论坛题目:超大数字的阶乘问题,有成就感,散分100!
- 一个弱智才会有的问题啊,送分了!!!
- 如何用textbox绑定到MSHFlexGrid中的单元格
- vb安装盘问题,急
- 200分求教!!! 如何实现VB ActiveX DLL的Singleton Patten?
- 怎样才可以似的MSFORM中的窗体开时只能打开一个?
Set mrc = ExecuteSQL(txtSQL, MsgText)
改成
Set mrc=new adodb.recordset
Set mrc=executesql(txtsql,msgtext)
"select distinct datepart(yy,indate) from bookin where ammount = '0' "
是否正确,在某些条件下distinct需要加上group by
set mrc = new adodb.recordset
加了这个也没有用,
照样出,变量未定义,
真的很怪啊。
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connStr As String
connStr = "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=g:\vbr\vbr.mdb"
conn.Open connStrrs.CursorLocation = adUseClient
rs.Open "SELECT * FROM [参数表]", conn, adOpenKeyset, adLockPessimistic
这样,rs 就成为了一个数据集,可以象正常的 DAO 一样操作了。至于连接和数据集对象前面的 ADODB 是为了和 DAO 进行区分,如果程序中没有使用 DAO 的话,可以省略。
如果要使得数据集和控件绑定的话,VB 会在工程中自动引用 Microsoft Data Binding Collection VB 6.0(MSBIND.DLL),这里以文本框(TextBox)为例说明绑定的方法。假定有 Text1 和 Text2 两个控件,分别绑定字段“参数名”和“参数值”,则绑定的方法是:代码:
Set Text1.DataSource = rs.DataSource
Text1.DataField = "参数名"
Set Text2.DataSource = rs.DataSource
Text2.DataField = "参数值" 这样,当你控制记录位置(MoveFirst, MoveLast, MovePrevious, MoveNext 等等)时,控件中的数据会随记录位置自动变化。
但是更多的情况是:你需要程序生成数据库和表,这样你就需要引用 Microsoft ADO Ext. 2.5 for DDL and Security(MSADOX.DLL)。下面是两个示例:创建数据库示例:代码:
Dim cat As New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.3.51; Data Source=g:\vbr\vbr.mdb"数据库创建时就占用了这个文件,如果要关闭,可以用 Set cat = Nothing 来完成,这样就可以使用其它的功能,例如打开等等。创建表示例:代码:
Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog ' 打开目录。
cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=g:\vbr\vbr.mdb;"
tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tbl还有关键字、过程、视图、索引等创建方法,一般情况下上面提到的两个引用都必须同时使用,有关详细的资料可以在 MSDN 中找到。
总的来说,似乎 ADO 是对已有数据库表的使用,ADOX 则似乎是对 ADO 创建方面的扩充。
可是不行,MyLf(風之子) 你说的,有道理,
可是怎么判断mrc不为空呢??
具体怎么做?
msgbox "记录集为空!!!"
end if如上面的不行,你可将执行SQL语句的函数的结束部分的:
Set rst = Nothing 改为:rst.close能后你在主程序中用:
if mrc.State =adStateClosed then
exit sub
else
你自己写的代码
end if
我的答案是,你的sql查询是错的(不行可以放到查询分析器里面去看,得到的将会是null)。
一个recordcount里面没有记录的话,不就等于
是recordcount的objiect=nothing,对象为nothing不就等于没有构造对象,不就等于没有定义。
别老是相信实例上的东西,要抱着研究的态度看实例上的东西
txtSQL = "select distinct datepart(yy,indate) from bookin where ammount = '0' "
Set mrc = ExecuteSQL(txtSQL, MsgText)
If mrc.EOF = False Then
With mrc
Do While Not .EOF
cboYear(0).AddItem .Fields(0)
.MoveNext
Loop
End With
中加上下面做好做成异常的处理
If (mrc Is Nothing)
Debug.Print Err.Number & vbCrLf & Err.Description
Exit Sub
Else
......
End If