存储过程不是执行 [英] Stored procedure is not Executing
问题描述
我从BL Class调用存储过程如下:
public DataSet fn_Search_OPDRegistration( string opd, string pid, string patientname, string frmdate, string todate , int sexid, int departmentid, string doctorid, string mlcstatus, int 类别, string reffby, string agefrm, string ageto)
{
DataSet ds = < span class =code-keyword> 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!= 选择 )
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;
}
我的存储过程如下:
ALTER proc [dbo]。[sp_Search_OPD_Registration]
(
@ OPD varchar ( 30 )= ' ',
@ PID varchar ( 30 )= ' ',
@ Patient_name varchar ( 100 )= ' ',
< span class =code-sdkkeyword> @ FromDate datetime = ' ',
@ ToDate d atetime = ' ',
@ SexId int = null,
@ DepartmentId int = null,
@ DoctorId varchar ( 20 )= ' < span class =code-string>',
@ mlcstatus varchar ( 20 )= ' ',
@ Category int = null,
@Reffby varchar ( 50 )= ' ',
@ AgeFrm int = null,
< span class =code-sdkkeyword> @ AgeTo int = null
)
as
< span class =code-keyword>声明 @ strQuery nvarchar (MAX)
声明 @ strOPD nvarchar (< span class =code-digit> 100 )
声明 @ strPID nvarchar ( 100 )
声明 @ strPatientname nvarchar ( 100 )
声明 @ strDate nvarchar ( 100 )
声明 @ strSexid nvarchar ( 100 )
声明 @ strDepartment nvarchar ( 100 )
声明 @ strDoctor nvarchar ( 100 )
声明 @ strmlcstatus nvarchar ( 100 )
声明 @ strCategory nvarchar ( 100 )
声明 @ strReffby nvarchar ( 100 )
声明 @ strAge nvarchar (< span class =code-digit> 100 )
set @ strQuery = ' '
set @ strOPD = ' '
set @ strPID = ' '
set @ strPatientname = ' '
set @strDate = ' '
设置 @ strSexid = ' '
set @ strDepartment = ' '
set @ strDoctor = ' '
set @ strmlcstatus = ' '
set @ strCategory = ' '
set @ strReffby = ' '
set @ strAge = ' '
if (@ OPD<> ' ')
set @ strOPD = ' 和O.OpdNo =' + ' ''' + @ OPD + ' '''
if (@ PID< ;> ' ')
set @ strPID = ' 和O.RegNo =' + ' ''' + @ PID + ' '''
if (@ Patient_name<> ' ')
set @ strPatientname = 和R.Fname =' + ' ''' + @ Patient_name + ' '''
if (@ FromDate<> ' 和 @ToDate<> ' ')
set @ strDate = 和O.AdmitDate在' + ' ''' + convert( nvarchar ( 12 ), @ FromDate ,< span class =code-digit> 3 )+ ' ''' + < span class =code-string>' 和' + ' ''' + convert( nvarchar ( 12 ),< span class =code-sdkkeyword> @ ToDate , 3 )+ ' '''
else
BEGIN
if (@ FromDate<> ' ')
set @ strDate = ' 和O.AdmitDate =' + ' ''' + convert( nvarchar ( 12 ), @ FromDate , 3 )+ ' '''
else
if (@ ToDate<> ' ')
set @ strDate = ' 和O.AdmitDate =' + ' ''' + convert( nvarchar ( 12 ), @ ToDate , 3 )+ ' '''
< span class =code-keyword> END
if (@ SexId<> 0)
set @ strSexid = ' 和R.Sex =' + CAST( @ SexId as nvarchar )
if (@ DepartmentId<> 0)
set @ strDepartment = ' 和O.Department =' + CAST( @ DepartmentId as nvarchar )
if (@ DoctorId<> ' ')
set @ strDoctor = ' 和O.Doctor =' + ' ''' + CAST( @ DoctorId as nvarchar )+ ' '''
if (@ mlcstatus<> ' ')
set @ strmlcstatus = ' 和O.mlc =' + ' ''' + CAST( @ mlcstatus as nvarchar )+ ' '''
if (@ Category<> 0)
set @ strCategory = ' 和O.Category =' + CAST( @ Category as nvarchar )
if (@ Reffby<> ' ')
set @ strReffby = ' 和O.ReffBy =' + ' ''' + CAST( @ Reffby as nvarchar )+ ' '''
if (@ AgeFrm<> 0 和 @ AgeTo<> 0)
set @ strAge = ' 和R.Age在' + CAST之间( @ AgeFrm as nvarchar )+ ' 和' + CAST( @ AgeTo as nvarchar )
else
BEGIN
if (@ AgeTo<> 0)
set @ strAge = ' 和R.Age =' + CAST( @ AgeTo as nvarchar )
else
if ( @AgeFrm <> 0)
set @ strAge = ' < span class =code-string>和R.Age =' + CAST( @ AgeFrm as nvarchar )
END
设置 @ strQuery = ' 选择O.AdmitDate,O.Department,O.Doctor,O .ReffBy,O.Category,O.MLC,R.Age,R.Sex from
OpdPatient as O inner join注册为R On O.RegNo = R.RegNo,其中1 = 1和
O.删除= 1' + @ strOPD + @ strPID + @ strDate + @ strPatientname + @ strSexid + @ strDepartment + @ strDoctor + @ strmlcstatus + @ strCategory + @ strAge + @ strReffby
exec sp_executeSQL @ strQuery
我收到此错误:对象参考不是Inisiallize .....
请帮帮我
谢谢
不确定,但可能是这样的吗?
if (frmdate!= )
sqlpr [ 3 ] = new SqlParameter( @ FromDate,System.DateTime.Parse(frmdate ));
这意味着如果frmdate =,则永远不会设置sqlpr [3]。
你的SP需要一个datetime参数,但是没有得到它。
同样适用于有条件添加的所有其他参数使用if。
如果没有提供,我会尝试添加else并将所有参数设置为默认值。
< blockquote>我看到的第一件事是您的代码调用SPSp_Serach_OPD_Registration,但在SQL Server中它被命名为Sp_Search_OPD_Registration。
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));
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
Not sure, but could it be something like this?
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.
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".
这篇关于存储过程不是执行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!