检索“数字"来自 Sql VB.NET System.Data.OleDb.OleDbException: '条件表达式中的数据类型不匹配.' [英] Retrieving "Number" From Sql VB.NET System.Data.OleDb.OleDbException: 'Data type mismatch in criteria expression.'

问题描述

如果我想检索在访问数据库中保存为数字的值.

If I want to retrieve a value that is saved as a number in an access database.

我正在使用以下内容:

            Dim sql As String = "SELECT ArithmeticScore FROM " & tablename & " WHERE DateAscending = '" & todaysdate & "'"
            Using connection As New OleDbConnection(getconn)
                Using command As New OleDbCommand(sql, connection)
                    connection.Open()
                    scorevalue = CDec(command.ExecuteScalar()) 'Data type mismatch in criteria expression.
                    connection.Close()
                End Using
            End Using
            MsgBox(scorevalue)

getconn = 作为字符串的连接字符串

getconn = connection string as a string

scorevalue = 没有十进制

字段ArithmeticScore设置为表中的Number.

现在单元格中的确切值是 50，但程序应该允许任何十进制值.

The exact value in the cell right now is 50, but the program should allow for any decimal value.

我得到的错误是条件表达式中的数据类型不匹配".

The error im getting is "Data type mismatch in criteria expression".

推荐答案

错误消息中提到的标准表达式 未引用ArithmeticScore 输出.它在谈论 WHERE 子句.todaysdate 的任何内容与数据库对 DateAscending 列的期望不匹配.

The criteria expression mentioned in the error message does not refer to the ArithmeticScore output. It's talking about the WHERE clause. Whatever you have for todaysdate does not match what the database is expecting for the DateAscending column.

由于 OleDb 是一个通用的提供者，我们不知道你在谈论什么类型的数据库，但大多数数据库都有一种方法可以在 SQL 中获取当前日期值:getdate()、current_timestamp 等.使用该机制可能会解决冲突，并且首先不需要为此使用字符串连接.

Since OleDb is a generic provider, we don't know exactly what kind of database you're talking to, but most databases have a way to get the current date value in SQL: getdate(), current_timestamp, etc. Using that mechanism will likely solve the conflict, and there's no need to use string concatenation for this in the first place.

Dim sql As String = "SELECT ArithmeticScore FROM " & tablename & " WHERE DateAscending = Date()"

解决此问题的另一种方法是使用适当的参数化查询，无论如何您都应该这样做.永远可以使用字符串连接将数据替换为 SQL 查询，如果您发现自己需要考虑如何格式化日期或数字字符串以在 SQL 命令中使用，那么您几乎总是做一些非常错误的事情.

The other way you can fix this is with proper parameterized queries, which you should doing anyway. It's NEVER okay to use string concatenation to substitute data into an SQL query, and if you find yourself needing to think about how to format a date or number string for use in an SQL command, you're almost always doing something very wrong.

这篇关于检索“数字"来自 Sql VB.NET System.Data.OleDb.OleDbException: '条件表达式中的数据类型不匹配.'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！