检查数据库文件中是否存在ID时出错

本文关键字：ID 出错 存在 是否 数据库 文件 检查 | 更新日期: 2023-09-27 18:16:51

我试图检查是否某些ID已经存在于数据库中。如果没有，我希望用户将id更改为其他内容。

所有这些都是在textx的TextChanged函数中完成的。

问题是，我得到一个错误，因为查询看起来很好，我不知道为什么我看到这个:The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

执行检查的

方法:

private bool DoesIDExist(int dataID, string filePath)
{
    HashPhrase hash = new HashPhrase();
    DataTable temp = new DataTable();
    string hashShortPass = hash.ShortHash(pass);
    bool result = false;
    // Creating a connection string. Using placeholders make code
    // easier to understand.
    string connectionString =
        @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};
          Persist Security Info=False; Jet OLEDB:Database Password={1};";
    string sql = string.Format
        ("SELECT FROM PersonalData WHERE [DataID] = {0}", dataID);
    using (OleDbConnection connection = new OleDbConnection())
    {
        // Creating command object.
        // Using a string formatting let me to insert data into
        // place holders I have used earlier.
        connection.ConnectionString =
            string.Format(connectionString, filePath, hashShortPass);
        using (OleDbCommand command = new OleDbCommand(sql, connection))
        {
            // Creating command object.
            // Using a string formatting let me to insert data into
            // place holders I have used earlier.
            connection.ConnectionString =
                string.Format(connectionString, filePath, hashShortPass);
            try
            {
                // Open database connection.
                connection.Open();
                using (OleDbDataReader read = command.ExecuteReader())
                {
                    // Checking if there is any data in the file.
                    if (read.HasRows)
                    {
                        // Reading information from the file.
                        while (read.Read())
                        {
                            if (read.GetInt32(0) == dataID)
                                return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.Message);
            }
        }
    }
    return result;
}

检查数据库文件中是否存在ID时出错

我认为你的选择是缺少一些列你想提取出来吗?

string sql = string.Format
    ("SELECT FROM PersonalData WHERE [DataID] = {0}", dataID);

应该是这样的:

string sql = string.Format
    ("SELECT * FROM PersonalData WHERE [DataID] = {0}", dataID);

您错过了要选择的内容

string sql = string.Format
    ("SELECT FROM PersonalData WHERE [DataID] = {0}", dataID);

改成

string sql = string.Format
    ("SELECT * FROM PersonalData WHERE [DataID] = {0}", dataID);

您需要在SELECT子句中指定一些东西。我猜:

SELECT DataID FROM PersonalData WHERE ...

问题在于这行代码:

string sql = string.Format
        ("SELECT FROM PersonalData WHERE [DataID] = {0}", dataID);

您需要指定要选择的内容。例如:SELECT *、SELECT [MyColumn]、SELECT TOP 1 *等。根据您的要求，您想要的似乎是:

string sql = string.Format
 ("SELECT COUNT(*) AS UserCount FROM PersonalData WHERE [DataID] = {0}", dataID);

附加信息:

如果这种方法在网络上被使用，比如从查询字符串中提取ID，那么你就会让自己对SQL注入攻击敞开大门。稍微修改一下代码就可以解决这个问题:

string sql = "SELECT FROM PersonalData WHERE [DataID] = @DataID";
using (OleDbCommand command = new OleDbCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@DataID", dataID);
        }
相关文章：