How to fix error 'Must declare scalar variable' in SQL query through c#?

Question

I keep getting this annoying error. I really new to SQL and this literally my first query ever and I just cannot get it to work. I have been trying for hours. Please help!

I get the error:

System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@FirstNamez".

Code:

public static void CreateNewEmployee(string FirstNamez, string LastNamez, int Pinz, string Departmentz)
{
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = @"Server = localhost\SQLEXPRESS; Database = Employee; Trusted_Connection = True;";

    SqlCommand command = new SqlCommand();

    using (connection)
    {
        connection.Open();
        string commandtext = "INSERT INTO dbo.EmployeeDatabase (FirstName, LastName, PIN, Department) VALUES (@FirstNamez, @Lastnamez, @Pinz, @Departmentz);";

        command.CommandText = commandtext;
        command.Connection = connection;

        command.ExecuteNonQuery();
    }
}

Possible duplicate of [Must declare scalar variable @Id?](https://stackoverflow.com/questions/39771791/must-declare-scalar-variable-id) — Hoppeduppeanut, Jun 26 '18 at 04:33
There is no need to create a new account every time you ask a question - https://stackoverflow.com/questions/51034532/how-to-insert-column-to-table-in-sql-server-using-c-sharp/51034584 . — mjwills, Jun 26 '18 at 05:08
Possible duplicate of [What is passing parameters to SQL and why do I need it?](https://stackoverflow.com/questions/3216233/what-is-passing-parameters-to-sql-and-why-do-i-need-it) — mjwills, Jun 26 '18 at 05:28

score 3 · Accepted Answer · answered Jun 26 '18 at 04:37

You're defining all your parameters in the query text - but you're never setting their values!

Try this:

public static void CreateNewEmployee(string FirstNamez, string LastNamez, int Pinz, string Departmentz)
{
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = @"Server = localhost\SQLEXPRESS; Database = Employee; Trusted_Connection = True;";

    SqlCommand command = new SqlCommand();

    using (connection)
    {
        connection.Open();
        string commandtext = "INSERT INTO dbo.EmployeeDatabase (FirstName, LastName, PIN, Department) VALUES (@FirstNamez, @Lastnamez, @Pinz, @Departmentz);";

        command.CommandText = commandtext;
        command.Connection = connection;

        // define the parameters and set their values!
        command.Parameters.Add("@FirstNamez", SqlDbType.VarChar, 100).Value = FirstNamez;
        command.Parameters.Add("@LastNamez", SqlDbType.VarChar, 100).Value = LastNamez;
        command.Parameters.Add("@Pinz", SqlDbType.Int).Value = Pinz;
        command.Parameters.Add("@Departmentz", SqlDbType.VarChar, 100).Value = Departmentz;

        command.ExecuteNonQuery();
    }
}

score 1 · Answer 2 · answered Jun 26 '18 at 04:30

1

You aren't specifying the values for the parameters. Check out this article on how to add the parameter values to the command: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx

answered Jun 26 '18 at 04:30

AaronHolland

1,595
1
16
32

Prashant Pimpale · Answer 3 · 2018-06-27T02:45:39.573

Use below code:

Create Stored procedure in your Database instead of the AdHoc query like :

CREATE PROC spAddNewEmployee
@FirstNamez nvarchar(100),
@Lastnamez nvarchar(100),
@Pinz nvarchar(100),
@Departmentz nvarchar(100)

AS

BEGIN
    INSERT INTO dbo.EmployeeDatabase (FirstName, LastName, PIN, Department) 
    VALUES (@FirstNamez, @Lastnamez, @Pinz, @Departmentz)
END

Add in your C# code:

public static void CreateNewEmployee(string FirstNamez, string LastNamez, int Pinz, string Departmentz)
     {
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = @"Server = localhost\SQLEXPRESS; Database = Employee; Trusted_Connection = True;";

        SqlCommand cmd = new SqlCommand("spAddNewEmployee", connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@FirstNamez", FirstNamez);
        cmd.Parameters.AddWithValue("@Lastnamez ", Lastnamez );
        cmd.Parameters.AddWithValue("@Pinz ", Pinz );
        cmd.Parameters.AddWithValue("@Departmentz ", Departmentz );

        connection.Open();
        cmd.ExecuteReader();
        connection.Close();

    }

This code works, but it doesn't answer OP's specific question or provide any context as to *why* it works, and also contains other bits that are irrelevant to OP's problem. Comments about possible SQL injection and code formatting might be better left as a simple comment instead. — Hoppeduppeanut, Jun 26 '18 at 23:26
Actually scrap that, in its current state it won't even compile because there's no variable with the name `con`. — Hoppeduppeanut, Jun 27 '18 at 01:55
Yes that was typo mistake. Changed it! I also vote to delete it. — Prashant Pimpale, Jun 27 '18 at 02:41

How to fix error 'Must declare scalar variable' in SQL query through c#?

3 Answers3