windows 从 C# 中选择 SQL Server 数据库中的特定记录

Selecting specific records in a SQL Server database from C#

提问by Mike

I am currently trying to grab some rows from a SQL Server database using C# that are of the following criteria:

我目前正在尝试使用 C# 从 SQL Server 数据库中获取一些符合以下条件的行：

• From the RamResultsdatabase
• in the Resultstable
• where the Datecolumn is equal to the current date

• 从RamResults数据库
• 在Results桌子上
• 其中该Date列等于当前日期

I have the following so far:

到目前为止，我有以下几点：

// Open the same connection with the same connection string.
using (SqlCeConnection con = new SqlCeConnection(DatabaseControl.conString))
{
   con.Open();
   // Read specific values in the table.
   using (SqlCeCommand com = new SqlCeCommand("SELECT Result FROM RamResults WHERE Date == @Form1.date", con))
   {
      SqlCeDataReader reader = com.ExecuteReader();
      while (reader.Read())
      {
         int resultsoutput = reader.GetInt32(0);
         MessageBox.Show(resultsoutput.ToString());
      }
   }
}

Using SELECT Result FROM RamResults WHERE Date == Form1.datethrows an error:

使用SELECT Result FROM RamResults WHERE Date == Form1.date会引发错误：

There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = = ]

解析查询时出错。[令牌行号= 1，令牌行偏移= 43，令牌错误== ]

Although if I take out the WHERE statement e.g.

虽然如果我取出 WHERE 语句，例如

SELECT Result FROM RamResults

it works perfectly

它完美地工作

采纳答案by dknaack

Description

描述

2 things

2件事

1. Use =instead of ==because this is the right equals operator in T-SQL. Your Query should be like this

   SELECT Result FROM RamResults WHERE Date = @Date

2. You forget to pass in the parameter.

1. 使用=而不是==因为这是 中的正确等于运算符T-SQL。你的查询应该是这样的

   SELECT Result FROM RamResults WHERE Date = @Date

2. 您忘记传入参数。

Sample

样本

// Open the same connection with the same connection string.
using (SqlCeConnection con = new SqlCeConnection(DatabaseControl.conString))
{
    con.Open();
    // Read specific values in the table.
    using (SqlCeCommand com = new SqlCeCommand("SELECT Result FROM RamResults WHERE Date = @Date", con))
    {
        com.Parameters.AddWithValue("@Date", Form1.date);
        SqlCeDataReader reader = com.ExecuteReader();
        while (reader.Read())
        {
            int resultsoutput = reader.GetInt32(0);
            MessageBox.Show(resultsoutput.ToString());
        }
    }
}

回答by Dennis Traub

Try parameterizing your query and replace ==with =in your WHEREclause:

尝试参数化您的查询并在您的子句中替换==为：=WHERE

// ...
using (SqlCeCommand com = 
    new SqlCeCommand("SELECT Result FROM RamResults WHERE Date = @date", con))
{
    com.Parameters.Add(new SqlParameter("date", Form1.date));
    // ...
}
// ...

回答by Aaron

Try this:

尝试这个：

using (SqlCeCommand com = new SqlCeCommand("SELECT Result FROM RamResults WHERE Date = @date", con))
    {
        com.Parameters.AddWithValue("date",Form1.date);
        SqlCeDataReader reader = com.ExecuteReader();
        while (reader.Read())

Always use SQL parameters instead of string concatenation.

始终使用 SQL 参数而不是字符串连接。

回答by ron tornambe

The operator "==" is invalid syntax for SQL. Use a single equal sign "=" in the where clause.

运算符“==”是 SQL 的无效语法。在 where 子句中使用单个等号“=”。

回答by Toni Parviainen

Try

尝试

var query = "SELECT Result FROM RamResults WHERE Date = " + Form1.date;
using (SqlCeCommand com = new SqlCeCommand(query, con))

I would suggest using parameters as in this example on MSDN

我建议使用MSDN 上这个示例中的参数

回答by ypercube??

Not ==, use =for equation in SQL:

不是==，=用于 SQL 中的方程：

WHERE Date = @Form1.date

回答by Amit Mittal

Replace your SQL Query with the following:

将您的 SQL 查询替换为以下内容：

SELECT Result 
FROM RamResults 
WHERE Date like DATEADD(day, DATEDIFF(day, 0,  getdate()), 0)

Hope this works.

希望这有效。

回答by Ankit K

That is quite confusing and many people commit these kind of mistakes. While C# uses == for equality Operations (Ok We do have Equal() as well), SQL uses just = for it.

这很令人困惑，很多人都会犯这种错误。虽然 C# 使用 == 进行相等操作（好吧，我们也有 Equal()），而 SQL 仅使用 = 作为它。

Apart from this, you also forgot to pass the parameter here

除此之外，你还忘记在这里传递参数

回答by Harsh

I found two things in your code which is creating a problem

我在您的代码中发现了两件事，这造成了问题

1) assign values to parameter 2) == instead of = (just to make it working appropriately with SQL )

1) 为参数赋值 2) == 而不是 = （只是为了使它与 SQL 正常工作）

so the code should be like this :

所以代码应该是这样的：

using (SqlCeConnection con = new SqlCeConnection(DatabaseControl.conString))
    {
    con.Open();
    using (SqlCeCommand com = new SqlCeCommand("SELECT Result FROM RamResults WHERE Date = @Date", con))
    {
        com.Parameters.AddWithValue("@Date", Form1.date);
        SqlCeDataReader reader = com.ExecuteReader();
        while (reader.Read())
        {
            int resultsoutput = reader.GetInt32(0);
            MessageBox.Show(resultsoutput.ToString());
        }
    }
}