Geek Answers Handbook

Ado.net ExecuteScalar () returns null

I am executing a stored procedure in C # (via vs2008) using ado.net using the ExecuteScalar command. The stored proc returns the key of the new record, but ExecuteScalar returns null. I look in the database and the record is really added. I can use the output parameter to get the value, but then I will not know why this did not work.

When I run sp in ssms, pkey is returned.

What am I doing wrong?

Here is the C # code:

public int SaveNewPerson(EPerson ePerson) { int newPersonPkey; SqlConnection cn = new SqlConnection(cnn.PersonData); using (cn) { try { SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "People.dbo.AddNewPerson"; cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 150).Value = ePerson.LastName; cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 150).Value = ePerson.FirstName; cn.Open(); object result = cmd.ExecuteScalar(); newPersonPkey = int.Parse(result.ToString()); cn.Close(); } catch (Exception e) { // call error method throw new Exception(e.Message + " save new Person error "); } } return newPersonPkey; }

And this is sp:

  PROCEDURE [dbo].[AddNewPerson] @FirstName varchar(50) ,@LastName varchar(50) AS BEGIN SET NOCOUNT ON; INSERT INTO [People].[dbo].[Persons] ( [FirstName] ,[LastName] ) VALUES ( @FirstName ,@LastName ) declare @persons_PKey int set @persons_PKey = @@IDENTITY return @persons_PKey end
+4
source share
2 answers

The ExecuteScalar method returns the first field of the first record of the result, but since your query does not produce a result, it returns null.

You can either select a value instead of returning it from a stored procedure, or add a parameter with the direction set to ParameterDirection.ReturnValue to catch what the stored procedure returns.

+8
source

Try changing the stored procedure to use Select Statement to return an identifier instead of using the following form:

 SELECT CAST(scope_identity() AS int)

Thus, changing your procedure to:

  PROCEDURE [dbo].[AddNewPerson] @FirstName varchar(50) ,@LastName varchar(50) AS BEGIN SET NOCOUNT ON; INSERT INTO [People].[dbo].[Persons] ( [FirstName] ,[LastName] ) VALUES ( @FirstName ,@LastName ) SELECT CAST(scope_identity() AS int) end

MSDN's ExecuteScalar () documentation says that it will return the first column of the first row in the result set, or null otherwise if the result set is empty.

+2
source

More articles:


All Articles