-1

I would like to get last selected person ID.

string personID = "SELECT PersonID FROM TestDatabase.[dbo].[Persons] where name LIKE 'XYZ%'";

SqlCommand cmd = new SqlCommand(personID, con);
SqlDataReader reader = cmd.ExecuteReader();


var lastSelectedSingleClientPhoneId = reader.GetDecimal(0);

But unfortunately it did not work. I already tried to get int16, int32 and int64. When i use INSERT I can get the ID using the following select:

SELECT SCOPE_IDENTITY();

Insert command below:

string insertPerson = "INSERT INTO TestDatabase.[dbo].[Persons] (firstName,secondName) VALUES (@firstName,@secondName);SELECT SCOPE_IDENTITY();"; 
SqlCommand cmd = new SqlCommand(insertPerson, con);
cmd.Parameters.AddWithValue("@firstName", txt_firstName.Text);
cmd.Parameters.AddWithValue("@secondName", txt_secondName.Text);
SqlDataReader reader = cmd.ExecuteReader(); 
reader.Read(); 
var lastInsertedPersontId = reader.GetDecimal(0);'
MethodMan
  • 18,625
  • 6
  • 34
  • 52
ctrlaltdlt
  • 9
  • 3
  • Can you clarify what you mean by 'it did not work'? Does it throw? – cristobalito Dec 14 '15 at 23:21
  • Wait, are you trying to get the ID of a record you just inserted? That's not how you do that, use an [`OUTPUT` clause](http://stackoverflow.com/questions/42648/). – Dour High Arch Dec 14 '15 at 23:22
  • can you show your `INSERT` statement.. you can do the Insert command and the Select `SCOPE_IDENTITY()` all in one statement for example `"INSERT INTO Properties (PropertyName,PropertyAddress,PropertyCity,PropertyState,PropertyZip,PropertyNotes)" +" values(@Name,@Address,@City,@State,@Zip,@Notes)SELECT CAST(scope_identity() AS int)";` – MethodMan Dec 14 '15 at 23:22
  • @cristobalito "Invalid attempt to read when no data is present" - But this query return corectly personID. – ctrlaltdlt Dec 14 '15 at 23:24
  • you need to show your Insert command why are you posting partial code.. also doing a Select is not how you get the `@@SCOPE_IDENTITY` – MethodMan Dec 14 '15 at 23:26
  • Does the code referenced here (http://www.dotnetperls.com/sqlconnection) help? This question also seems to suggest it might - http://stackoverflow.com/questions/1147615/invalid-attempt-to-read-when-no-data-is-present – cristobalito Dec 14 '15 at 23:28
  • @MethodMan `string insertPerson = "INSERT INTO TestDatabase.[dbo].[Persons] (firstName,secondName) VALUES (@firstName,@secondName);SELECT SCOPE_IDENTITY();"; SqlCommand cmd = new SqlCommand(insertPerson, con); cmd.Parameters.AddWithValue("@firstName", txt_firstName.Text); cmd.Parameters.AddWithValue("@secondName", txt_secondName.Text); SqlDataReader reader = cmd.ExecuteReader(); reader.Read(); var lastInsertedPersontId = reader.GetDecimal(0);` – ctrlaltdlt Dec 14 '15 at 23:30
  • @ctrlaltdlt - can you put the code \ query into the question instead of the edits? – cristobalito Dec 14 '15 at 23:30
  • also `reader.GetDecimal(0)` is the PersonID in your table a Int32 or a Decimal – MethodMan Dec 14 '15 at 23:31
  • @MethodMan In INSERT query i get the Decimal. – ctrlaltdlt Dec 14 '15 at 23:34
  • put your reader.Read() in a while loop also you need ExecuteScalar if you are returning 1 row. – MethodMan Dec 14 '15 at 23:36
  • 1
    you need to use output params as well take a look at some of these examples and refactor your code.. http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id – MethodMan Dec 14 '15 at 23:38
  • Now the question is confused - the first part talks about getting the result of a select while the second part seems to want the result of the insert? It sounds like you want the second - can you clarify the question? – cristobalito Dec 14 '15 at 23:40
  • I'm trying to make changes. @cristobalito I just want get the result of a select. In second part i just give example, beacuse when I use INSERT query i know how to get lastInstertPersonID. – ctrlaltdlt Dec 14 '15 at 23:49

2 Answers2

0

This should return all Persons beginning with 'XYZ' in the Persons database table.

string personID = "SELECT PersonID FROM TestDatabase.[dbo].[Persons] where name LIKE 'XYZ%'";

using(var cmd = new SqlCommand(personID, con))
using (var reader = cmd.ExecuteReader())
{
    while(reader.Read())
    {
        Console.WriteLine(reader.GetValue(0));
    }
}

I appreciate you're looking for a type to call GetXXX, but you can easily extend this pseudo-code to determine the appropriate type.

cristobalito
  • 4,192
  • 1
  • 29
  • 41
  • You have written similar code that I wrote in the question. It does not work. Loop cause that does not throw exception. – ctrlaltdlt Dec 15 '15 at 00:36
0

You can try combining the two SQL statements as one CommandText value then use ExecuteScalar to return the last inserted ID

string insertPerson = "INSERT INTO TestDatabase.[dbo].[Persons] (firstName,secondName) VALUES (@firstName,@secondName); SELECT SCOPE_IDENTITY();"; 
SqlCommand cmd = new SqlCommand(insertPerson, con);
cmd.Parameters.AddWithValue("@firstName", txt_firstName.Text);
cmd.Parameters.AddWithValue("@secondName", txt_secondName.Text);
int personID = (int)cmd.ExecuteScalar();
/// ... convert integer value to a string or do something

You can also try this SQL statement as an alternative:

SELECT TOP 1 PersonID FROM Persons ORDER BY PersonID DESC;
abramlimpin
  • 5,027
  • 11
  • 58
  • 97