• 主页
  • ASP
  • .NET Framework
  • Web Services
  • VB
  • VC
  • 图表区
  • 分析与设计
  • 组件/控件开发
  • LINQ

asp.net程序中怎么操作SQLSEVER数据库!!??求大神帮忙!

zhqiao0729 2014-05-19 04:33:26
我需要在asp.net中编写对SQLSERVER的查询插入操作,@t是我从文本框获得的数据
String constr = "Data Source=localhost;Integrated Security=False;Initial Catalog=Lxj;User ID=sa;Password=sa";
SqlConnection conn = new SqlConnection(constr); //创建链接
conn.Open(); //打开链接

//创建数据库操作语句
String cmdstr = "select * from YSXS1 where time='" + T.Text.Trim() + "'";
string 年月 = T.Text.Trim();
string insertstr = "insert into CTXS(ctid,time,yye,kds,xfrs,kdj,rjxf,jlxs)
select N001 as ctid, @t as time,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t) as yye,(select kdsl from YSXS2 where
ctid= N001 and time= @t) as kds,(select xfrs from YSXS2 where
ctid= N001 and time= @t)as xfrs,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select kdsl from YSXS2 where
ctid= N001 and time= @t) as kdj,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select xfrs from YSXS2 where
ctid= N001 and time= @t) as rjxf,(select sum(cpje)
from YSXS1,CPXX where CPXX.cpid=YSXS1.cpid
and CPXX.gdfl=鸡类 and ctid=N001 and time=@t)as jlxs;";


红字部分总是显示曲线错误。。。请问怎么处理查询子句插入到新表的操作。。
...全文
223 点赞 收藏 13
写回复
13 条回复
by_封爱 2014年05月21日

   string insertstr = "insert into CTXS(ctid,time,yye,kds,xfrs,kdj,rjxf,jlxs) select N001 as ctid, @t as time,(select sum(cpje) from YSXS1 where ctid= N001 and time= @t) as yye,(select  kdsl from YSXS2 where ctid= N001 and time= @t) as kds,(select xfrs from YSXS2 where  ctid= N001 and time= @t)as xfrs,(select sum(cpje) from YSXS1 where ctid= N001 and time= @t)*1.00/(select  kdsl from YSXS2 where ctid= N001 and time= @t)  as kdj,(select sum(cpje) from YSXS1 where ctid= N001 and time= @t)*1.00/(select xfrs from YSXS2 where  ctid= N001 and time= @t) as rjxf,(select sum(cpje) from YSXS1,CPXX where CPXX.cpid=YSXS1.cpid  and CPXX.gdfl=鸡类 and ctid=N001 and time=@t)as jlxs;";
 
尼玛 你弄一行 肯定没错了...
回复 点赞
save4me 2014年05月21日

		protected void Button8_Click(object sender, EventArgs e)
		{
			String constr = "Data Source=localhost;Integrated Security=False;Initial Catalog=Lxj;User ID=sa;Password=sa";
			SqlConnection conn = new SqlConnection(constr);     //创建链接
			conn.Open();        //打开链接
			
			//创建数据库操作语句
                       下面这句你好像都没用到
			String cmdstr = "select * from YSXS1 where time='" + T.Text.Trim() + "'";
			string 年月 = T.Text.Trim();
			string insertstr = @" INSERT INTO CTXS (ctid
     , TIME
     , yye
     , kds
     , xfrs
     , kdj
     , rjxf
     , jlxs)
    SELECT 'N001' AS ctid
         , @t AS TIME
         , (SELECT sum (cpje)
            FROM YSXS1
            WHERE ctid = 'N001' AND TIME = @t) AS yye
         , (SELECT kdsl
            FROM YSXS2
            WHERE ctid = 'N001' AND TIME = @t) AS kds
         , (SELECT xfrs
            FROM YSXS2
            WHERE ctid = 'N001' AND TIME = @t) AS xfrs
         , (SELECT sum (cpje)
            FROM YSXS1
            WHERE ctid = 'N001' AND TIME = @t) * 1.00 / (SELECT kdsl
                                                       FROM YSXS2
                                                       WHERE ctid = 'N001' AND TIME = @t) AS kdj
         , (SELECT sum (cpje)
            FROM YSXS1
            WHERE ctid = 'N001' AND TIME = @t) * 1.00 / (SELECT xfrs
                                                       FROM YSXS2
                                                       WHERE ctid = 'N001' AND TIME = @t) AS rjxf
         , (SELECT sum (cpje)
            FROM YSXS1
                , CPXX
            WHERE CPXX.cpid = YSXS1.cpid AND CPXX.gdfl = '鸡类' AND ctid = 'N001' AND TIME = @t) AS jlxs;";
                        SqlCommand cmd = new SqlCommand(insertstr, conn);
                       //插入SQL语句里面有@t,所以加了个参数,具体的值根据需要改一下
			cmd.Parameters.Add("t", 年月);
			//int ret = cmd.ExecuteNonQuery();
                       //执行插入语句
			cmd.ExecuteNonQuery();
			
			string strSQL = "select CTXS.* from CTXS where time = @t";
			SqlDataAdapter myAdapter = new SqlDataAdapter(strSQL, conn);
			DataSet myDS = new DataSet();
			
			
			myAdapter.SelectCommand.Parameters.Add(new SqlParameter("@t", SqlDbType.Char, 6));
			myAdapter.SelectCommand.Parameters["@t"].Value = 年月;
			myAdapter.Fill(myDS, "CTXS");
			
			dataGridView1.DataSource = null;
			dataGridView1.DataSource = myDS;
			dataGridView1.DataBind();
			
			dataGridView1.Visible = true;
			conn.Close();
		}
回复 点赞
zhqiao0729 2014年05月20日
引用 3 楼 apple8160 的回复:
string insertstr = @"insert into CTXS(ctid,time,yye,kds,xfrs,kdj,rjxf,jlxs)
select N001 as ctid, @t as time,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t) as yye,(select  kdsl from YSXS2 where
ctid= N001 and time= @t) as kds,(select xfrs from YSXS2 where 
ctid= N001 and time= @t)as xfrs,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select  kdsl from YSXS2 where
ctid= N001 and time= @t)  as kdj,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select xfrs from YSXS2 where 
ctid= N001 and time= @t) as rjxf,(select sum(cpje)
from YSXS1,CPXX where CPXX.cpid=YSXS1.cpid 
and CPXX.gdfl=鸡类 and ctid=N001 and time=@t)as jlxs;";
protected void Button8_Click(object sender, EventArgs e)
    {
         String constr = "Data Source=localhost;Integrated Security=False;Initial Catalog=Lxj;User ID=sa;Password=sa";
                SqlConnection conn = new SqlConnection(constr);     //创建链接          
                conn.Open();     //打开链接
                
        //创建数据库操作语句
                String cmdstr = "select * from YSXS1 where time='" + T.Text.Trim() + "'";
                string 年月 = T.Text.Trim();

               string insertstr = @"insert into CTXS(ctid,time,yye,kds,xfrs,kdj,rjxf,jlxs)
select N001 as ctid, @t as time,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t) as yye,(select  kdsl from YSXS2 where
ctid= N001 and time= @t) as kds,(select xfrs from YSXS2 where 
ctid= N001 and time= @t)as xfrs,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select  kdsl from YSXS2 where
ctid= N001 and time= @t)  as kdj,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select xfrs from YSXS2 where 
ctid= N001 and time= @t) as rjxf,(select sum(cpje)
from YSXS1,CPXX where CPXX.cpid=YSXS1.cpid 
and CPXX.gdfl=鸡类 and ctid=N001 and time=@t)as jlxs;";
               SqlCommand cmd = new SqlCommand(insertstr, conn);
               
            string strSQL = "select CTXS.* from CTXS where time = @t";
            SqlDataAdapter myAdapter = new SqlDataAdapter(strSQL, conn);
            DataSet myDS = new DataSet();
       

            myAdapter.SelectCommand.Parameters.Add(new SqlParameter("@t", SqlDbType.Char, 6));
            myAdapter.SelectCommand.Parameters["@t"].Value = 年月;
            myAdapter.Fill(myDS, "CTXS");

            GridView1.DataSourceID = null;
            GridView1.DataSource = myDS;
            GridView1.DataBind();

            GridView1.Visible = true;
            conn.Close();
这样倒是没错误但是点击按钮后没有反应啊,数据库也没有变化。。。请大神给看看,语句是不是有错误。我是需要把查询的数据放入另一个表,然后再把这个表中的数据利用GridView 呈现
回复 点赞
apple8160 2014年05月20日
少了个 VALUES
回复 点赞
apple8160 2014年05月20日
string insertstr = @"insert into CTXS(ctid,time,yye,kds,xfrs,kdj,rjxf,jlxs)  VALUES
select N001 as ctid, @t as time,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t) as yye,(select  kdsl from YSXS2 where
ctid= N001 and time= @t) as kds,(select xfrs from YSXS2 where 
ctid= N001 and time= @t)as xfrs,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select  kdsl from YSXS2 where
ctid= N001 and time= @t)  as kdj,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select xfrs from YSXS2 where 
ctid= N001 and time= @t) as rjxf,(select sum(cpje)
from YSXS1,CPXX where CPXX.cpid=YSXS1.cpid 
and CPXX.gdfl=鸡类 and ctid=N001 and time=@t)as jlxs;";
回复 点赞
yzf86211861 2014年05月20日
楼主的水平真是连 学生都不如哦。
回复 点赞
zhqiao0729 2014年05月20日
引用 6 楼 lutaotony 的回复:
你可以断点吧sql查出来放到数据库中取执行,看看执行效果就知道是不是sql的问题了
语句在数据库执行过,没问题可以插入,不过当时@t是用的一个固定的值,放在程序里就不行了,语句不执行。。。
回复 点赞
M依然 2014年05月20日
引号前面加上@。string 的变量不加上@ 只有全部在一行才会生成一个值
回复 点赞
lutaotony 2014年05月20日
你可以断点吧sql查出来放到数据库中取执行,看看执行效果就知道是不是sql的问题了
回复 点赞
save4me 2014年05月19日
这个查询看上去很乱,这么多子查询~~ 查询字符串中的N001是什么?如果是值,需要使用单引号,同样鸡类也是值,需要使用单引号括起来。 INSERT INTO CTXS (ctid , TIME , yye , kds , xfrs , kdj , rjxf , jlxs) SELECT 'N001' AS ctid , @t AS TIME , (SELECT sum (cpje) FROM YSXS1 WHERE ctid = 'N001' AND TIME = @t) AS yye , (SELECT kdsl FROM YSXS2 WHERE ctid = 'N001' AND TIME = @t) AS kds , (SELECT xfrs FROM YSXS2 WHERE ctid = 'N001' AND TIME = @t) AS xfrs , (SELECT sum (cpje) FROM YSXS1 WHERE ctid = 'N001' AND TIME = @t) * 1.00 / (SELECT kdsl FROM YSXS2 WHERE ctid = 'N001' AND TIME = @t) AS kdj , (SELECT sum (cpje) FROM YSXS1 WHERE ctid = 'N001' AND TIME = @t) * 1.00 / (SELECT xfrs FROM YSXS2 WHERE ctid = 'N001' AND TIME = @t) AS rjxf , (SELECT sum (cpje) FROM YSXS1 , CPXX WHERE CPXX.cpid = YSXS1.cpid AND CPXX.gdfl = '鸡类' AND ctid = 'N001' AND TIME = @t) AS jlxs;
回复 点赞
apple8160 2014年05月19日
string insertstr = @"insert into CTXS(ctid,time,yye,kds,xfrs,kdj,rjxf,jlxs)
select N001 as ctid, @t as time,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t) as yye,(select  kdsl from YSXS2 where
ctid= N001 and time= @t) as kds,(select xfrs from YSXS2 where 
ctid= N001 and time= @t)as xfrs,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select  kdsl from YSXS2 where
ctid= N001 and time= @t)  as kdj,(select sum(cpje) from YSXS1 where
ctid= N001 and time= @t)*1.00/(select xfrs from YSXS2 where 
ctid= N001 and time= @t) as rjxf,(select sum(cpje)
from YSXS1,CPXX where CPXX.cpid=YSXS1.cpid 
and CPXX.gdfl=鸡类 and ctid=N001 and time=@t)as jlxs;";
回复 点赞
宇哥_ 2014年05月19日
insert into table select * from Product
回复 点赞
宇哥_ 2014年05月19日
insert into ... values(...)
回复 点赞
发动态
发帖子
.NET技术社区
创建于2007-09-28

4.9w+

社区成员

66.8w+

社区内容

.NET技术交流专区
社区公告
暂无公告