向多表插入 牛人来找茬！
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 --------------------编程问答-------------------- 贴这么多代码是要猜谜吗 --------------------编程问答-------------------- 是因为我比较懒 而且最近不在状态 怕删错东西了
--------------------编程问答--------------------
设定自动编号也可以自己插入编号，只不过要加一句语句好像
--------------------编程问答--------------------
多加一个编码自动string _code=Guid.NewGuid().ToString();
Guid是不会重复的 --------------------编程问答-------------------- 你还是没表述清楚你的问题，多表插入哪儿冲突了，怎么设置了自增主键列就有冲突 --------------------编程问答-------------------- 因为我的students表里面是有学号的 而teachers表里面没有教职工编号 然后向这两个表里面插入的时候 也要把他们的用户信息插入Users表里面 --------------------编程问答-------------------- 表单验证和sql语句都写在后台代码里吗？
为什么不交给存储过程去做？ --------------------编程问答-------------------- 自动编号就自动编号啊，有什么要紧呢，执行完操作就取最大ID的一条就是你刚插入的ID了。
比如insert完了后select max(id) from Users 这样就取得了刚插入的ID了，然后再插入什么头像啊，其它信息啊，都用这ID就行了。 --------------------编程问答--------------------
我想问下，多个表的数据要传到存储过程是不要建好多参数，也不方便吧 --------------------编程问答--------------------
你现在的问题是什么呢?能具体说下吗,你说你id是自增的,这个和你的问题直接有什么联系??? --------------------编程问答--------------------
你在程序中同样要进行语句拼接，也没比存储过程方便到哪儿去。
另外，存储过程的目的是为了以后维护起来方便。
例如插入操作的时候，将来某一个表有一个字段不需要了，或者查询的时候，将来要你做个排序之类的，你觉得是重新修改代码然后编译再发布方便，还是稍稍动动存储过程方便？ --------------------编程问答-------------------- 给teachers表里加列教职工编号不行么 --------------------编程问答-------------------- 如果“头痛”，那么就改为使用GUID作为数据的uuid嘛。
补充：.NET技术 , ASP.NET
