SQL LIKE query to C# code

Question

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'"

What exception do you get? And wjhat is dateTimePicker1? Shouldn't you use somthing like dateTimePicker1.Value? — Eugene
– Eugene, Commented Jan 8, 2013 at 19:53
Keep in mind you're leaving yourself wide open for a SQL injection attack with that code, be sure to switch to using SqlParameter (msdn.microsoft.com/en-us/library/yy6y35y8(v=VS.80).aspx) An example (stackoverflow.com/questions/2701506/…) — mlorbetske
– mlorbetske, Commented Jan 8, 2013 at 19:54

iCantSeeSharp · Accepted Answer · 2013-01-08 19:57:54Z

5

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

answered Jan 8, 2013 at 19:57

iCantSeeSharp

3,8904 gold badges48 silver badges69 bronze badges

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

Comments

George Marklow · Accepted Answer · 2013-12-17 22:12:02Z

3

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%'");
}

answered Dec 17, 2013 at 22:12

George Marklow

311 bronze badge

Comments

jac · Accepted Answer · 2013-01-08 19:56:45Z

1

Use single quotes for character fields.

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

answered Jan 8, 2013 at 19:56

jac

9,7542 gold badges39 silver badges64 bronze badges

Comments

lante · Accepted Answer · 2013-01-08 20:01:34Z

1

Use % and ' and please consider to use parameters:

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

edited Jan 8, 2013 at 20:01

answered Jan 8, 2013 at 19:56

lante

7,3924 gold badges39 silver badges59 bronze badges

Comments

APhillips · Accepted Answer · 2020-02-18 16:10:07Z

0

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

edited Feb 18, 2020 at 16:10

APhillips

1,19111 silver badges17 bronze badges

answered Feb 18, 2020 at 16:02

Angelo Cuevas Ongue

34 bronze badges

1 Comment

Piotr Labunski Over a year ago

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value.

Collectives™ on Stack Overflow

SQL LIKE query to C# code

ANSWER

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

ANSWER

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related