Must declare the scalar variable - why won't my parameter work?

Question

I am using the old SqlClient to insert some data with a loop. I have added all the parameters correctly but I get an exception thrown that says

"Must declare the scalar variable \"@KeyProject\"."

string query = @"INSERT INTO dbo.ImportedProjects 
                        (
                            KeyProject, 
                            KeyCompany, 
                            KeyCountry, 
                            KeyCustomer, 
                            KeyEmployeeProjectManager, 
                            KeyEmployeeProjectOwner, 
                            KeyOrganisation, 
                            ProjectNumber, 
                            ProjectName, 
                            ProjectOwnerNumber, 
                            ProjectManagerNumber, 
                            ProjectOwnerName, 
                            ProjectManagerName, 
                            ProjectOwnerInitials, 
                            ProjectManagerInitials, 
                            CustomerNumber, 
                            CustomerName, 
                            CreatedDate, 
                            ProjectStatus, 
                            ProjectOpenClosed
                        ) VALUES (
                            @KeyProject, 
                            @KeyCompany, 
                            @KeyCountry, 
                            @KeyCustomer, 
                            @KeyEmployeeProjectManager, 
                            @KeyEmployeeProjectOwner, 
                            @KeyOrganisation, 
                            @ProjectNumber, 
                            @ProjectName, 
                            @ProjectOwnerNumber, 
                            @ProjectManagerNumber, 
                            @ProjectOwnerName, 
                            @ProjectManagerName, 
                            @ProjectOwnerInitials, 
                            @ProjectManagerInitials, 
                            @CustomerNumber, 
                            @CustomerName, 
                            @CreatedDate, 
                            @ProjectStatus, 
                            @ProjectOpenClosed
                        )";

        SqlConnection sqlCon = new SqlConnection("Server=localhost;Database=DatabaseName;Trusted_Connection=True;");
        SqlCommand idInsertCms = new SqlCommand("SET IDENTITY_INSERT dbo.ImportedProjects ON", sqlCon);
        SqlCommand sqlCmd= new SqlCommand(query, sqlCon);

        try
        {
            sqlCon.Open();

            idInsertCms.ExecuteNonQuery();

            foreach (var item in importedProjects)
            {
                sqlCmd.Parameters.Add("@KeyProject", SqlDbType.Int);
                sqlCmd.Parameters["@KeyProject"].Value = item.KeyProject;
                sqlCmd.Parameters.Add("@KeyCompany", SqlDbType.Int);
                sqlCmd.Parameters["@KeyCompany"].Value = item.KeyCompany;
                sqlCmd.Parameters.Add("@KeyCountry", SqlDbType.Int);
                sqlCmd.Parameters["@KeyCountry"].Value = item.KeyCountry;
                sqlCmd.Parameters.Add("@KeyCustomer", SqlDbType.Int);
                sqlCmd.Parameters["@KeyCustomer"].Value = item.KeyCustomer;
                sqlCmd.Parameters.Add("@KeyEmployeeProjectManager", SqlDbType.Int);
                sqlCmd.Parameters["@KeyEmployeeProjectManager"].Value = item.KeyEmployeeProjectManager;
                sqlCmd.Parameters.Add("@KeyEmployeeProjectOwner", SqlDbType.Int);
                sqlCmd.Parameters["@KeyEmployeeProjectOwner"].Value = item.KeyEmployeeProjectOwner;
                sqlCmd.Parameters.Add("@KeyOrganisation", SqlDbType.Int);
                sqlCmd.Parameters["@KeyOrganisation"].Value = item.KeyOrganisation;
                sqlCmd.Parameters.Add("@ProjectNumber", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectNumber"].Value = item.ProjectNumber;
                sqlCmd.Parameters.Add("@ProjectName", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectName"].Value = item.ProjectName;
                sqlCmd.Parameters.Add("@ProjectOwnerNumber", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectOwnerNumber"].Value = item.ProjectOwnerNumber;
                sqlCmd.Parameters.Add("@ProjectManagerNumber", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectManagerNumber"].Value = item.ProjectManagerNumber;
                sqlCmd.Parameters.Add("@ProjectOwnerName", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectOwnerName"].Value = item.ProjectOwnerName;
                sqlCmd.Parameters.Add("@ProjectManagerName", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectManagerName"].Value = item.ProjectManagerName;
                sqlCmd.Parameters.Add("@ProjectOwnerInitials", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectOwnerInitials"].Value = item.ProjectOwnerInitials;
                sqlCmd.Parameters.Add("@ProjectManagerInitials", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectManagerInitials"].Value = item.ProjectManagerInitials;
                sqlCmd.Parameters.Add("@CustomerNumber", SqlDbType.VarChar);
                sqlCmd.Parameters["@CustomerNumber"].Value = item.CustomerNumber;
                sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar);
                sqlCmd.Parameters["@CustomerName"].Value = item.CustomerName;
                sqlCmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
                sqlCmd.Parameters["@CreatedDate"].Value = item.CreatedDate;
                sqlCmd.Parameters.Add("@ProjectStatus", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectStatus"].Value = item.ProjectStatus;
                sqlCmd.Parameters.Add("@ProjectOpenClosed", SqlDbType.VarChar);
                sqlCmd.Parameters["@ProjectOpenClosed"].Value = item.ProjectOpenClosed;
                sqlCmd.Parameters.Clear();

                sqlCmd.ExecuteNonQuery();
            }

            idInsertCms.CommandText = "SET IDENTITY_INSERT dbo.ImportedProjects OFF";
            idInsertCms.ExecuteNonQuery();

What's wrong with my code?

Answer 1

Instead of inserting in a loop, you can use a different approach, try creating a table and add rows to this table with your values, once you finishing adding your rows to this table, you can send this table as parameter to your stored procedure, in SQL Server create a user-defined table with the same columns as your table created in the loop and use this type as type in the parameter, after this you can do an insert into your table using a select

var newDataRow = medicareBuyInBuyInDataTable.NewRow();

foreach (var value in values) {
    newDataRow["FieldName"] = value;
    ....
    ....
}

yourDataTable.Rows.Add(newDataRow);

// send your table as parameter
 var parameter = new List<KeyValuePair<string, object>>{
                new KeyValuePair<string, object>("@yourTableParam", medicareBuyInDataTable)
            };
 return DbAccess.ExecuteScalar(ConString, StoredProcedureName, parameter);

In SQL Server create a type with the same values in the table you are sending:

-- ================================
-- Create User-defined Table Type
-- ================================
USE <database_name, sysname, AdventureWorks>
GO

-- Create the data type
CREATE TYPE YourTableTypeName AS TABLE 
(
    <columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>, 
    <column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>, 
    <column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>, 
    PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

Finally, in the stored procedure, use the type you created "YourTableTypeName"

CREATE PROCEDURE [dbo].[YourProcedureName]
    @yourTableParam [dbo].[YourTableTypeName] READONLY
AS
BEGIN
    INSERT INTO [dbo].[YourTable]
        SELECT * 
        FROM yourTableParam 
END

Answer 2

How about you create a new SqlCommand for each iteration like this:

string query = @"INSERT INTO dbo.ImportedProjects 
                    (
                        KeyProject, 
                        KeyCompany, 
                        KeyCountry, 
                        KeyCustomer, 
                        KeyEmployeeProjectManager, 
                        KeyEmployeeProjectOwner, 
                        KeyOrganisation, 
                        ProjectNumber, 
                        ProjectName, 
                        ProjectOwnerNumber, 
                        ProjectManagerNumber, 
                        ProjectOwnerName, 
                        ProjectManagerName, 
                        ProjectOwnerInitials, 
                        ProjectManagerInitials, 
                        CustomerNumber, 
                        CustomerName, 
                        CreatedDate, 
                        ProjectStatus, 
                        ProjectOpenClosed
                    ) VALUES (
                        @KeyProject, 
                        @KeyCompany, 
                        @KeyCountry, 
                        @KeyCustomer, 
                        @KeyEmployeeProjectManager, 
                        @KeyEmployeeProjectOwner, 
                        @KeyOrganisation, 
                        @ProjectNumber, 
                        @ProjectName, 
                        @ProjectOwnerNumber, 
                        @ProjectManagerNumber, 
                        @ProjectOwnerName, 
                        @ProjectManagerName, 
                        @ProjectOwnerInitials, 
                        @ProjectManagerInitials, 
                        @CustomerNumber, 
                        @CustomerName, 
                        @CreatedDate, 
                        @ProjectStatus, 
                        @ProjectOpenClosed
                    )";

    SqlConnection sqlCon = new SqlConnection("Server=localhost;Database=DatabaseName;Trusted_Connection=True;");
    SqlCommand idInsertCms = new SqlCommand("SET IDENTITY_INSERT dbo.ImportedProjects ON", sqlCon);


    try
    {
        sqlCon.Open();

        idInsertCms.ExecuteNonQuery();

        foreach (var item in importedProjects)
        {
            SqlCommand sqlCmd= new SqlCommand(query, sqlCon);
            sqlCmd.Parameters.Add("@KeyProject", SqlDbType.Int);
            sqlCmd.Parameters["@KeyProject"].Value = item.KeyProject;
            sqlCmd.Parameters.Add("@KeyCompany", SqlDbType.Int);
            sqlCmd.Parameters["@KeyCompany"].Value = item.KeyCompany;
            sqlCmd.Parameters.Add("@KeyCountry", SqlDbType.Int);
            sqlCmd.Parameters["@KeyCountry"].Value = item.KeyCountry;
            sqlCmd.Parameters.Add("@KeyCustomer", SqlDbType.Int);
            sqlCmd.Parameters["@KeyCustomer"].Value = item.KeyCustomer;
            sqlCmd.Parameters.Add("@KeyEmployeeProjectManager", SqlDbType.Int);
            sqlCmd.Parameters["@KeyEmployeeProjectManager"].Value = item.KeyEmployeeProjectManager;
            sqlCmd.Parameters.Add("@KeyEmployeeProjectOwner", SqlDbType.Int);
            sqlCmd.Parameters["@KeyEmployeeProjectOwner"].Value = item.KeyEmployeeProjectOwner;
            sqlCmd.Parameters.Add("@KeyOrganisation", SqlDbType.Int);
            sqlCmd.Parameters["@KeyOrganisation"].Value = item.KeyOrganisation;
            sqlCmd.Parameters.Add("@ProjectNumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectNumber"].Value = item.ProjectNumber;
            sqlCmd.Parameters.Add("@ProjectName", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectName"].Value = item.ProjectName;
            sqlCmd.Parameters.Add("@ProjectOwnerNumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectOwnerNumber"].Value = item.ProjectOwnerNumber;
            sqlCmd.Parameters.Add("@ProjectManagerNumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectManagerNumber"].Value = item.ProjectManagerNumber;
            sqlCmd.Parameters.Add("@ProjectOwnerName", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectOwnerName"].Value = item.ProjectOwnerName;
            sqlCmd.Parameters.Add("@ProjectManagerName", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectManagerName"].Value = item.ProjectManagerName;
            sqlCmd.Parameters.Add("@ProjectOwnerInitials", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectOwnerInitials"].Value = item.ProjectOwnerInitials;
            sqlCmd.Parameters.Add("@ProjectManagerInitials", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectManagerInitials"].Value = item.ProjectManagerInitials;
            sqlCmd.Parameters.Add("@CustomerNumber", SqlDbType.VarChar);
            sqlCmd.Parameters["@CustomerNumber"].Value = item.CustomerNumber;
            sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar);
            sqlCmd.Parameters["@CustomerName"].Value = item.CustomerName;
            sqlCmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
            sqlCmd.Parameters["@CreatedDate"].Value = item.CreatedDate;
            sqlCmd.Parameters.Add("@ProjectStatus", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectStatus"].Value = item.ProjectStatus;
            sqlCmd.Parameters.Add("@ProjectOpenClosed", SqlDbType.VarChar);
            sqlCmd.Parameters["@ProjectOpenClosed"].Value = item.ProjectOpenClosed;

            sqlCmd.ExecuteNonQuery();
        }

        idInsertCms.CommandText = "SET IDENTITY_INSERT dbo.ImportedProjects OFF";
        idInsertCms.ExecuteNonQuery();

SqlCommand sqlCmd= new SqlCommand(query, sqlCon);
sqlCmd.Parameters.Add("@KeyProject", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCompany", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCountry", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCustomer", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyEmployeeProjectManager", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyEmployeeProjectOwner", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyOrganisation", SqlDbType.Int);
sqlCmd.Parameters.Add("@ProjectNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerInitials", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerInitials", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CustomerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
sqlCmd.Parameters.Add("@ProjectStatus", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOpenClosed", SqlDbType.VarChar);

foreach (var item in importedProjects)
{
    sqlCmd.Parameters["@KeyProject"].Value = item.KeyProject;
    sqlCmd.Parameters["@KeyCompany"].Value = item.KeyCompany;
    sqlCmd.Parameters["@KeyCountry"].Value = item.KeyCountry;
    sqlCmd.Parameters["@KeyCustomer"].Value = item.KeyCustomer;
    sqlCmd.Parameters["@KeyEmployeeProjectManager"].Value = item.KeyEmployeeProjectManager;
    sqlCmd.Parameters["@KeyEmployeeProjectOwner"].Value = item.KeyEmployeeProjectOwner;
    sqlCmd.Parameters["@KeyOrganisation"].Value = item.KeyOrganisation;
    sqlCmd.Parameters["@ProjectNumber"].Value = item.ProjectNumber;
    sqlCmd.Parameters["@ProjectName"].Value = item.ProjectName;
    sqlCmd.Parameters["@ProjectOwnerNumber"].Value = item.ProjectOwnerNumber;
    sqlCmd.Parameters["@ProjectManagerNumber"].Value = item.ProjectManagerNumber;
    sqlCmd.Parameters["@ProjectOwnerName"].Value = item.ProjectOwnerName;
    sqlCmd.Parameters["@ProjectManagerName"].Value = item.ProjectManagerName;
    sqlCmd.Parameters["@ProjectOwnerInitials"].Value = item.ProjectOwnerInitials;
    sqlCmd.Parameters["@ProjectManagerInitials"].Value = item.ProjectManagerInitials;
    sqlCmd.Parameters["@CustomerNumber"].Value = item.CustomerNumber;
    sqlCmd.Parameters["@CustomerName"].Value = item.CustomerName;
    sqlCmd.Parameters["@CreatedDate"].Value = item.CreatedDate;
    sqlCmd.Parameters["@ProjectStatus"].Value = item.ProjectStatus;
    sqlCmd.Parameters["@ProjectOpenClosed"].Value = item.ProjectOpenClosed;

    sqlCmd.ExecuteNonQuery();
}

or you could recycle sqlCmd and setup the parameters outside the loop and then just set them within the loop:

SqlCommand sqlCmd= new SqlCommand(query, sqlCon);
sqlCmd.Parameters.Add("@KeyProject", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCompany", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCountry", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyCustomer", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyEmployeeProjectManager", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyEmployeeProjectOwner", SqlDbType.Int);
sqlCmd.Parameters.Add("@KeyOrganisation", SqlDbType.Int);
sqlCmd.Parameters.Add("@ProjectNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOwnerInitials", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectManagerInitials", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CustomerNumber", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CustomerName", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime);
sqlCmd.Parameters.Add("@ProjectStatus", SqlDbType.VarChar);
sqlCmd.Parameters.Add("@ProjectOpenClosed", SqlDbType.VarChar);

foreach (var item in importedProjects)
{
    sqlCmd.Parameters["@KeyProject"].Value = item.KeyProject;
    sqlCmd.Parameters["@KeyCompany"].Value = item.KeyCompany;
    sqlCmd.Parameters["@KeyCountry"].Value = item.KeyCountry;
    sqlCmd.Parameters["@KeyCustomer"].Value = item.KeyCustomer;
    sqlCmd.Parameters["@KeyEmployeeProjectManager"].Value = item.KeyEmployeeProjectManager;
    sqlCmd.Parameters["@KeyEmployeeProjectOwner"].Value = item.KeyEmployeeProjectOwner;
    sqlCmd.Parameters["@KeyOrganisation"].Value = item.KeyOrganisation;
    sqlCmd.Parameters["@ProjectNumber"].Value = item.ProjectNumber;
    sqlCmd.Parameters["@ProjectName"].Value = item.ProjectName;
    sqlCmd.Parameters["@ProjectOwnerNumber"].Value = item.ProjectOwnerNumber;
    sqlCmd.Parameters["@ProjectManagerNumber"].Value = item.ProjectManagerNumber;
    sqlCmd.Parameters["@ProjectOwnerName"].Value = item.ProjectOwnerName;
    sqlCmd.Parameters["@ProjectManagerName"].Value = item.ProjectManagerName;
    sqlCmd.Parameters["@ProjectOwnerInitials"].Value = item.ProjectOwnerInitials;
    sqlCmd.Parameters["@ProjectManagerInitials"].Value = item.ProjectManagerInitials;
    sqlCmd.Parameters["@CustomerNumber"].Value = item.CustomerNumber;
    sqlCmd.Parameters["@CustomerName"].Value = item.CustomerName;
    sqlCmd.Parameters["@CreatedDate"].Value = item.CreatedDate;
    sqlCmd.Parameters["@ProjectStatus"].Value = item.ProjectStatus;
    sqlCmd.Parameters["@ProjectOpenClosed"].Value = item.ProjectOpenClosed;

    sqlCmd.ExecuteNonQuery();
}