Must declare scalar variable error ADO.NET

Question

So, I have my update and its giving me this error on a string. I already tried using the concatenation way without params, I've tryed changing the params order and making the verification for null, still its returning errors.

For the ones asking. The error its in the title "Must declare the scalar variable "@Apellido"."

This is the entire code:

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    OleDbCommand command = new OleDbCommand();
    OleDbTransaction transaction = null;

    // Set the Connection to the new OleDbConnection.
    command.Connection = connection;

    // Open the connection and execute the transaction. 
    try
    {
        connection.Open();
        command.CommandText = "UPDATE Asociados SET Apellido=@Apellido, Nombre=@Nombre, FechaNacimiento=@FechaNacimiento, Comentarios=@Comentarios, DNI=@DNI, ParcelaID=@ParcelaID, FechaIngreso=@FechaIngreso, Nacionalidad=@Nacionalidad, EstadoCivil=@EstadoCivil, Profesion=@Profesion, DomicilioComercial=@DomicilioComercial, TelefonoComercial=@TelefonoComercial, Celular=@Celular, Email=@Email, Patente1=@Patente1, Patente2=@Patente2, Patente3=@Patente3, Domicilio=@Domicilio, Telefono=@Telefono, Localidad=@Localidad, CodigoPostal=@CodigoPostal, Referente=@Referente, Saldo=@Saldo, FechaBaja=@FechaBaja WHERE ID=@ID";
        command.Parameters.AddWithValue("@ID", ((object)AsociadoModificado.ID) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Apellido", AsociadoModificado.Apellido.ToString());
        command.Parameters.AddWithValue("@Nombre",AsociadoModificado.Nombre.ToString());
        command.Parameters.AddWithValue("@FechaNacimiento",Convert.ToDateTime(AsociadoModificado.FechaNacimiento).Date);
        command.Parameters.AddWithValue("@Comentarios", AsociadoModificado.Comentarios.ToString());
        command.Parameters.AddWithValue("@DNI", ((object)AsociadoModificado.DNI) ?? DBNull.Value);
        command.Parameters.AddWithValue("@ParcelaID", ((object)AsociadoModificado.ParcelaID) ?? DBNull.Value);
        command.Parameters.AddWithValue("@FechaIngreso", ((object)Convert.ToDateTime(AsociadoModificado.FechaIngreso).Date) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Nacionalidad", ((object)AsociadoModificado.Nacionalidad) ?? DBNull.Value);
        command.Parameters.AddWithValue("@EstadoCivil", ((object)AsociadoModificado.EstadoCivil) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Profesion", ((object)AsociadoModificado.Profesion) ?? DBNull.Value);
        command.Parameters.AddWithValue("@DomicilioComercial", ((object)AsociadoModificado.DomicilioComercial) ?? DBNull.Value);
        command.Parameters.AddWithValue("@TelefonoComercial", ((object)AsociadoModificado.TelefonoComercial) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Celular", ((object)AsociadoModificado.Celular) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Email", ((object)AsociadoModificado.Email) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Patente1", ((object)AsociadoModificado.Patente1) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Patente2", ((object)AsociadoModificado.Patente2) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Patente3", ((object)AsociadoModificado.Patente3) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Domicilio", ((object)AsociadoModificado.Domicilio) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Telefono", ((object)AsociadoModificado.Telefono) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Localidad", ((object)AsociadoModificado.Localidad) ?? DBNull.Value);
        command.Parameters.AddWithValue("@CodigoPostal", ((object)AsociadoModificado.CodigoPostal) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Referente", ((object)AsociadoModificado.Referente) ?? DBNull.Value);
        command.Parameters.AddWithValue("@Saldo", ((object)AsociadoModificado.Saldo) ?? DBNull.Value);
        command.Parameters.AddWithValue("@FechaBaja", ((object)AsociadoModificado.FechaBaja) ?? DBNull.Value);
        command.ExecuteNonQuery();
        connection.Close();
    }
    catch(Exception e)
    {
    }
}

Providing the error text would be a good start. tooth.Pull(1). — Sam Axe, Aug 07 '15 at 01:18
@SamAxe as you can guess the error message its the title. "Must declare scalar variable %/Apellido/% :) — Ariel, Aug 07 '15 at 02:44
Narrow it down, simplify to one parameter query and test it again. — zed, Aug 07 '15 at 02:53
I hope you copy/pasted that error text because you apparently have trouble figuring out what you typed. Take a look at your title again. Anyhow, the most common cause of this error is a misspelling (thus copy/paste is important here) either in the SQL or the Parameters.AddWithValue calls. — Sam Axe, Aug 07 '15 at 02:54
I have copy pasted, its not the misspelling (checked with the DB, the query and the command) its SQL 2008 . I had misspelling errors before in the past 8 years developing .net both web and winforms but never something I could not fix, this seems like a error in which the data its being parsed between the update in .net and the update in SQL since if I replace the query with values and do it in the Managment updates without problems. — Ariel, Aug 07 '15 at 03:05
Is it possible that AsociadoModificado.Apellido is null ? I'd need to check and see what happens if you try .Tostring() on null property... — andrew, Aug 07 '15 at 03:12
Andrew, I've tested the line with the cast to object and the comparison to DBnull, its not null in not 1 single situation. Yet it keeps giving me the error, the only thing I can see when debugging its that the value of apellido has double quotes instead of literal quotes (db quotes) that might or might not be casted on the excecution. — Ariel, Aug 07 '15 at 03:14

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

See StackOverflow Order counts, you have ID last in your CommandText and first in your parameter assignment.

edited May 23 '17 at 11:57

Community

1
1

answered Aug 07 '15 at 01:39

JBrooks

9,901
2
28
32

Yep, just changed it for testing. Should not matter since Im declaring the "@varname" still, no matter the order I cannot make it work, I've tryed in the query order, in the DB order and in no particular order. Nothing works. – Ariel Aug 07 '15 at 02:45

score 0 · Accepted Answer · edited May 23 '17 at 11:57

0

You're using OleDbCommand like it was a SqlCommand. Named parameters (eg: @Apellido) are used in SqlCommand. For an OleDbCommand, you must use positional placeholders.

Check this out:

Also, it has been asked here several times. :)

edited May 23 '17 at 11:57

Community

1
1

answered Aug 07 '15 at 03:15

Andre Calil

7,652
34
41

And thats why the error must be in the posts so the indexer can found it. Now it works oddly enought since the select works with named parameters. Thanks Andre. – Ariel Aug 07 '15 at 03:22

Must declare scalar variable error ADO.NET

2 Answers2