CODEWITHSUNDEEP
c#sql-server-2008stored-procedures
zen_1991
zen_1991

Reputation: 629

Inserting with an Auto-Incrementing ID

I am inserting into a database using a stored procedure and i am getting the error:

Procedure or function 'sp_Addrecord' expects parameter '@RecordNumber', which was not supplied.

RecordNumber is an auto incrementing ID so i understand id have to omit it from my insert command and specify which columns and where i have to insert to avoid this but i am calling the procedure which is handled by another class so where would i be able to specify this as you would normally say something like this: 

SqlCommand cmd = new SqlCommand("INSERT INTO CARS (carDate, carTime) Values (@Date, @Time)", conDatabase);

Here is my code, i avoided the using statement for simplicity of this example:

List<CarRecord> carRecords;

private void Save_Record_Click(object sender, RoutedEventArgs e)
    {
        SqlConnection conDatabase = new SqlConnection(String.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", SQLFunctions.connectSQL.SQLSERVER_ID, SQLFunctions.connectSQL.SQLDatabaseName, SQLFunctions.connectSQL.SQLServerLoginName, SQLFunctions.connectSQL.SQLServerPassword));
        conDatabase.Open();
        SqlCommand cmd = new SqlCommand("sp_Addrecord", conDatabase);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
        conDatabase.Close();
    }

public bool Addrecord(CarRecord DataRecord)
    {
        return ExecuteNonQuery("sp_Addrecord", null,
                CreateParameter("@Date", SqlDbType.NVarChar, DataRecord.carDate),
                CreateParameter("@Time", SqlDbType.NVarChar, DataRecord.carTime),
        );
    }

EDIT - Stored Procedure:

USE [SDC Logging]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Addrecord] 

@RecordNumber   int,
@Date   nvarchar(50),
@Time   nvarchar(50),
AS
BEGIN
SET NOCOUNT ON;

WITH [source](RecordNumber, Date, Time)
 AS 
(
    SELECT @RecordNumber, @Date, @Time,
)
MERGE dbo.Bags AS [target] USING [source]
ON [target].Date = [source].Date
WHEN MATCHED THEN UPDATE SET 

        [target].Date = @Date, 
        [target].Time = @Time, 

WHEN NOT MATCHED THEN
    INSERT ( Date, Time, )
    VALUES( @Date, @Time, );

    SELECT SCOPE_IDENTITY()
END

Upvotes: 0

Views: 106

Answers (2)

Paresh Damani
Paresh Damani

Reputation: 1

Try This.

You don't need to call separate method Addrecord.

However, you still want to use a separate method. Add code below in the AddRecord method and remove existing code:

SqlParameter []parms = new SqlParameter[1];
parms[0] = new SqlParameter("@Date",DataRecord.carDate) ;
parms[1] = new SqlParameter("@Time",DataRecord.carTime) ;
cmd.Parameters.AddRange(parms);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
conDatabase.Close();

Upvotes: 0

Leo
Leo

Reputation: 14820

The error says it all. Your sp_Addrecord has a parameter specified that you are supplying. Basically, the parameters you specify here...

return ExecuteNonQuery("sp_Addrecord", null,
                CreateParameter("@Date", SqlDbType.NVarChar, DataRecord.carDate),
                CreateParameter("@Time", SqlDbType.NVarChar, DataRecord.carTime),
        );

must match the name and datatype of the parameters defined by sp_Addrecord stored procedure. In addition, make sure your stored procedure's query matches this query...

INSERT INTO CARS (carDate, carTime) Values (@Date, @Time)

Edit based on your Edit

You need to specified the @RecordNumber parameter here...

return ExecuteNonQuery("sp_Addrecord", null,
                CreateParameter("@RecordNumber", SqlDbType.Int, DataRecord.recordNumber),
                CreateParameter("@Date", SqlDbType.NVarChar, DataRecord.carDate),
                CreateParameter("@Time", SqlDbType.NVarChar, DataRecord.carTime),
        );

Don't worry about the insert just make sure that when inserting you pass a "invalid record number" such as -1, if the MERGE statement doesn't find the record with id of -1 it will successfully insert the record with an auto-generated Id with the help of your identity column

Upvotes: 1

Related Questions