Hi,
I am calling stored procedure from BL Class like this:
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));
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:
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