向多表插入 牛人来找茬！

Question

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;public partial class AdminInterface_UserManage : System.Web.UI.Page
{
    string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    SqlConnection sqlConn;
    SqlCommand sqlComm;    public SqlConnection GetConnection()
    {
        string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        SqlConnection sqlConn = new SqlConnection(connStr);
        return sqlConn;
    }    public void FillS()
    {
        sqlConn = new SqlConnection(connStr);
        sqlConn.Open();
        string sqlstr = "select * from Students";
        SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlConn);
        DataSet myds = new DataSet();
        myda.Fill(myds, "Students");
        GridView1.DataKeyNames = new string[] { "StudentId" };
        GridView1.DataBind();
        sqlConn.Close();
    }    public void FillT()
    {
        sqlConn = new SqlConnection(connStr);
        sqlConn.Open();
        string sqlstr = "select * from Teacher";
        SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlConn);
        DataSet myds = new DataSet();
        myda.Fill(myds, "Teacher");
        GridView1.DataKeyNames = new string[] { "CourseName" };
        GridView1.DataBind();
        sqlConn.Close();
    }    bool IsUserNameNotExisted(string username)
    {
        sqlConn = new SqlConnection(connStr);
        sqlConn.Open();
        string sqlStr = "select StudentId from Students where StudentId='" + txtId + "'";
        SqlCommand sqlComm = new SqlCommand(sqlStr, sqlConn);
        using
            (SqlDataReader dr = sqlComm.ExecuteReader())
        {
            if (dr.Read())
            {
                sqlConn.Close();
                return false;
            }
        }
        sqlConn.Close();
        return true;
    }    bool IsCourseNotExisted(string course)
    {
        sqlConn = new SqlConnection(connStr);
        sqlConn.Open();
        string sqlStr = "select CourseName from Teacher where CourseName='" + txtCourse + "'";
        SqlCommand sqlComm = new SqlCommand(sqlStr, sqlConn);
        using
            (SqlDataReader dr = sqlComm.ExecuteReader())
        {
            if (dr.Read())
            {
                sqlConn.Close();
                return false;
            }
        }
        sqlConn.Close();
        return true;
    }    SQLDataClassesDataContext db = new SQLDataClassesDataContext();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["AName"] != null)
        {
            Name.Text = Session["AName"].ToString();
        }
        else
        {
            Name.Text = "管理员身份登陆";
        }
        Session["AName"] = Name.Text;
        var result = from c in db.Users
                     select c;
        GridView1.DataSource = result;
        //GridView1.DataBind();
        //var result = from c in db.Users
        //             select c;
        //GridView1.DataSource = result;
        //GridView1.DataBind();
        //btnUsers.Enabled = false;
    }    protected void btnClass_Click(object sender, EventArgs e)
    {
        Response.Redirect("~/AdminInterface/ClassManage.aspx?AName="+Name);
    }
    protected void btnBack_Click(object sender, EventArgs e)
    {
        Response.Redirect("~/AdminInterface/Home.aspx?AName=" + Name);    }
    protected void btnExit_Click(object sender, EventArgs e)
    {
        Response.Redirect("~/Login.aspx");    }
    protected void ShowInfo_Click(object sender, EventArgs e)
    {
        MultiView1.SetActiveView(View1);
    }
    protected void UpdateInfo_Click(object sender, EventArgs e)
    {
        MultiView1.SetActiveView(View2);
    }
 
    protected void btnAddStu_Click(object sender, EventArgs e)
    {
        MultiView1.SetActiveView(View3);
    }
    protected void btnAddTea_Click(object sender, EventArgs e)
    {
        MultiView1.SetActiveView(View4);
    }    protected void btnSure_Click(object sender, EventArgs e)
    {
        string Id =txtId.Text.Trim();
        string Name = txtName.Text.Trim();
        string sex =girl.Checked ? "男" : "女";
        string age = txtAge.Text.Trim();
        string belong = Belong.SelectedItem.Value;
        string className = txtClassName.Text.Trim();
        string time = Time.SelectedItem.Value;
        string pass = Convert.ToString("111");
        string kind = "学生";
        SqlConnection sqlConn = GetConnection();
        sqlConn.Open();
        //string sql = "insert into Students values('" + Id + "','" + Name + "','" + sex + "','" + age + "','" + belong + "','" + className + "','" + time + "')";
        string sql1 = "insert into Users values('" + Id + "','" + Name + "'','" + pass + "'','" + kind + "')";
        if (txtId.Text.Trim() == "")
        {
            Response.Write("<script>alert('学号不能为空！！')</script>");
            txtId.Focus();
        }
        else if (txtName.Text.Trim() == "")
        {
            Response.Write("<script>alert('姓名不能为空！')</script>");
            txtName.Focus();
        }
        else if (txtAge.Text.Trim() == "")
        {
            Response.Write("<script>alert('年龄不能为空！')</script>");
            txtAge.Focus();
        }
        else if (txtClassName.Text.Trim()=="")
        {
            Response.Write("<script>alert('班级名称不能为空！')</script>");
            txtClassName.Focus();
        }
        else if (!IsUserNameNotExisted(txtId.Text.Trim()))
        {
            Response.Write("<script>alert('该学号已存在！')</script>");
            txtId.Text = "";
            txtName.Text = "";
            txtAge.Text = "";
            txtClassName.Text = "";
            txtId.Focus();
        }        else
        {
            //SqlCommand sqlCom = new SqlCommand(sql, sqlConn);
            SqlCommand sqlCom1 = new SqlCommand(sql1, sqlConn);
            //sqlCom.ExecuteNonQuery();
            sqlCom1.ExecuteNonQuery();
            Response.Write("<script>alert('数据插入成功！')</script>");
            MultiView1.SetActiveView(View2);            
            FillS();
        }
    }
    protected void btnYes_Click(object sender, EventArgs e)
    {
        string course = txtCourse.Text.Trim();
        string classes = txtClass.Text.Trim();
        string teacher = txtTName.Text.Trim();
        string period = txtPeriod.Text.Trim();
        string pass = "111";
        string kind = "教师";
        SqlConnection sqlConn = GetConnection();
        sqlConn.Open();
        string sql = "insert into Teacher values('" + course + "','" + classes + "','" + teacher + "'','" + period + "')";
        string sql1 = "insert into Users(UserName,Password,Purview)values('" + teacher + "'','" + pass + "'','" + kind + "')";
        if (txtCourse.Text.Trim() == "")
        {
            Response.Write("<script>alert('课程名不能为空！！')</script>");
            txtCourse.Focus();
        }
        else if (txtClass.Text.Trim() == "")
        {
            Response.Write("<script>alert('班级名不能为空！')</script>");
            txtClass.Focus();
        }
        else if (!IsCourseNotExisted(txtCourse.Text.Trim()))
        {
            Response.Write("<script>alert('该班级已有该课程！')</script>");
            txtCourse.Text = "";
            txtClass.Text = "";
            txtTName.Text = "";
            txtPeriod.Text = "";
            txtCourse.Focus();
        }        else
        {
            SqlCommand sqlCom = new SqlCommand(sql, sqlConn);
            SqlCommand sqlCom1 = new SqlCommand(sql1, sqlConn);
            sqlCom.ExecuteNonQuery();
            sqlCom1.ExecuteNonQuery();
            Response.Write("<script>alert('数据插入成功！')</script>");
            MultiView1.SetActiveView(View2);   
            FillT();
        }
    }    protected void btnSearch_Click(object sender, EventArgs e)
    {          
        if (ListBox1.SelectedIndex == 0) 
        {
            var result = from c in db.Users
                         where c.UserId.Equals(txtCondition.Text.Trim())
                         select c;
            GridView1.DataSource = result;
            GridView1.DataBind();
        }
        if (ListBox1.SelectedIndex == 1)
        {
            var result = from c in db.Users
                         where c.UserName.Contains(txtCondition.Text.Trim())
                         select c;
            GridView1.DataSource = result;
            GridView1.DataBind();
        }        if (ListBox1.SelectedIndex == 2)
        {
            var result = from c in db.Users
                         where c.Purview.Equals(txtCondition.Text.Trim())
                         select c;
            GridView1.DataSource = result;
            GridView1.DataBind();
        }
    }
    
}
ps ：我的数据库设计跟这个有些冲突，我把Users的主键UserId  设置为了自动编号，现在也在为这个问题头痛！！！！来人解救我吧！多表插入 asp.net 

Answer 1

多加一个编码自动
string _code=Guid.NewGuid().ToString();Guid是不会重复的

Answer 2

因为我的students表里面是有学号的  而teachers表里面没有教职工编号   然后向这两个表里面插入的时候   也要把他们的用户信息插入Users表里面 

Answer 3

表单验证和sql语句都写在后台代码里吗？为什么不交给存储过程去做？

Answer 4

自动编号就自动编号啊，有什么要紧呢，执行完操作就取最大ID的一条就是你刚插入的ID了。
比如insert完了后select max(id) from Users   这样就取得了刚插入的ID了，然后再插入什么头像啊，其它信息啊，都用这ID就行了。

Answer 5

给teachers表里加列教职工编号不行么   

Answer 6

如果“头痛”，那么就改为使用GUID作为数据的uuid嘛。