有一News表,id为主键,自动增长,新增记录后想获取新增记录的id,无法获取,代码如下 SqlConnection Conn = new SqlConnection(strconn) ;
Conn.Open();
string sql = "insert into News(Title,NewsFrom,NewsContents,UpdateTime) values(@Title,@NewsFrom,@NewsContents,@UpdateTime)";
SqlCommand cmd = new SqlCommand(sql,Conn);
cmd.Parameters.Add(new SqlParameter("@Title",SqlDbType.NVarChar,255));
cmd.Parameters.Add(new SqlParameter("@NewsFrom",SqlDbType.NVarChar,50));
cmd.Parameters.Add(new SqlParameter("@NewsContents",SqlDbType.NText));
cmd.Parameters.Add(new SqlParameter("@UpdateTime",SqlDbType.DateTime,8));
cmd.Parameters["@Title"].Value=StrTitle;
cmd.Parameters["@NewsFrom"].Value=StrNewsFrom;
cmd.Parameters["@NewsContents"].Value=StrContents;
cmd.Parameters["@UpdateTime"].Value=DateTime.Now;
cmd.ExecuteNonQuery();
//获取新增记录ID
sql="select SCOPE_IDENTITY() as ID from News";
SqlCommand cmdsel = new SqlCommand(sql,Conn);
SqlDataReader NewsDr = cmdsel.ExecuteReader();
if(NewsDr.Read())
{
Response.Redirect("News_Detail.aspx?ID="+NewsDr["ID"].ToString());
}
Conn.Close();
Conn.Open();
string sql = "insert into News(Title,NewsFrom,NewsContents,UpdateTime) values(@Title,@NewsFrom,@NewsContents,@UpdateTime)";
SqlCommand cmd = new SqlCommand(sql,Conn);
cmd.Parameters.Add(new SqlParameter("@Title",SqlDbType.NVarChar,255));
cmd.Parameters.Add(new SqlParameter("@NewsFrom",SqlDbType.NVarChar,50));
cmd.Parameters.Add(new SqlParameter("@NewsContents",SqlDbType.NText));
cmd.Parameters.Add(new SqlParameter("@UpdateTime",SqlDbType.DateTime,8));
cmd.Parameters["@Title"].Value=StrTitle;
cmd.Parameters["@NewsFrom"].Value=StrNewsFrom;
cmd.Parameters["@NewsContents"].Value=StrContents;
cmd.Parameters["@UpdateTime"].Value=DateTime.Now;
cmd.ExecuteNonQuery();
//获取新增记录ID
sql="select SCOPE_IDENTITY() as ID from News";
SqlCommand cmdsel = new SqlCommand(sql,Conn);
SqlDataReader NewsDr = cmdsel.ExecuteReader();
if(NewsDr.Read())
{
Response.Redirect("News_Detail.aspx?ID="+NewsDr["ID"].ToString());
}
Conn.Close();
SELECT @@identity
就行了,不用另查
Conn.Open();
string sql = "insert into News(Title,NewsFrom,NewsContents,UpdateTime) values(@Title,@NewsFrom,@NewsContents,@UpdateTime);SELECT @Identity=SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sql,Conn);
cmd.Parameters.Add(new SqlParameter("@Title",SqlDbType.NVarChar,255));
cmd.Parameters.Add(new SqlParameter("@NewsFrom",SqlDbType.NVarChar,50));
cmd.Parameters.Add(new SqlParameter("@NewsContents",SqlDbType.NText));
cmd.Parameters.Add(new SqlParameter("@UpdateTime",SqlDbType.DateTime,8));
SqlParameter identity=new SqlParameter("@Identity",SqlDbType.Int);
identity.Direction = ParameterDirection.Output;
cmd.Parameters.Add(identity);
cmd.Parameters["@Title"].Value=StrTitle;
cmd.Parameters["@NewsFrom"].Value=StrNewsFrom;
cmd.Parameters["@NewsContents"].Value=StrContents;
cmd.Parameters["@UpdateTime"].Value=DateTime.Now;
int iden=int.Parse(cmd.ExecuteReader().toString());
Conn.Open();
string sql = "insert into News(Title,NewsFrom,NewsContents,UpdateTime) values(@Title,@NewsFrom,@NewsContents,@UpdateTime) SELECT @@Identity";
SqlCommand cmd = new SqlCommand(sql,Conn);
cmd.Parameters.Add(new SqlParameter("@Title",SqlDbType.NVarChar,255));
cmd.Parameters.Add(new SqlParameter("@NewsFrom",SqlDbType.NVarChar,50));
cmd.Parameters.Add(new SqlParameter("@NewsContents",SqlDbType.NText));
cmd.Parameters.Add(new SqlParameter("@UpdateTime",SqlDbType.DateTime,8));
SqlParameter identity=new SqlParameter("@Identity",SqlDbType.Int);
cmd.Parameters["@Title"].Value=StrTitle;
cmd.Parameters["@NewsFrom"].Value=StrNewsFrom;
cmd.Parameters["@NewsContents"].Value=StrContents;
cmd.Parameters["@UpdateTime"].Value=DateTime.Now;
int Id=Convert.ToInt32(cmd.ExecuteScalar());试试
或把添加放在存储过程,在过程里
select max(id) from news 返回数据
SELECT MAX(ID) from News 这样多用户同时并发操作怎么办。不推荐这样做。
declare @table table (id int identity(1,1),mcontent nvarchar(200))insert into @table (mcontent)
select 'tt' union all
select 'yy'
select * from @tableselect @@identity 'newid'insert into @table (mcontent)
select 'mm'
select @@identity 'newid'
lz运行一下就看明白了
insert into testid (data) values ('aaa') select @@identity
就会返回当前插入行的自动编号的ID。
Conn.Open();
string sql = "insert into News(Title,NewsFrom,NewsContents,UpdateTime) values(@Title,@NewsFrom,@NewsContents,@UpdateTime);SELECT @Identity=SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(sql,Conn);
cmd.Parameters.Add(new SqlParameter("@Title",SqlDbType.NVarChar,255));
cmd.Parameters.Add(new SqlParameter("@NewsFrom",SqlDbType.NVarChar,50));
cmd.Parameters.Add(new SqlParameter("@NewsContents",SqlDbType.NText));
cmd.Parameters.Add(new SqlParameter("@UpdateTime",SqlDbType.DateTime,8));
SqlParameter identity=new SqlParameter("@Identity",SqlDbType.Int);
identity.Direction = ParameterDirection.Output;
cmd.Parameters.Add(identity);
cmd.Parameters["@Title"].Value=StrTitle;
cmd.Parameters["@NewsFrom"].Value=StrNewsFrom;
cmd.Parameters["@NewsContents"].Value=StrContents;
cmd.Parameters["@UpdateTime"].Value=DateTime.Now;
int iden=int.Parse(cmd.ExecuteReader().toString());
sql="select Ident_Current('News') ";
SqlCommand cmdsel = new SqlCommand(sql,Conn);
SqlDataReader NewsDr = cmdsel.ExecuteReader();