The Stored Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
-- =============================================
-- Author:        <Marcus Le Count>
-- Create date: <22 November 2008>
-- Description:    <Inserts a new record into the Accounts Table>
-- =============================================

ALTER PROCEDURE [dbo].[CreateNewAccount]
    (
    @AccountName nvarchar(50),
    @OpeningBalance money,
    @Balance money,
    @DateCreated datetime,
    @LastUpdate datetime,
    @AccountType int,
    @CreatedBy int
    )
AS
DECLARE @MyIdentity int
BEGIN
    INSERT INTO [accounts]
           ([accountName]
           ,[balance]
           ,[openingBalance]
           ,[dateCreated]
           ,[dateLastUpdate]
           ,[accountType]
           ,[createdBy])
     VALUES
           (@AccountName
            ,@Balance
            ,@OpeningBalance
            ,@DateCreated
            ,@LastUpdate
            ,@AccountType
            ,@CreatedBy)

    SELECT @MyIdentity = SCOPE_IDENTITY()

    SELECT @MyIdentity AS [MyIdentity]

RETURN

END

 

The ASP.NET code

public String CreateNewAccount()
    {
        SqlConnection conn;
        SqlCommand comm;

        string newID;

        //read the connection string from web.config
        string connectionString = ConfigurationManager.ConnectionStrings[
        "DefaultDatabase"].ConnectionString;

        //Initialize connection
        conn = new SqlConnection(connectionString);

        //create command (using stored Procedure)
        comm = new SqlCommand("CreateNewAccount", conn);
        comm.CommandType = CommandType.StoredProcedure;

        //add the command parameters
        comm.Parameters.AddWithValue("@AccountName", System.Data.SqlDbType.NVarChar);
        comm.Parameters["@AccountName"].Value = m_AccountName;
        comm.Parameters.AddWithValue("@OpeningBalance", System.Data.SqlDbType.Money);
        comm.Parameters["@OpeningBalance"].Value = m_OpeningBalance;
        comm.Parameters.AddWithValue("@Balance", System.Data.SqlDbType.Money);
        comm.Parameters["@Balance"].Value = m_Balance;
        comm.Parameters.AddWithValue("@DateCreated", System.Data.SqlDbType.DateTime);
        comm.Parameters["@DateCreated"].Value = m_DateCreated;
        comm.Parameters.AddWithValue("@LastUpdate", System.Data.SqlDbType.DateTime);
        comm.Parameters["@LastUpdate"].Value = m_LastUpdate;
        comm.Parameters.AddWithValue("@AccountType", System.Data.SqlDbType.Int);
        comm.Parameters["@AccountType"].Value = m_AccountType;
        comm.Parameters.AddWithValue("@CreatedBy", System.Data.SqlDbType.Int);
        comm.Parameters["@CreatedBy"].Value = m_CreatedBy;
        try
        {
            //open the connection
            conn.Open();

            //execute the command
            newID = Convert.ToString(comm.ExecuteScalar());
            return newID;
        }
        catch
        {
            return "false";
        }
        finally
        {
            //close the connection
            conn.Close();
            comm.Dispose();
        }

    }