RecordSet方法产生转换错误并且SQL结果没有BOF和EOF
我有一个 SQL 查询，已通过 SQL 程序成功执行。我最终将数据合并到一列中，该列具有标题（无列名称）。
我尝试通过 Visual Basic 程序执行相同的语句，并返回
MsgBox recordstObject.Fields("")
所需的结果。
但是，执行
MsgBox recordstObject.RecordCount
返回
[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting
the varchar value 'A01' to data type int.
当我尝试结合使用 while 循环和recordstObject.MoveNext 时，也会出现此错误。我尝试通过检查是否有 BOF 或 EOF 结果
If Not (recordstObject.BOF And recordstObject.EOF) Then
MsgBox "No Begin or End"
Else
MsgBox "There are records"
End If
recordstObject.MoveNext
显示“没有开始或结束”MsgBox 出现。
应该指出的是，我目前正在处理一个结果，但最终需要补偿多个结果。
我的 sql 语句是
SELECT id + '|' + name + '|DogBreed' FROM breeds WHERE c_id = 9
Which MsgBox recordstObject.Fields("") 说 1|Scottish Terr|DogBreed
有关如何解决此问题的建议？
谢谢。
I have a SQL query that I have executed through the SQL program successfully. I ended up with the data that I combined into one column which has the header (No Column Name).
I attempted the execute this same statement through a Visual Basic program and executing
MsgBox recordstObject.Fields("")
returns the desired results.
However, executing
MsgBox recordstObject.RecordCount
returns
[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting
the varchar value 'A01' to data type int.
This error also appears when I tried a work around with a combination of a while loop and recordstObject.MoveNext. I've tried checking if there's a BOF or an EOF via
If Not (recordstObject.BOF And recordstObject.EOF) Then
MsgBox "No Begin or End"
Else
MsgBox "There are records"
End If
recordstObject.MoveNext
And it turns out that the "No Begin or End" MsgBox showed up.
It should be noted that I'm currently working with a result of one but will eventually need to compensate for multiple results.
My sql Statement is
SELECT id + '|' + name + '|DogBreed' FROM breeds WHERE c_id = 9
Which MsgBox recordstObject.Fields("") says 1|Scottish Terr|DogBreed
Suggestions on how to fix this?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为一个空记录集与 BOF 和 EOF 一起出现 - 这意味着你的测试是错误的（即你说 If Not Empty then Msgbox "Empty")
某些连接/锁定/编组方法不支持 RecordCount 属性，因为记录不会一次性全部传输到客户端。然而，迭代记录集应该可以解决问题。
不知道为什么会出现类型转换错误，除非驱动程序根据它看到的第一行猜测整数数据类型，然后在后续行中出错。但这对于 SQL Server 来说相当奇怪 - 它在 Excel 文本 ODBC 驱动程序上更为典型。
可以贴一下你的SQL吗？
I thought an empty recordset shows up with BOF and EOF together - which would mean your test is the wrong way round (ie you're saying If Not Empty Then Msgbox "Empty")
Some connection/locking/marshalling methods do not support the RecordCount property because the records aren't all transferred to the client in one go. However iterating through the recordset should have done the trick.
Not sure why you're getting the type conversion error, unless the driver is guessing an Integer datatype based on the first row it sees and then getting tripped up on subsequent rows. That would be quite weird for SQL Server though - it's more typical on Excel text ODBC drivers.
Can you post your SQL ?