CODEWITHSUNDEEP
c#asp.netsqlstored-proceduressql-server-2008-r2
C Sharper
C Sharper

Reputation: 8626

stored procedure in sql used in c#

I have used following stored procedure in 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.

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

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

Upvotes: 0

Views: 122

Answers (3)

Puneet Khurana
Puneet Khurana

Reputation: 396

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

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/

Upvotes: 1

Steve
Steve

Reputation: 216293

You need to double your single quotes around the ISNULL check

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'

Upvotes: 4

Umesh
Umesh

Reputation: 2732

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

order by J.idJob

Upvotes: 0

Related Questions