CODEWITHSUNDEEP
c#sql.netauto-increment
Shimrit Revivo
Shimrit Revivo

Reputation: 61

Get auto_increment id from table

I have this code:

string conStr = ConfigurationManager.ConnectionStrings["BackgammonGame"].ConnectionString;
            SqlConnection con = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            con.Open();
            cmd.CommandText = ("INSERT INTO Game (playerA, playerB) OUTPUT INSERTED.gameID VALUES (@playerA, @playerB)");

            cmd.Parameters.Add("@playerA", SqlDbType.NVarChar).Value = firstPlayer;
            cmd.Parameters.Add("@playerB", SqlDbType.NVarChar).Value = secondPlayer;

            cmd.ExecuteNonQuery();

            int id = (int)cmd.ExecuteScalar();
            con.Close();

When I insert into this table, I have an auto_increment int primary key column called gameID, and I declared in the sql statement that I want the gameID in output. my problem is that when I write this line in the code: int id = (int)cmd.ExecuteScalar(); the inserted parameters apear twice in the table (2 rows with the same info.), but when I delete it it's ok.

I need this row so I can use this id in other table.

Upvotes: 3

Views: 3022

Answers (2)

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

Change your command text with this and try

cmd.CommandText = ("INSERT INTO Game (playerA, playerB) VALUES (@playerA,@playerB); 
                     SELECT  SCOPE_IDENTITY()");

SCOPE IDENTITY returns the identity value of last inserted row. Hence that will returns the identity filed of the inserted row using the insert query

EDIT

You are executing the query two times

cmd.ExecuteNonQuery(); // Avoid this
int id = (int)cmd.ExecuteScalar();// This is enough

In both case your query gets executed and it cause insertion twice. ExecuteNonQuery() will execute the insert query and will returns the number of rows affected.

Where as ExecuteScalar() will return the result of the select scope_identity() staement which is the identity column of the inserted row.

Here is your code

 con.Open();
 cmd.CommandText = ("INSERT INTO Game (playerA, playerB) VALUES (@playerA,@playerB); 
                     SELECT  SCOPE_IDENTITY()"); 

 cmd.Parameters.Add("@playerA", SqlDbType.NVarChar).Value = firstPlayer;
 cmd.Parameters.Add("@playerB", SqlDbType.NVarChar).Value = secondPlayer;

 int id = Convert.ToInt32(cmd.ExecuteScalar());
 con.Close();

Upvotes: 2

Ehsan
Ehsan

Reputation: 32681

Modify your command like this

INSERT INTO YourTable(val1, val2, val3 ...) 
VALUES(@val1, @val2, @val3...)
SELECT SCOPE_IDENTITY()

But i personally prefer to write a stored procedure and return the primary key as an output parameter of that sp.

Upvotes: 1

Related Questions