Code:
CREATE TABLE [AUTO PROBA] (
[AUTO ID] [int] IDENTITY (2, 5) NOT NULL ,
[NAME] [varchar] (30) NOT NULL ,
CONSTRAINT [PK_AUTO] PRIMARY KEY ([AUTO ID])
)
CREATE TABLE [AUTO PROBA] (
[AUTO ID] [int] IDENTITY (2, 5) NOT NULL ,
[NAME] [varchar] (30) NOT NULL ,
CONSTRAINT [PK_AUTO] PRIMARY KEY ([AUTO ID])
)
Sledeci kod funkcionise i vraca zadnji generisani IDENTITY:
Code:
SqlConnection con = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=TEST;Connect Timeout=10");
con.Open();
SqlCommand cmd1 = con.CreateCommand();
cmd1.CommandText = "insert into [AUTO PROBA](NAME) values('1')";
int result1 = cmd1.ExecuteNonQuery();
SqlCommand cmd2 = con.CreateCommand();
cmd2.CommandText = "select SCOPE_IDENTITY()";
int result2 = Convert.ToInt32(cmd2.ExecuteScalar());
cmd1.Dispose();
cmd2.Dispose();
con.Dispose();
MessageBox.Show("result1: " + result1 + Environment.NewLine + "result2: " + result2);
SqlConnection con = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=TEST;Connect Timeout=10");
con.Open();
SqlCommand cmd1 = con.CreateCommand();
cmd1.CommandText = "insert into [AUTO PROBA](NAME) values('1')";
int result1 = cmd1.ExecuteNonQuery();
SqlCommand cmd2 = con.CreateCommand();
cmd2.CommandText = "select SCOPE_IDENTITY()";
int result2 = Convert.ToInt32(cmd2.ExecuteScalar());
cmd1.Dispose();
cmd2.Dispose();
con.Dispose();
MessageBox.Show("result1: " + result1 + Environment.NewLine + "result2: " + result2);
Medjutim ako umesto:
Code:
cmd1.CommandText = "insert into [AUTO PROBA](NAME) values('1')";
cmd1.CommandText = "insert into [AUTO PROBA](NAME) values('1')";
komandu napisem preko parametara:
Code:
cmd1.CommandText = "insert into [AUTO PROBA](NAME) values(@p1)";
cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = "1";
cmd1.CommandText = "insert into [AUTO PROBA](NAME) values(@p1)";
cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = "1";
Insert prodje ali SCOPE_IDENTITY ne funkcionise.
Ne razumem u cemu je problem.
Ako spojimo sve u jednu komandu onda moze:
Code:
SqlConnection con = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=TEST;Connect Timeout=10");
con.Open();
SqlCommand cmd1 = con.CreateCommand();
cmd1.CommandText = "insert into [AUTO PROBA](NAME) values(@p1); select SCOPE_IDENTITY();";
cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = "1";
int result1 = Convert.ToInt32(cmd1.ExecuteScalar());
cmd1.Dispose();
con.Dispose();
MessageBox.Show("result1: " + result1);
SqlConnection con = new SqlConnection("Data Source=(local);Integrated Security=SSPI;Initial Catalog=TEST;Connect Timeout=10");
con.Open();
SqlCommand cmd1 = con.CreateCommand();
cmd1.CommandText = "insert into [AUTO PROBA](NAME) values(@p1); select SCOPE_IDENTITY();";
cmd1.Parameters.Add("@p1", SqlDbType.VarChar).Value = "1";
int result1 = Convert.ToInt32(cmd1.ExecuteScalar());
cmd1.Dispose();
con.Dispose();
MessageBox.Show("result1: " + result1);
Zanima me zasto nece da rade komande odvojeno ako se u prvoj komandi korsite parametri? Konekcija nije zatvorena pre izvrsenja SCOPE_IDENTITY() funkcije, trebalo bi da vrati zadnji IDENTITY medjutim vraca NULL.