System.Data.SqlClient.SqlExcep...

Question

0.00/5 (No votes)

See more:

I am trying to get data from database and show it in a ASP.NET DataGrid.

Its showing the above mentioned error.

This is the select command

C#

public MasterJobList GetListForGrid(int RecCount, int PageNo, string OrderBy)
        {
            strSql = "WITH TempTable AS (Select JobDetails.JobCode,JobDetails.CurrentStatus,MasterModel.Name As ModelNumber,MasterModel.Code As ModelCode,MasterBrand.Code As BrandCode,MasterBrand.Name As BrandName,MasterDeviceType.Code As DeviceCode,MasterDeviceType.Name As DType From JobDetails JobDetails  Inner Join MasterDeviceType On JobDetails.DType=MasterDeviceType.Code Inner Join MasterBrand On JobDetails.BCode=MasterBrand.Code Inner join MasterModel on JobDetails.ModelNumber=MasterModel.Code, ROW_NUMBER() OVER (ORDER BY '" + OrderBy + "') AS 'RowNumber' FROM JobDetails  WHERE 1 = 1 " + strFilter + ") SELECT * FROM TempTable WHERE RowNumber BETWEEN " + ((PageNo == 1) ? 1 : ((PageNo - 1) * RecCount) + 1).ToString() + " AND " + ((PageNo == 1) ? RecCount : (PageNo * RecCount)).ToString().ToString();  
            MasterJobList objList = new MasterJobList();
            DataTable dt = new DataTable();
            dt = objDB.GetDataTableFromSQL(strSql);
            if (dt != null)
            {
                foreach (DataRow Dr in dt.Rows)
                {
                    jobs obj = new jobs();
                    obj.JobCode =Convert.ToInt32(Dr["JobCode"].ToString());

                    if (Dr["DType"] != DBNull.Value)
                        obj.DType = Dr["DType"].ToString();
                    else
                        obj.DType = "";

                    if (Dr["BrandName"] != DBNull.Value)
                        obj.BrandName = Dr["BrandName"].ToString();
                    else
                        obj.BrandName = "";

                    if (Dr["ModelNumber"] != DBNull.Value)
                        obj.ModelNumber = Dr["ModelNumber"].ToString();
                    else
                        obj.ModelNumber = "";

                    if (Dr["CurrentStatus"] != DBNull.Value)
                        obj.CurrentStatus = Dr["CurrentStatus"].ToString();
                    else
                        obj.CurrentStatus = "";

                  objList.Add(obj);
                }
            }
            return objList;
        }

The exact error is:

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'OVER'.

Posted 19-Nov-14 1:33am

tastini

Updated 19-Nov-14 2:05am

Thanks7872

v3

Add a Solution

Comments

Shweta N Mishra 19-Nov-14 7:45am

what value your Orderby variable contains?

tastini 19-Nov-14 7:53am

integer

Shweta N Mishra 19-Nov-14 8:26am

It should be the name of column based on the order of which you want to create the rownumber

tastini 19-Nov-14 8:53am

yes it is ...

Richard Deeming 19-Nov-14 12:53pm

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

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

Shweta N Mishra · Answer 1 · 2014-11-19T06:38:00

Solution 2

what i see is that you have defined row_number after your join clause, it should be in select statement instead.

Here is a way to identify the issue.

Debug your code like this.

Before executing strSql, print it and then run the command in SQL Editor where you will be able to identify the issue easily.
or post it here we can then check it.

Also it does not requires single quotes.

Posted 19-Nov-14 6:38am

Shweta N Mishra

Updated 19-Nov-14 6:43am

v2

Comments

tastini 20-Nov-14 4:05am

Check this code , no errors but not return any data
strSql = "WITH TempTable AS (Select JobCode,CurrentStatus,ModelName,BrandName,ReceiptDate, ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") AS 'RowNumber' FROM JobDetails WHERE 1 = 1 " + strFilter + ") SELECT * FROM TempTable WHERE RowNumber BETWEEN " + ((PageNo == 1) ? 1 : ((PageNo - 1) * RecCount) + 1).ToString() + " AND " + ((PageNo == 1) ? RecCount : (PageNo * RecCount)).ToString().ToString();"

Shweta N Mishra 20-Nov-14 4:11am

that depends on the data in your table and the filters and parameters your are applying on it.

you idetify the issue by removing one by one parameter or filter and checking the result.

tastini 20-Nov-14 4:12am

when I am using the row number and filter its not giving any result without that its showing the data. really I don't know where is the problem.

AnvilRanger · Answer 2 · 2014-11-19T01:39:00

Solution 1

Is your variable OrderBy a string? If so you need to include the single quotes.

(ORDER BY '" + OrderBy + "')

Posted 19-Nov-14 1:39am

AnvilRanger

Comments

tastini 19-Nov-14 7:56am

Please check my sql command.