Selecting specific records in a SQL Server database from C#

Question

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

From the RamResults database
in the Results table
where the Date column is equal to the current 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.date throws an error:

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

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

SELECT Result FROM RamResults

it works perfectly

Did you do any basic research on SQL SELECT syntax? msdn.microsoft.com/en-us/library/ms173294%28SQL.90%29.aspx Note it's the same as every other SQL mainstream SQL dialect... — gbn
– gbn, Commented Jan 2, 2012 at 17:01
== != =...in SQL == is =, also use parameters, what you have there won't work , sql server doesn't know what Form1.date is — SQLMenace
– SQLMenace, Commented Jan 2, 2012 at 17:01
SqlCE does not know anything about your form, so it will not understand what @Form1.date means. For c#, the query is just a meaningless string. C# does not automatically replace this parameter with actual data. Some answers below show how to pass the parameter. — Olivier Jacot-Descombes
– Olivier Jacot-Descombes, Commented Jan 2, 2012 at 17:35

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

Description

2 things

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
You forget to pass in the parameter.

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());
        }
    }
}

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 2, 2012 at 17:03

dknaack

60.7k28 gold badges166 silver badges211 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dennis Traub · Accepted Answer · 2012-01-02 17:02:34Z

5

Try parameterizing your query and replace == with = in your WHERE clause:

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

answered Jan 2, 2012 at 17:02

Dennis Traub

51.9k7 gold badges100 silver badges113 bronze badges

Comments

Aaron · Accepted Answer · 2012-01-02 17:03:30Z

2

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.

answered Jan 2, 2012 at 17:03

Aaron

58k11 gold badges123 silver badges142 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2012-01-02 17:00:52Z

1

Not ==, use = for equation in SQL:

WHERE Date = @Form1.date

answered Jan 2, 2012 at 17:00

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

Comments

ron tornambe · Accepted Answer · 2012-01-02 17:02:44Z

1

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

answered Jan 2, 2012 at 17:02

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

Comments

Toni Parviainen · Accepted Answer · 2012-01-02 17:03:23Z

1

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

answered Jan 2, 2012 at 17:03

Toni Parviainen

2,4172 gold badges17 silver badges16 bronze badges

Comments

Lion · Accepted Answer · 2012-01-02 17:16:18Z

1

Replace your SQL Query with the following:

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

Hope this works.

edited Jan 2, 2012 at 17:16

Lion

19.1k22 gold badges83 silver badges111 bronze badges

answered Jan 2, 2012 at 17:14

Amit Mittal

1,14711 silver badges31 bronze badges

Comments

Ankit K · Accepted Answer · 2012-01-02 21:28:43Z

0

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.

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

answered Jan 2, 2012 at 21:28

Ankit K

111 silver badge1 bronze badge

Comments

Harsh · Accepted Answer · 2012-01-18 12:59:06Z

0

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 )

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());
        }
    }
}

answered Jan 18, 2012 at 12:59

Harsh

2472 silver badges3 bronze badges

Collectives™ on Stack Overflow

Selecting specific records in a SQL Server database from C#

9 Answers 9

Description

Sample

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

Description

Sample

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related