none
大家帮帮忙:不走else RRS feed

  • 问题

  • protected void Button1_Click(object sender, EventArgs e)
        {
            string strConnection = ConfigurationSettings.AppSettings["strConnection"];
            SqlConnection con = new SqlConnection(strConnection);
            con.Open();
            string strselect = "select * from Registration  where ExamId='"+ this.DropDownList1.SelectedValue +"' and StuId='"+ Session["User"].ToString() +"'";
            SqlCommand cmd = new SqlCommand(strselect, con);
            try
            {
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    this.ClientScript.RegisterStartupScript(typeof(string), "exist", "alert('您已经报名!');", true);
                    sdr.Close();
                }
                else
                {
                    string strinsert = "insert into Registration (StuId,ExamId,RegTime) values('"+ Session["User"].ToString() +"','"+ this.DropDownList1.SelectedValue +"','"+ DateTime.Now.ToString("yyyy-MM-dd") +"')";
                    SqlCommand cmd1 = new SqlCommand(strinsert, con);
                    int result = cmd1.ExecuteNonQuery();
                    if (result > 0)
                        this.ClientScript.RegisterStartupScript(typeof(string), "success", "alert('报名成功!');", true);
                }
                con.Close();
            }
            //错误处理
            catch
            {
                con.Close();
                this.ClientScript.RegisterStartupScript(typeof(string), "error", "alert('出错了!');", true);
            }
    
    }

     

    我做考试报名系统的“考生报名”功能:
    考生登录,进入该功能页面,选择相应的考试名称,点击“报名”
    先检查报名表下,登陆考生是否已经对该考试信息进行报名
    有,则弹出MsgBox(“您已经报名”)
    无,则插入信息,提示(“报名成功”)


    现在这段代码,只能达到检查已经报名并提示,而不能进行新的报名
    到底为什么,看了半天也找不出

    大家帮忙下好吗?

     

    2009年5月22日 11:44

答案

  • --------------------------------------------------------------------------------------------
    T-SQL: 建表、 存储过程及其它
    --------------------------------------------------------------------------------------------
    IF EXISTS (SELECT 1 FROM Sysobjects WHERE NAME = 'Registration' AND Type = 'U')
    	DROP TABLE Registration
    GO 
    
    IF EXISTS (SELECT 1 FROM Sysobjects WHERE NAME = 'spCheckAndUpdate' AND Type = 'P')
    	DROP PROC spCheckAndUpdate
    GO
    
    CREATE TABLE Registration
    (
    	StuId VARCHAR(50),
    	ExamId VARCHAR(50),
    	RegTime DATETIME
    	PRIMARY KEY(StuId, ExamId)
    )
    
    GO
    
    CREATE PROC spCheckAndUpdate
    (
    	@StuId VARCHAR(50),
    	@ExamId VARCHAR(50)
    )
    AS
    IF NOT EXISTS (SELECT 1 FROM Registration WHERE StuId = @StuId AND ExamId = @ExamId) BEGIN
    	INSERT INTO Registration(StuId, ExamId, RegTime) VALUES (@StuId, @ExamId, GETDATE())
    	SELECT 0
    END ELSE BEGIN
    	SELECT 1
    END
    
    --------------------------------------------------------------------------------------------
    -- 页面代码
    --------------------------------------------------------------------------------------------
    protected void Button1_Click(object sender, EventArgs e)
    {
        //string strConnection = @"server=XXY\SQLEXPRESS;database=XSample;Integrated Security=SSPI";
        string strConnection = ConfigurationSettings.AppSettings["strConnection"];
        using (SqlConnection conn = new SqlConnection(strConnection))
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "spCheckAndUpdate";
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddRange(
                new SqlParameter[] { 
                    new SqlParameter("@StuId", Session["User"].ToString()),
                    new SqlParameter("@ExamId", this.DropDownList1.SelectedValue),
                });
            //comm.Parameters.AddRange(
            //    new SqlParameter[] { 
            //        new SqlParameter("@StuId", "X.X.Y1"),
            //        new SqlParameter("@ExamId", "001"),
            //    });
            conn.Open();
            int fResult = (int)comm.ExecuteScalar();
            string fMsg = string.Format("alert('{0}');", (fResult == 0) ? "报名成功!" : "您已经报名!");
            this.ClientScript.RegisterStartupScript(typeof(string), "exist", fMsg, true);
        }
    }


    知识改变命运,奋斗成就人生!
    2009年5月22日 12:32
    版主

全部回复

  • --------------------------------------------------------------------------------------------
    T-SQL: 建表、 存储过程及其它
    --------------------------------------------------------------------------------------------
    IF EXISTS (SELECT 1 FROM Sysobjects WHERE NAME = 'Registration' AND Type = 'U')
    	DROP TABLE Registration
    GO 
    
    IF EXISTS (SELECT 1 FROM Sysobjects WHERE NAME = 'spCheckAndUpdate' AND Type = 'P')
    	DROP PROC spCheckAndUpdate
    GO
    
    CREATE TABLE Registration
    (
    	StuId VARCHAR(50),
    	ExamId VARCHAR(50),
    	RegTime DATETIME
    	PRIMARY KEY(StuId, ExamId)
    )
    
    GO
    
    CREATE PROC spCheckAndUpdate
    (
    	@StuId VARCHAR(50),
    	@ExamId VARCHAR(50)
    )
    AS
    IF NOT EXISTS (SELECT 1 FROM Registration WHERE StuId = @StuId AND ExamId = @ExamId) BEGIN
    	INSERT INTO Registration(StuId, ExamId, RegTime) VALUES (@StuId, @ExamId, GETDATE())
    	SELECT 0
    END ELSE BEGIN
    	SELECT 1
    END
    
    --------------------------------------------------------------------------------------------
    -- 页面代码
    --------------------------------------------------------------------------------------------
    protected void Button1_Click(object sender, EventArgs e)
    {
        //string strConnection = @"server=XXY\SQLEXPRESS;database=XSample;Integrated Security=SSPI";
        string strConnection = ConfigurationSettings.AppSettings["strConnection"];
        using (SqlConnection conn = new SqlConnection(strConnection))
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "spCheckAndUpdate";
            comm.CommandType = CommandType.StoredProcedure;
            comm.Parameters.AddRange(
                new SqlParameter[] { 
                    new SqlParameter("@StuId", Session["User"].ToString()),
                    new SqlParameter("@ExamId", this.DropDownList1.SelectedValue),
                });
            //comm.Parameters.AddRange(
            //    new SqlParameter[] { 
            //        new SqlParameter("@StuId", "X.X.Y1"),
            //        new SqlParameter("@ExamId", "001"),
            //    });
            conn.Open();
            int fResult = (int)comm.ExecuteScalar();
            string fMsg = string.Format("alert('{0}');", (fResult == 0) ? "报名成功!" : "您已经报名!");
            this.ClientScript.RegisterStartupScript(typeof(string), "exist", fMsg, true);
        }
    }


    知识改变命运,奋斗成就人生!
    2009年5月22日 12:32
    版主
  • 非常感谢,功能已经实现
    2009年5月22日 12:56
  • 嗯,上面的方式只有一次数据交互同时完成两个功能
    知识改变命运,奋斗成就人生!
    2009年5月22日 12:57
    版主

  • 能否把GETDATE()格式化一下呢?
    想要原来那种程序中DateTime.Now.ToString("yyyy-MM-dd") 这样的
    但是搜索了下,没找到SQL怎么控制
    2009年5月22日 13:04
  • 存的时候不用改,取的时候可以格式化如

    Select Convert(varchar(20), RegTime, 120) from Registration

    知识改变命运,奋斗成就人生!
    2009年5月22日 13:07
    版主
  • 好的,非常感谢
    2009年5月22日 13:08
  • SELECT 
    	Stuid, 
    	ExamId, 
    	Convert(varchar(20), RegTime, 20),
    	SUBSTRING(Convert(varchar(20), RegTime, 20), 1, 10) -- 只取日间部分不取时间
    	FROM Registration

    知识改变命运,奋斗成就人生!
    2009年5月22日 13:10
    版主