CODEWITHSUNDEEP
c#sql
Silvia Stoyanova
Silvia Stoyanova

Reputation: 281

SQL LIKE query to C# code

I need to use the following query in my C# code:

SELECT AVG(Percent) 
From Table1
Where code Like "Sport" and Year Like"2011" and Sitting Like"June";

I did it like this:

"SELECT AVG(Percentage) FROM MasterTable WHERE Code LIKE " + comboBoxSubject.Text +
"AND Year LIKE "+dateTimePicker1 +" AND Sitting LIKE June"

but i get an exception probably because the parameters are extracted from different controls and are not placed in inverted commas.

Can anyone help me ?

ANSWER

That is the query that worked for me:

"SELECT AVG(Percent) FROM MasterTable WHERE Code LIKE '" + comboBoxSubject.Text + "' AND Year LIKE '" + dateTimePicker1.Value.Year + "' AND Sitting LIKE 'June'"

Upvotes: 3

Views: 31141

Answers (5)

Angelo Cuevas Ongue
Angelo Cuevas Ongue

Reputation: 3

MySqlCommand cmd = new MySqlCommand("SELECT Employee_No, Image, Last_Name, First_Name, Middle_Name, Suffix, Sex, Birthdate, Contact_No, Address, Username FROM user_tbl WHERE Employee_No LIKE '%" + searchemployeeno + "%' OR Last_Name LIKE '%" + searchemployeeno + "%' ", SQLConn.conn);

Upvotes: 0

George Marklow
George Marklow

Reputation: 31

There are three problems.

  • There's no space after the code value and AND
  • There are missing single quotes between values
  • The wildcard symbol (%) is missing from the SQL LIKE statements

It depends what kind of project you are working on but often I find it is much easier to spot syntax errors and missing spaces by printing the end query out. For example, below is a console application that does this.

static void Main(string[] args)
{
    const string code = "Sport";
    const string year = "2011";
    Console.WriteLine("SELECT AVG(Percentage) FROM MasterTable WHERE Code LIKE '%" + code + "%' AND Year LIKE '%" + year + "%' AND Sitting LIKE '%June%'");
}

Upvotes: 3

lante
lante

Reputation: 7346

Use % and ' and please consider to use parameters:

SELECT AVG(Percentage) FROM MasterTable WHERE (Code LIKE '%' + @text + '%')

Upvotes: 1

iCantSeeSharp
iCantSeeSharp

Reputation: 3880

Supposing you use SQLite, because you don't mention any database. This is how you can avoid SQL injection.

var selectCommand = new SQLiteCommand("@SELECT AVG (PERCENT) 
                                       FROM TABLE1
                                       WHERE CODE LIKE @sport AND YEAR LIKE @year AND SITTING LIKE @month");
selectCommand.Parameters.AddWithValue("@sport", sportParameter);
selectCommand.Parameters.AddWithValue("@year", yearParameter);
selectCommand.Parameters.AddWithValue("@month", monthParameter);

Upvotes: 5

jac
jac

Reputation: 9726

Use single quotes for character fields.

"SELECT AVG(Percentage) FROM MasterTable WHERE Code LIKE '" + comboBoxSubject.Text +
        "' AND Year LIKE '" + dateTimePicker1 + "' AND Sitting LIKE 'June'"

Upvotes: 1

Related Questions