Must Declare Scalar variable C# & SQL Server

Question

I have only just started using SQL Server in C# but I am getting an error

Must Declare Scalar Variable

when it tries to run. Obviously I have read other posts where this was the problem and I have tried to fix it using the information from the posts, however none were successful.

public static void AddUser(ProgressBar progressBar1, string FirstName, string LastName, string CurrentWorkingGrade, string PredictedGrade, string year)
{
    try
    {
        ///Increases the ID Key
        int IDVal = 7;

        ///Uses Connection String from DB to form local connection
        SqlConnection Connect = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename = C:\Users\Chris\Documents\LogInFormMK2\LogInFormMK2\Students.mdf; Integrated Security = True");

        ///Opens Connection
        Connect.Open();

        ///Initialises the AddRecord 
        SqlCommand AddRecord = new SqlCommand();

        ///Sets all variables passed from main to a parameters to avoid SQl injection
        AddRecord.Parameters.AddWithValue("@FN", FirstName);
        AddRecord.Parameters.AddWithValue("@LN", LastName);
        AddRecord.Parameters.AddWithValue("@CWG", CurrentWorkingGrade);
        AddRecord.Parameters.AddWithValue("@PG", PredictedGrade);
        AddRecord.Parameters.AddWithValue("@YR", year);

        ///Creates the query
        AddRecord = new SqlCommand("INSERT INTO dbo.StudentINFO (Id, FirstName, LastName, DateOfBirth, CurrentWorkingGrade, PredictedGrade, Year) VALUES (IDVal, + @FN, + @LN, '2001/05/21', + @CWG, + @PG, + @YR)");

        AddRecord.Connection = Connect;

        AddRecord.ExecuteNonQuery();

        MessageBox.Show("Student added succesfully");

        IDVal = IDVal + 1;
    }
    catch(Exception ex)
    {
        MessageBox.Show("Sorry there was an error adding this student. Error Code" + ex);
    }
}

Joel Coehoorn · Accepted Answer · 2018-02-26 15:55:13Z

4

You do this:

SqlCommand AddRecord = new SqlCommand();

Then you do this to declare and set your parameters:
_{(and btw: the AddWithValue() method is not the best way to use parameters)}

///Sets all variables passed from main to a parameters to avoid SQl injection
AddRecord.Parameters.AddWithValue("@FN", FirstName);
AddRecord.Parameters.AddWithValue("@LN", LastName);
AddRecord.Parameters.AddWithValue("@CWG", CurrentWorkingGrade);
AddRecord.Parameters.AddWithValue("@PG", PredictedGrade);
AddRecord.Parameters.AddWithValue("@YR", year);

Then you do this again:

AddRecord = new SqlCommand(...);

Note the new keyword... you have replaced your SqlCommand object with an entirely new object. This new object does not have any parameters declared.

You can fix this either by setting the CommandText property instead of re-creating the whole object, or by moving that line up above the parameter code, to combine it with the earlier variable declaration.

While I'm here, you should also think about what happens if an exception is thrown while executing your sql statement. In short, program execution will jump straight to the catch block and never call Connect.Close()... which you fail to do anyway. You should wrap your SqlConnection object in a using block to make sure the connection is closed properly, even if an exception is thrown.

edited Feb 26, 2018 at 15:55

answered Feb 23, 2018 at 22:51

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ChrisD Over a year ago

Thank you, what would be a better way to add a parameter? and why would it be better? Sorry im really new to SQL haha

Joel Coehoorn Over a year ago

blogs.msmvps.com/jcoehoorn/blog/2014/05/12/…

FaizanHussainRabbani · Accepted Answer · 2018-02-23 22:58:58Z

This:

AddRecord = new SqlCommand("INSERT INTO dbo.StudentINFO (Id, FirstName, LastName, DateOfBirth, CurrentWorkingGrade, PredictedGrade, Year) VALUES (IDVal, + @FN, + @LN, '2001/05/21', + @CWG, + @PG, + @YR)");

Should come before this:

AddRecord.Parameters.AddWithValue("@FN", FirstName);
AddRecord.Parameters.AddWithValue("@LN", LastName);
AddRecord.Parameters.AddWithValue("@CWG", CurrentWorkingGrade);
AddRecord.Parameters.AddWithValue("@PG", PredictedGrade);
AddRecord.Parameters.AddWithValue("@YR", year);

Becoming:

///Creates the query
SqlCommand AddRecord = new SqlCommand("INSERT INTO dbo.StudentINFO (Id, FirstName, LastName, DateOfBirth, CurrentWorkingGrade, PredictedGrade, Year) VALUES (IDVal, + @FN, + @LN, '2001/05/21', + @CWG, + @PG, + @YR)");

///Sets all variables passed from main to a parameters to avoid SQl injection
AddRecord.Parameters.AddWithValue("@FN", FirstName);
AddRecord.Parameters.AddWithValue("@LN", LastName);
AddRecord.Parameters.AddWithValue("@CWG", CurrentWorkingGrade);
AddRecord.Parameters.AddWithValue("@PG", PredictedGrade);
AddRecord.Parameters.AddWithValue("@YR", year);

Collectives™ on Stack Overflow

Must Declare Scalar variable C# & SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related