CODEWITHSUNDEEP
c#.netsqlsql-serverwindows
Mike
Mike

Reputation: 2396

Selecting specific records in a SQL Server database from C#

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

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

Upvotes: 6

Views: 11977

Answers (9)

Harsh
Harsh

Reputation: 247

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

Upvotes: 0

Ankit K
Ankit K

Reputation: 11

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

Upvotes: 0

Amit Mittal
Amit Mittal

Reputation: 1127

Replace your SQL Query with the following:

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

Hope this works.

Upvotes: 1

dknaack
dknaack

Reputation: 60556

Description

2 things

  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.

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

Upvotes: 6

Aaron
Aaron

Reputation: 57843

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.

Upvotes: 2

Toni Parviainen
Toni Parviainen

Reputation: 2387

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

Upvotes: 1

ron tornambe
ron tornambe

Reputation: 10780

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

Upvotes: 1

Dennis Traub
Dennis Traub

Reputation: 51694

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));
    // ...
}
// ...

Upvotes: 5

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

Not ==, use = for equation in SQL:

WHERE Date = @Form1.date

Upvotes: 1

Related Questions