临时表返回空值? [英] temporary table return null values?

问题描述

你好朋友
这是我已发布的存储过程，现在可以正常工作.

Hello friends
this is my stored procedure which have been already posted and now working fine.

create procedure [dbo].[temptable1] (@branchid int,@academicyearid int)
as
begin
set nocount on

declare @branchname varchar(50)
declare @acfy int ;
declare @acty int
select @branchname=Branch_name from Branch where Branch_id=@branchid
select @acfy=Acadamic_year_from,@acty=Acadamic_year_to from Acadamic_year where Acadamic_year_id=@academicyearid
create table #temptable1(Brach_name varchar(50),Acadamic_year_from smallint,Acadamic_year_to smallint)
insert into #temptable1(Brach_name,Acadamic_year_from,Acadamic_year_to)values(@branchname,@acfy,@acty)
select * from #temptable1;

end

这是我用于调用此过程的C#代码.

an this is my c# code for calling this procedure.

SqlCommand cmd = new SqlCommand("temptable1", Dob.con);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.Add(new SqlParameter("@branchid",r));
               cmd.Parameters.Add(new SqlParameter("@academicyearid",s));
               SqlDataAdapter da=new SqlDataAdapter(cmd);
               DataSet ds=new DataSet();
               da.Fill(ds,"#temptable1");
               DataTable dt=ds.Tables["#temptable1"];
               return dt;

但是数据集和数据表返回空值.问题是什么?

but dataset and datatable return null values.what is the problem

推荐答案

我已经检查了上面的查询.唯一的原因应该是参数不正确或没有记录与相应参数匹配.

我尝试使用以下脚本和代码.它的工作.检查一下，
DB ----

I have checked the above query. The only reason should be parameters would be incorrect or no records matches the corresponding parameters.

I tried with below scripts and code. Its working. check with this,
DB----

Create table branch
(branch_id int,
branch_name varchar(10)
)

Create table Acadamic_year
(academic_year_id int,
academic_year_from int,
academic_year_to int)


insert into branch values(1,'MSC')
insert into Acadamic_year values(1,2012,2013)


alter procedure [dbo].[temptable] (@branchid int,@academicyearid int)

as
begin
set nocount on

declare @branchname varchar(50)
declare @acfy int ;
declare @acty int
select @branchname=Branch_name from Branch where Branch_id=@branchid
select @acfy=Academic_year_from,@acty=Academic_year_to from Acadamic_year where Academic_year_id=@academicyearid
create table #temptable(Brach_name varchar(10),Acadamic_year_from smallint,Acadamic_year_to smallint)
insert into #temptable(Brach_name,Acadamic_year_from,Acadamic_year_to)values(@branchname,@acfy,@acty)


select * from #temptable


end

--exec temptable 1,1

C#代码:

--exec temptable 1,1

C# code:

public partial class Form1 : Form
    {
        private SqlConnection con;
        public Form1()
        {
            InitializeComponent();
            con = new SqlConnection("Data Source=localhost\\sqlexpress; Initial Catalog=Cricket; Integrated Security=SSPI;");
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            int r=1;
            int s=1;
            SqlCommand cmd = new SqlCommand("temptable", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@branchid", r));
            cmd.Parameters.Add(new SqlParameter("@academicyearid", s));
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds, "#temptable1");
            DataTable dt = ds.Tables["#temptable1"];

           
        }
    }

尝试:

SqlCommand cmd = new SqlCommand("temptable1");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@branchid"));
cmd.Parameters.Add(new SqlParameter("@academicyearid"));
SqlDataAdapter da=new SqlDataAdapter(cmd, Dob.con);
DataSet ds=new DataSet();
da.Fill(ds,"myTable");
DataTable dt=ds.Tables["myTable"];
return dt;

参考:
MSDN:HOW TO:使用Visual C#.NET在ASP.NET中调用SQL Server存储过程 [ MSDN:HOW TO:使用ADO.NET和Visual C#.NET调用参数化存储过程 [ MSDN:配置参数和参数数据类型(ADO.NET) [

Refer:
MSDN: HOW TO: Call SQL Server Stored Procedures in ASP.NET by Using Visual C# .NET[^]
MSDN: HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]
MSDN: Configuring Parameters and Parameter Data Types (ADO.NET)[^]

不要填充数据集时使用#

Do not use # while filling Dataset

SqlCommand cmd = new SqlCommand("temptable1", Dob.con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@branchid",r));
                cmd.Parameters.Add(new SqlParameter("@academicyearid",s));
                SqlDataAdapter da=new SqlDataAdapter(cmd);
                DataSet ds=new DataSet();
                da.Fill(ds,"temptable1");
                DataTable dt=ds.Tables["#temptable1"];
                return dt;

谢谢
灰烬

Thanks
Ashish

这篇关于临时表返回空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！