news_category表是:
category_id 主键
category_namenew表是
category_id 不为空
news_id 主键
title现在我news_category做个插入完了,然后我在new也在插入,category_id怎么样才能插入到new表里啊
希望各位老师能写详细下,我是新手,sql语句应该怎么写还有代码!
category_id 主键
category_namenew表是
category_id 不为空
news_id 主键
title现在我news_category做个插入完了,然后我在new也在插入,category_id怎么样才能插入到new表里啊
希望各位老师能写详细下,我是新手,sql语句应该怎么写还有代码!
这个网上多的是!自己找找!
如 select category_id from news_category into new(sssssss)试试不行给我信息。
{
if (!IsPostBack)
{
//绑定category_id
DataTypeBind(); } } private void DataTypeBind()
{
DataTable xTable = new DataTable(); xTable.Columns.Add("category_id", System.Type.GetType("System.String"));
xTable.Columns.Add("category_Name", System.Type.GetType("System.String")); DataRow dr = xTable.NewRow();
dr["category_id"] = "1";
dr["category_Name"] = "category_Name"; xTable.Rows.Add(dr);
//xTable你要自己从数据库拿。这个是测试用的而已。
this.DropDownList1.DataSource = xTable;
this.DropDownList1.DataTextField = "category_id";
this.DropDownList1.DataValueField = "category_Name";
this.DropDownList1.DataBind();
this.DropDownList1.Items.Insert(0, new ListItem("----Select Type ----", "0"));
this.DropDownList1.SelectedValue = "0";
}
protected void Button1_Click(object sender, EventArgs e)
{
string category_id=this.DropDownList1.SelectedValue //你选择的category_id
} <form id="form1" runat="server">
<div>
category:<asp:DropDownList ID="DropDownList1" runat="server">
</asp:DropDownList>
Title:
</div>
Title <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click" />
</form>
this.DropDownList1.DataValueField = "category_Name";
这两个地方写反了 this.DropDownList1.DataTextField = "category_Name";
this.DropDownList1.DataValueField = "category_id";这样才是对的
你一个页面同时要Catergory和New?
晕算了,写吧
con.Open();
SqlTransaction tran = con.BeginTransaction();
string InsertSQL;
InsertSQL = "INSERT INTO news_category (category_name) VALUES (@category_name) SELECT @@IDENTITY AS 'Identity'"; try
{
SqlParameter[] arParams = new SqlParameter[1]; arParams[0] = new SqlParameter("@category_name", 赋值); int category_id = Convert.ToInt32(SqlHelper.ExecuteScalar(tran, CommandType.Text, InsertSQL, arParams)); //获取category_id
//保存New-------begin string sqlLog = "INSERT INTO new (category_id,title ) VALUES (@category_id,@title) SELECT @@IDENTITY AS 'Identity'";
SqlParameter[] LogarParams = new SqlParameter[2];
LogarParams[0] = new SqlParameter("@category_id", category_id);
LogarParams[1] = new SqlParameter("@title", title); // LogarParams[3] = new SqlParameter("@News_id", _ID); int new_id; object o = SqlHelper.ExecuteScalar(tran, CommandType.Text, sqlLog, LogarParams);
if (o != DBNull.Value)
{
new_id = Convert.ToInt32(o);
} tran.Commit();
return new_id;
// MessageBox.Show("Insert successfully");
}
catch (SqlException ex)
{
// rollback the transaction if there are errors
tran.Rollback();
// MessageBox.Show(ex.Message.ToString());
return -1;
}
finally
{
// close the connection
con.Close();
}
new表里我只插入进去news_category表里的category_id!!!也得先给category_name负值吗
然后你再吧这个值插入到new表里就可以了。写个事务解决。SqlTransaction tran = conn.BeginTransaction();string strSql = "insert into news_category(category_name) value('值');select @@identity;"; SqlCommand comm = new SqlCommand();
comm.CommandText = strSql;
comm.Connection = conn;
conn.Open();
int cateid = (int)comm.ExecuteScalar();然后接着做插入到new表的操作
--添加同步
create trigger tir_Add on news_category
for insert
as
declare @id int
select @id=category_id from INSERTED
insert into new values(@id,'title插入的时候为空')
---删除同步---
create trigger tir_del on news_category
for delete
as
declare @id int
select @id=category_id from Deleted
delete from new where category_id =@id至于代码的话,那就是事务处理了,插入一个表的同时插入另一个表。。代码 上面的人都贴出来了。触发器实现起来简单些,不过大项目应该慎用。。对触发器过分的依赖,势必影响数据库的结构,同时增加了维护的复杂程序.
这个就是sql2005上面用的啊。实在不懂网上查查资料吧。帖子里面很多正确答案的。你自己试试。
Use Master
GO
Create Table news_category
(
UID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Category_Name NVARCHAR(256) NOT NULL
)GOCreate Table New
(
Category_ID INT NOT NULL,
News_ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
Title NVARCHAR(128) NOT NULL
)Declare @Category_ID INT
Insert into news_category(Category_Name) values('Sports')
Set @Category_ID=@@IDENTITY
Print '@Category_ID='+CAST(@Category_ID AS NVARCHAR(20))Insert into New(Category_ID,Title) values(@Category_ID,'NBA')
GOSelect A.Category_Name,B.News_ID,B.Title
From News_category A inner join New B On A.UID=B.Category_ID
Order by A.Category_Name,B.TitleGODrop table News_category
Drop table New