c＃中使用的sql存储过程

I have used following stored procedure in sql:

我在sql中使用了以下存储过程:

alter procedure [dbo].[usp_Member_Org_OnGoingJobs]
(
    @idUser varchar(50)

)
as
begin
declare @qry as varchar(max)
set @qry='select J.idJob,j.DateAdded,j.OpenedByWho,j.JobAddress ,j.final,j.idOrg,j.note
                              from Job J 
                              inner join Users U on
                              U.idOrg=J.idOrg
                              where U.IdUser='+ @idUser+ '
                              and ISNULL(j.Final,'')='' 
                              order by idJob'


  execute(@qry)
end
GO

This stored procedure is formed sucessfully in sql.

这个存储过程是在sql中成功形成的。

But, When i tried to use them through asp.net c#, It gives me error:

但是,当我试图通过asp.net c#使用它时,它给了我错误:

Incorrect syntax near the keyword 'order'.

Everything seems correct.

一切似乎都是正确的。

Please tell me where i am making mistake??

请告诉我我在哪里弄错了?

Edit:

private void BindOnGoingJobs()
    {
        string sqlOnGoingJobs = "usp_Member_Org_OnGoingJobs";

        DataTable dtJobList = new DataTable();
        ArrayList paramList = new ArrayList();

        paramList.Add(new ParamData("@idUser", Convert.ToString(Session["idUser"])));
        dtJobList = obj.ExecuteProcedureAndGetDataTable(sqlOnGoingJobs, paramList);
        grdOnGoingJobs.DataSource = dtJobList;
        grdOnGoingJobs.DataBind();

        paramList.Clear();
    }

 public DataTable ExecuteProcedureAndGetDataTable(string procedureName, ArrayList Parameters)
        {
            DataTable dt = new DataTable();
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedureName;
                cmd.Parameters.Clear();
                foreach (ParamData p in Parameters)
                {
                    cmd.Parameters.AddWithValue(p.pName, p.pValue);
                }
                da.SelectCommand = cmd;
                da.Fill(dt);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                con.Close();
                return dt;
            }
        }

3 个解决方案

#1

You need to double your single quotes around the ISNULL check

您需要在ISNULL检查周围加倍单引号

set @qry='select J.idJob,j.DateAdded,j.OpenedByWho,j.JobAddress ,j.final,j.idOrg,j.note
                          from Job J 
                          inner join Users U on
                          U.idOrg=J.idOrg
                          where U.IdUser='+ @idUser+ '
                          and ISNULL(j.Final,'''')='''' 
                          order by idJob'

#2

You need to escape the quotation mark by placing quotes 2 times like this.

你需要通过这样的2次引号来转义引号。

and ISNULL(j.Final,'''')=''''

Check this blog post http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/

查看此博客文章http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-字符串/

#3

Just add the table alias in order by, I guess should solve the issue

只需按顺序添加表别名,我想应该解决问题

order by J.idJob

由J.idJob订购

#1