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