Dave Burke - Assignment with SQLHelper.ExecuteScalar() using a StoredProcedure in a single line. No can do.

Assignment with SQLHelper.ExecuteScalar() using a StoredProcedure in a single line. No can do.

I desperately wanted to assign an integer using Sqlhelper.ExecuteScaler() with a StoredProcedure IN A SINGLE LINE, but I threw in the towel after giving it a good college try. As in:

int k = int.Parse(SQLHelper.ExecuteScalar(DataHelper.Connection(), "p_insert_list_schools", new object[] { "test", 1}).ToString());

I couldn't escape the Object reference not set runtime.

I ended up using:

SqlParameter[] p =
{
SQLHelper.MakeInParam("@school",SqlDbType.VarChar,100, txtSchool.Text,
SQLHelper.MakeOutParam("@schid",SqlDbType.Int,4)
};
SQLHelper.ExecuteScalar(DataHelper.Connection(), CommandType.StoredProcedure, "p_insert_list_schools", p)
int k = int.Parse(p[2].Value.ToString());

I googled and found no evidence of anyone else being able to do this. Doable using a SQL query, but not a stored procedure. If anyone has the secret on this, please let me know!

Or the problem could be with my SQL procedure...

------------------------------------------------------------------------------------------------------------------------
-- Date Created: Wednesday, March 31, 2004
-- Created By: Generated by CodeSmith
------------------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE dbo.p_insert_list_schools
@school varchar(100),
@schid int output
AS

set nocount on

INSERT INTO [dbo].[list_schools] (
[school]
) VALUES (
@school
)

Select @schid = @@Identity