Sql gives error Incorrect syntax near 'like' keyword.

Question

0.00/5 (No votes)

See more:

I am trying to run sql query from program , the query when used in stored procedure, it works fine , but when using from program it gives above error.I cant use stored procedures as per clients requirement. my code is bellow,

C#

DateTime frmdate = Convert.ToDateTime(dtpfrom.Text);
                    DateTime todate = Convert.ToDateTime(dtpto.Text);
                    string datetimefrm = frmdate.ToString("yyyy-MM-dd 00:00:00.000");
                    string datetimeto = todate.ToString("yyyy-MM-dd 00:00:00.000");

   string ap = "Approved"+'%';
   string st = "Fully Processed"+'%';
                        
 string query = "SELECT DISTINCT indent_no as 'Indent No' ," +
                 "approved_date as 'Approved Date'," +
                 "from_store_name as 'From Store',to_store_name as 'To Store'," +
                 "indent_status as 'Indent Status'," +
                 "processing_status as 'Processing Status'" +
                 "FROM vw_DSInfo_indent_details" +
                 "WHERE [indent_status] like '"+ap+"'" +
        "AND [approved_date] BETWEEN '" + datetimefrm + "' AND '" + datetimeto + "'" +
        "AND [processing_status] NOT LIKE '"+st+"'" +
        "AND [from_store_name] ='" + txtfromstore.SelectedItem + "'"+
        "AND [to_store_name] = '" + txttostore.SelectedItem + "'";
                          
                            cmd = new SqlCommand();
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = query;
                            cmd.Connection = con;
                            table = new DataTable();
                            adapter = new SqlDataAdapter(cmd);
                            adapter.Fill(table);

Posted 21-Jan-16 0:36am

Member 11543226

Updated 21-Jan-16 0:39am

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-01-21T00:53:00

Spaces.

C#

string query = "SELECT DISTINCT indent_no as 'Indent No' ," +
                 "approved_date as 'Approved Date'," +
                 "from_store_name as 'From Store',to_store_name as 'To Store'," +
                 "indent_status as 'Indent Status'," +
                 "processing_status as 'Processing Status'" +
                 "FROM vw_DSInfo_indent_details" +
                 "WHERE [indent_status] like '"+ap+"'" +

Generates a string like:

SELECT DISTINCT ...processing_status as 'Processing Status'FROM vw_DSInfo_indent_detailsWHERE [indent_status] like ...

You get away with it for the Status'FROM becuaset the quote terminates the prvious data. But

vw_DSInfo_indent_detailsWHERE

is taken as a single word.
And please, don't do it like that.
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. You get away with it here because you define the strings yourself, but it's a dangerous precedent and may well leave you wide open to SQL Injection if the code is ever changed. Get into the habit of using parameterised queries at all times - it's a lot easier to read, and a lot safer!

F-ES Sitecore · Answer 2 · 2016-01-21T00:55:00

Use the debugger to examine the contents of the query variable and you'll see it has things like

processing_status as 'Processing Status'FROM vw_DSInfo_indent_detailsWHERE [indent_status] like 'xyz'AND [approved_date] BETWEEN

You're not adding the appropriate spaces.

FROM vw_DSInfo_indent_details" +
" WHERE [indent_status] like '"+ap+"'" +
" AND [approved_date] BETWEEN '" + datetimefrm + "' AND '" + datetimeto + "'" +

You should also use paramterised queries as your code will fail if the data you search for contains an apostrophe, and worse it leaves you open to SQL injection attacks. Google for how to use paramterised queries with ado.net for examples.

koolprasad2003 · Answer 3 · 2016-01-21T01:20:00

Solution 3

It looks single quotes are extra.
Simply just replace your variable with following code

C#

string ap = "Approved%";
string st = "Fully Processed%";

Posted 21-Jan-16 1:20am

koolprasad2003

Sql gives error Incorrect syntax near 'like' keyword.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0