CODEWITHSUNDEEP
c#sqlsql-serverado.netoledb
Billy
Billy

Reputation: 183

Must declare a scalar variable (C# and OleDb)

I know this question have been asked several times, but none of answers has helped me resolving this issue.

So, i'm writing data transfer utility, copying data from one table of OleDb database to table of another OleDb database.

I have read all the data from the source database, and i'm trying to write, but always gets this error

Must declare the scalar variable "@CategoryID"

Here's the code

 // generating the insert string below
 string insert = "INSERT INTO Categories VALUES (";
 for(int i = 0; i < cols.Length; i++)
 {
     string coma = ", ";
     if (i == cols.Length - 1)
     coma = " )";

     insert += "@" + cols[i] + coma;
 }

try
{ 
    while (src_reader.Read())         // reading from source database
    {
        dstcmd.CommandText = insert;   

        for (int i = 0; i < cols.Length; i++)
        {
            string temp = "@" + cols[i];     // cols is array of column names
            dstcmd.Parameters.AddWithValue(temp, src_reader[cols[i]]);

            // for debug purposes... below is screenshot of error
            Console.Write(temp + "  " + src_reader[cols[i]] + "\n");
        }

        Console.WriteLine("");

        // point of error
        dstcmd.ExecuteNonQuery();
    }
}
catch(Exception ex)
{
    Console.WriteLine(ex);
}

Here's the screenshot of error.

Error image

CategoryID is the first column of the table and hence the first value that is being inserted.

Any help will be appreciated. If i missed any information or something does not make sense, please do let me know.

Upvotes: 1

Views: 626

Answers (1)

andrews
andrews

Reputation: 2173

Try changing this part:

// generating the insert string below
 string insert = "INSERT INTO Categories VALUES (";
 for(int i = 0; i < cols.Length; i++)
 {
     string coma = ", ";
     if (i == cols.Length - 1)
     coma = " )";

     insert += "@" + cols[i] + coma;
 }

to

// generating the insert string below
 string insert = "INSERT INTO Categories VALUES (";
 for(int i = 0; i < cols.Length; i++)
 {
     string coma = ", ";
     if (i == cols.Length - 1)
     coma = " )";

     insert += "?" + coma;
 }

You don't need to use parameter names in VALUES, but just ? placeholders. However, make sure the order of parameters when you add them matches the order of columns in the table.

Also, it may be better to explicitly specify the column list in the INSERT clause, like:

string insert = "INSERT INTO Categories (Col1, Col2, Col3, etc.)  VALUES (";

See if you want to make that column names list dynamically generated too. But I suggest to get it working for the static column list first and then convert it to dynamic version.

Also, if you don't specify the column name list for INSERT you will have specify values for all columns.

Upvotes: 2

Related Questions