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

[zhqiao0729]

我需要在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;";

红字部分总是显示曲线错误。。。请问怎么处理查询子句插入到新表的操作。。

[宇哥_]

insert into ... values(...)

[宇哥_]

insert into table select * from Product

[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;";

[save4me]

这个查询看上去很乱，这么多子查询~~ 查询字符串中的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;

[lutaotony]

你可以断点吧sql查出来放到数据库中取执行，看看执行效果就知道是不是sql的问题了

[M依然]

引号前面加上@。string 的变量不加上@ 只有全部在一行才会生成一个值

[zhqiao0729]

引用 6 楼 lutaotony 的回复:

你可以断点吧sql查出来放到数据库中取执行，看看执行效果就知道是不是sql的问题了

语句在数据库执行过，没问题可以插入，不过当时@t是用的一个固定的值，放在程序里就不行了，语句不执行。。。

[yzf86211861]

楼主的水平真是连 学生都不如哦。

[apple8160]

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;";

[apple8160]

少了个 VALUES

[zhqiao0729]

引用 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 呈现

[save4me]

		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();
		}

[by_封爱]

   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;";
 

尼玛 你弄一行 肯定没错了...