bianma是变量,等于M000000040040300
SqlCommand cmd = new SqlCommand("zhengyingcancencu", cn);
cmd.CommandType = CommandType.StoredProcedure; //用的方式设为存储
cmd.Parameters.Add("@abc", SqlDbType.VarChar, 20); //填入参数
cmd.Parameters[0].Value = bianma;
cn.Open(); //打开连接
SqlDataReader rdr = cmd.ExecuteReader(); //获得储存过程的行
dt = rdr.GetSchemaTable(); //把sqldatareader转换为datatable
cn.Close();
出来的dt结果行数为零.
但是我在查询分析器里
exec zhengyingcancencu 'M000000040040300' 是有8条数据,
不知道ado.net执行存储过程的语法错在那里
SqlCommand cmd = new SqlCommand("zhengyingcancencu", cn);
cmd.CommandType = CommandType.StoredProcedure; //用的方式设为存储
cmd.Parameters.Add("@abc", SqlDbType.VarChar, 20); //填入参数
cmd.Parameters[0].Value = bianma;
cn.Open(); //打开连接
SqlDataReader rdr = cmd.ExecuteReader(); //获得储存过程的行
dt = rdr.GetSchemaTable(); //把sqldatareader转换为datatable
cn.Close();
出来的dt结果行数为零.
但是我在查询分析器里
exec zhengyingcancencu 'M000000040040300' 是有8条数据,
不知道ado.net执行存储过程的语法错在那里
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER proc zhengyingcancencu @abc varchar(20)
as
--先判断一下是否存在zhengyingcanlinsi临时表,如果存在就删除掉
if object_id('zhengyingcanlinsi') is not null
drop table zhengyingcanlinsi
select b.invcode as 材料料号,c.cinvname as 材料名称,isnull(c.cinvdefine7,c.cinvstd) + ' ' + isnull(c.cinvdefine6,'') as 规格描述,e.ccomunitname as 单位,
(f.baseqtyn / f.baseqtyd) * (1 + f.compscrap/100) as 用量,c.iSupplyType as 是否虚拟件 into zhengyingcanlinsi
from bom_parent a inner join bas_part d on a.parentid = d.partid inner join bom_opcomponent f on
a.bomid = f.bomid inner join bas_part b on f.componentid = b.partid
inner join inventory c on b.invcode = c.cinvcode inner join ComputationUnit e on c.ccomunitcode = e.ccomunitcode and c.cgroupcode = e.cgroupcode
where d.invcode =@abc order by b.invcode DESC
--如果存在虚拟件,就执行以下操作
if exists(select * from zhengyingcanlinsi where 是否虚拟件 = 3)
begin --先追加虚拟件材料
insert into zhengyingcanlinsi
select b.invcode as 材料料号,c.cinvname as 材料名称,isnull(c.cinvdefine7,c.cinvstd) + ' ' + isnull(c.cinvdefine6,'') as 规格描述,e.ccomunitname as 单位,
(f.baseqtyn / f.baseqtyd) * (1 + f.compscrap/100)*g.用量 as 用量,c.iSupplyType as 是否虚拟件
from bom_parent a inner join bas_part d on a.parentid = d.partid inner join bom_opcomponent f on
a.bomid = f.bomid inner join bas_part b on f.componentid = b.partid
inner join inventory c on b.invcode = c.cinvcode inner join ComputationUnit e on c.ccomunitcode = e.ccomunitcode and c.cgroupcode = e.cgroupcode
inner join (select 材料料号,用量 from zhengyingcanlinsi where 是否虚拟件 = 3) g on d.invcode = g.材料料号
order by b.invcode DESC
--删除虚拟件
delete zhengyingcanlinsi
where 是否虚拟件 = 3
end
--最后查询所有的材料出来
select * from zhengyingcanlinsiGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
cmd.CommandType = CommandType.StoredProcedure; //用的方式设为存储
cmd.Parameters.Add("@abc", SqlDbType.VarChar, 20); //填入参数
cmd.Parameters[0].Value = bianma;
cn.Open(); //打开连接
cmd.ExecuteNonQuery();
cn.Close();
SqlConnection con = new SqlConnection(strcon);
SqlCommand com = new SqlCommand("CheckAcc", con);
com.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0]; //con.Open();
//SqlDataReader dr = com.ExecuteReader();
//DataTable dt;
//con.Close();
GridView1.DataSource = dt;
GridView1.DataBind();
不妨试下SqlDataAdapter
dt = rdr.GetSchemaTable(); //把sqldatareader转换为datatable
应该这样。
dt.load(dr).