Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,
I am calling stored procedure from BL Class like this:

C#
public DataSet fn_Search_OPDRegistration(string opd,string pid,string patientname,string frmdate,string todate,int sexid,int departmentid,string doctorid,string mlcstatus,int category,string reffby,string agefrm,string ageto)
    {
        DataSet ds = new DataSet();
        sqlpr = new SqlParameter[13];
        sqlpr[0]=new SqlParameter("@OPD",opd);
        sqlpr[1]=new SqlParameter("@PID",pid);
        sqlpr[2]=new SqlParameter("@Patient_name",patientname);
        if(frmdate!="")
            //sqlpr[3]=new SqlParameter("@FromDate",System.DateTime.Parse(frmdate));
            sqlpr[3] = new SqlParameter("@FromDate", System.DateTime.Parse(frmdate));
        if(todate!="")
            sqlpr[4] = new SqlParameter("@ToDate", System.DateTime.Parse(todate));
        if(sexid!=0)
            sqlpr[5]=new SqlParameter("@SexId ",sexid);
        if(departmentid!=0)
            sqlpr[6]=new SqlParameter("@DepartmentId",departmentid);
        if(doctorid!="0")
            sqlpr[7]=new SqlParameter("@DoctorId",doctorid);
        sqlpr[8]=new SqlParameter("@mlcstatus",mlcstatus);       
        sqlpr[9] = new SqlParameter("@Category",category);
        if (reffby !="Select")
            sqlpr[10] = new SqlParameter("@Reffby", reffby);
        if(agefrm!="")
            sqlpr[11] = new  SqlParameter("@AgeFrm",System.Int32.Parse(agefrm));
        if(ageto!="")
            sqlpr[12]= new SqlParameter("@AgeTo",System.Int32.Parse(ageto));        
        ds=SqlHelper.ExecuteDataset(Con_Str,CommandType.StoredProcedure,"Sp_Serach_OPD_Registration",sqlpr);
        return ds;
    }

My stored Procedure is like this:

SQL
ALTER proc [dbo].[Sp_Search_OPD_Registration]
(
@OPD varchar(30)='',
@PID varchar(30)='',
@Patient_name varchar(100)='',
@FromDate datetime='',
@ToDate datetime='',
@SexId int=null,
@DepartmentId int=null,
@DoctorId varchar(20)='',
@mlcstatus varchar(20)='',
@Category int =null,
@Reffby varchar(50)='',
@AgeFrm int=null,
@AgeTo int =null
)
as
Declare @strQuery nvarchar(MAX)
Declare @strOPD nvarchar(100)
Declare @strPID nvarchar(100)
Declare @strPatientname nvarchar(100)
Declare @strDate nvarchar(100)
Declare @strSexid nvarchar(100)
Declare @strDepartment nvarchar(100)
Declare @strDoctor nvarchar(100)
Declare @strmlcstatus nvarchar(100)
Declare @strCategory nvarchar(100)
Declare @strReffby nvarchar(100)
Declare @strAge nvarchar(100)


set @strQuery='' 
set @strOPD =''
set @strPID =''
set @strPatientname =''
set @strDate =''
set @strSexid =''
set @strDepartment =''
set @strDoctor =''
set @strmlcstatus =''
set @strCategory =''
set @strReffby =''
set @strAge =''


if(@OPD<>'')
	set @strOPD=' and O.OpdNo ='+''''+@OPD+''''

if(@PID<>'')
	set	@strPID=' and O.RegNo ='+''''+@PID+''''

if(@Patient_name<>'')
	set @strPatientname=' and R.Fname ='+ '''' + @Patient_name +''''

if(@FromDate<>'' and @ToDate<>'')	
	set @strDate=' and O.AdmitDate between '+ '''' +convert(nvarchar(12),@FromDate,3)+ '''' +' and ' + '''' +convert(nvarchar(12),@ToDate ,3)+ ''''  
else
	BEGIN
		if(@FromDate<>'')
			set @strDate=' and O.AdmitDate = '+ '''' +convert(nvarchar(12),@FromDate,3)+''''
		else
			if(@ToDate<>'')
				set @strDate='and O.AdmitDate = '+ '''' +convert(nvarchar(12),@ToDate,3)+''''
	END


if(@SexId<>0)
		set @strSexid=' and R.Sex = '+CAST(@SexId as nvarchar)

if(@DepartmentId<>0)
	set @strDepartment=' and O.Department = '+ CAST(@DepartmentId as nvarchar)



if(@DoctorId<>'')
	set @strDoctor=' and O.Doctor ='+''''+ CAST(@DoctorId as nvarchar)+''''

if(@mlcstatus<>'')
	set @strmlcstatus=' and O.mlc ='+''''+CAST(@mlcstatus as nvarchar)+''''

if(@Category<>0)
	set @strCategory=' and O.Category ='+CAST(@Category as nvarchar)

if(@Reffby<> '')
	set @strReffby=' and O.ReffBy ='+''''+CAST(@Reffby as nvarchar)+''''

if(@AgeFrm<>0 and @AgeTo<>0)
	set @strAge='and R.Age between'+ CAST(@AgeFrm as nvarchar)+'and'+ CAST(@AgeTo as nvarchar)
else
	BEGIN
		if(@AgeTo<>0)
			set @strAge=' and R.Age = '+ CAST(@AgeTo as nvarchar)
		else 
			if(@AgeFrm <>0)
				set @strAge=' and R.Age = '+CAST(@AgeFrm as nvarchar)
	END



set @strQuery='select O.AdmitDate,O.Department,O.Doctor,O.ReffBy,O.Category,O.MLC,R.Age,R.Sex from 
OpdPatient as O inner join Registration as R On O.RegNo=R.RegNo where 1=1 and 
O.Deleted=1'+@strOPD+@strPID+@strDate+@strPatientname+@strSexid+@strDepartment+@strDoctor+@strmlcstatus+@strCategory+@strAge+@strReffby

exec sp_executeSQL @strQuery


I am getting this error: Object Reference is not Inisiallize.....

please help me
Thankyou
Posted
Updated 19-May-13 20:28pm
v2

Not sure, but could it be something like this?

C#
if(frmdate!="")
            sqlpr[3] = new SqlParameter("@FromDate", System.DateTime.Parse(frmdate));


That means that if frmdate="", sqlpr[3] is never set.

And your SP expects a datetime parameter, but is not getting it.

Same goes for all the other parameters that are added conditionally with an if.

I would try adding an else and set all the parameters to some default value if they are not supplied.
 
Share this answer
 
Comments
Member 8233601 20-May-13 7:10am    
As you can see i already initialise all the input parameters in my stored procedure. so if frmdate="" it will automatically initialised by default value declared in sp.
Johnny J. 20-May-13 7:14am    
I saw that, but I'm not completely sure it works like that...
Member 8233601 20-May-13 7:37am    
so what should i pass if frmdate =""
Johnny J. 20-May-13 7:41am    
No, it's not that. I'm just unsure how the database objects (SqlCommand in particular) handles it if there are X named parameters defined in a procedure, but only added X-1 named params in the object model.

I seem to recall that I've had a problem with it generating an exception in that case (but I could remember wrong) when you execute the command.

If you put a try/catch around

ds=SqlHelper.ExecuteDataset(Con_Str,CommandType.StoredProcedure,"Sp_Serach_OPD_Registration",sqlpr);

do you catch an Exception then?
Member 8233601 20-May-13 8:02am    
still i am getting this error
Object reference not set to an instance of an object.
The first thing I see is that your code calls the SP "Sp_Serach_OPD_Registration", but in SQL Server it's named "Sp_Search_OPD_Registration".
 
Share this answer
 
Comments
Member 8233601 20-May-13 2:40am    
I have corrected this mistake but still i am getting the same error

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900