Why is select scope_identity() returning 1 for SQL query?

Question

I am using SQL query to with SELECT SCOPE_IDENTITY() in sqlcommand. here is my code:

 SqlCommand cmd = new SqlCommand("INSERT INTO tbl_Supplier(Supplier_Name,Supplier_Address, Supplier_PhoneNo,Supplier_City,Supplier_Remarks) VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','"+DropDownList1.SelectedItem+"','"+TextBox4.Text+"') RETURN SCOPE_IDENTITY()", conn);
            var id = cmd.ExecuteScalar();
            conn.Close();

but the code is always returning 1?

Your code doesn't have `select scope_identity()` in it, it has `return scope_identity()` in it. `select` and `return` do different things. Please fix your question to say which one you're using. — , May 22 '16 at 14:02

score 4 · Accepted Answer · edited May 23 '17 at 12:31

4

You are using the wrong syntax to get that info.

"...; SELECT SCOPE_IDENTITY()"

(Notice also the semicolon before the SELECT and after the end of the first sql statement)

At this point the ExecuteScalar is able to get the first column of the first row returned by the SELECT

Said that, please take a bit of your time to learn how to execute "Parameterized Queries" your code is very weak and an easy target for Sql Injection

string cmdText = @"INSERT INTO tbl_Supplier 
                  (Supplier_Name,
                   Supplier_Address, 
                   Supplier_PhoneNo,
                   Supplier_City,
                   Supplier_Remarks) 
                  VALUES(@name, @address, @phone, @city, @remarks);
                  SELECT SCOPE_IDENTITY()"

 using(SqlCommand cmd = new SqlCommand(cmdText, connection))
 {
     connection.Open();
     cmd.Parameters.Add("@name", SqlDbType.NVarWChar).Value = TextBox1.Text;
     cmd.Parameters.Add("@address", SqlDbType.NVarWChar).Value = TextBox2.Text;
     cmd.Parameters.Add("@phone", SqlDbType.NVarWChar).Value = TextBox3.Text;
     cmd.Parameters.Add("@city", SqlDbType.NVarWChar).Value = DropDownList1.SelectedItem
     cmd.Parameters.Add("@remarks", SqlDbType.NVarWChar).Value = TextBox4.Text;

      var id = cmd.ExecuteScalar();
 }
 conn.Close();

edited May 23 '17 at 12:31

Community

1
1

answered May 22 '16 at 14:02

Steve

213,761
22
232
286

...VALUES('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','"+DropDownList1.SelectedItem+"','"+TextBox4.Text+"' ; SELECT SCOPE_IDENTITY()", conn); – Iqra May 22 '16 at 14:11
should i use this way? – Iqra May 22 '16 at 14:12
For the SCOPE_IDENTITY part, yes. For the first statement, you should use a parameterized query. – Steve May 22 '16 at 14:12
Please explain. Did you get any error message? Did you try to insert at least two records consecutively without stopping the debug session? What else is the problem? – Steve May 22 '16 at 14:20
Yes i was inserting two records at a time. but it almost solved the issue. Just the problem is the **var id** is not accessible outside the current block. and i can't declare it before pageload. – Iqra May 22 '16 at 14:44

Why is select scope_identity() returning 1 for SQL query?

1 Answers1