The parameterized query expects the parameter which was not supplied.

Question

Hi,

Why I am getting this error:

Microsoft.Data.SqlClient.SqlException: The parameterized query '(@person_id nvarchar(36),@device_id nvarchar(36),@prefix nvarcha' expects the parameter '@nickname', which was not supplied.

When I have already supplied @nickname parameter?

This is my stored procedure:

ALTER PROCEDURE [dbo].new_contact
  @person_id AS VARCHAR(255),
  @device_id AS VARCHAR(255),
  @prefix AS VARCHAR(255),
  @display_name AS VARCHAR(255),
  @first_name AS VARCHAR(255),
  @middle_name AS VARCHAR(255),
  @last_name AS VARCHAR(255),
  @nickname AS VARCHAR(255),
  @phonetic_first_name AS VARCHAR(255),
  @phonetic_middle_name AS VARCHAR(255),
  @phonetic_last_name AS VARCHAR(255),
  @suffix AS VARCHAR(255),
  @company AS VARCHAR(255),
  @job_title AS VARCHAR(255),
  @department AS VARCHAR(255),
  @birthday AS VARCHAR(255),
  @anniversary AS VARCHAR(255),
  @notes AS VARCHAR(255)
AS
BEGIN
	IF NOT EXISTS (SELECT person_id FROM contacts WHERE person_id = @person_id)
	BEGIN
		INSERT INTO contact (
			person_id, 
			device_id,
			prefix,
			display_name, 
			first_name, 
			middle_name,
			last_name,
			nickname,
			phonetic_first_name,
			phonetic_middle_name,
			phonetic_last_name,
			suffix,
			company,
			job_title,
			department,
			birthday,
			anniversary,
			notes)
		VALUES (
			@person_id, 
			@device_id,
			@prefix,
			@display_name, 
			@first_name, 
			@middle_name,
			@last_name,
			@nickname,
			@phonetic_first_name,
			@phonetic_middle_name,
			@phonetic_last_name,
			@suffix,
			@company,
			@job_title,
			@department,
			@birthday,
			@anniversary,
			@notes);
	END;
END

and this is my C#:

await Task.Run(async () =>
{
conn = new SQLiteAsyncConnection(App.SqlDB);
var persons = await conn.Table<Persons>().ToListAsync();

var connectionString = "Server=xxxxxxx.database.windows.net;Database=xxxxxx;User Id=xxxxx;Password=xxxxxx;";
var query = "EXEC new_contact @person_id, @device_id, @prefix, @display_name, @first_name, @middle_name, @last_name, @nickname, @phonetic_first_name, @phonetic_middle_name, @phonetic_last_name, @suffix, @company, @job_title, @department, @birthday, @anniversary, @notes";

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (var command = new SqlCommand(query, connection))
    {
        foreach (var person in persons)
        {
            command.Parameters.Clear();

            command.Parameters.AddWithValue("@person_id", person.Id);
            command.Parameters.AddWithValue("@device_id", SecureStorage.GetAsync("DeviceId").Result);
            command.Parameters.AddWithValue("@prefix", person.Prefix);
            command.Parameters.AddWithValue("@display_name", person.DisplayName);
            command.Parameters.AddWithValue("@first_name", person.FirstName);
            command.Parameters.AddWithValue("@middle_name", person.MiddleName);
            command.Parameters.AddWithValue("@last_name", person.LastName);
            command.Parameters.AddWithValue("@nickname", person.Nickname);
            command.Parameters.AddWithValue("@phonetic_first_name", person.PhoneticFirstName);
            command.Parameters.AddWithValue("@phonetic_middle_name", person.PhoneticMiddleName);
            command.Parameters.AddWithValue("@phonetic_last_name", person.PhoneticLastName);
            command.Parameters.AddWithValue("@suffix", person.Suffix);
            command.Parameters.AddWithValue("@company", person.Company);
            command.Parameters.AddWithValue("@job_title", person.JobTitle);
            command.Parameters.AddWithValue("@department", person.Department);
            command.Parameters.AddWithValue("@birthday", person.Birthday);
            command.Parameters.AddWithValue("@anniversary", person.Anniversary);
            command.Parameters.AddWithValue("@notes", person.Notes);
            command.ExecuteNonQuery();
        }
    }
}

Kindly help..

Thanks,

Jassim

Answer 1

Try

command.Parameters.AddWithValue( "nickname", person.Nickname ?? "" );

or

command.Parameters.AddWithValue( "nickname", person.Nickname ?? (object)DBNull.Value );

or (in new C#)

command.Parameters.AddWithValue( "nickname", person.Nickname ).Value ??= DBNull.Value;