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();
    }
}

There is no need to create a new account every time you ask a question - stackoverflow.com/questions/51034532/… . — mjwills
– mjwills, Commented Jun 26, 2018 at 5:08
Possible duplicate of What is passing parameters to SQL and why do I need it? — mjwills
– mjwills, Commented Jun 26, 2018 at 5:28

marc_s · Accepted Answer · 2018-06-26 04:37:58Z

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();
    }
}

AaronHolland · Accepted Answer · 2018-06-26 04:30:23Z

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, 2018 at 4:30

AaronHolland

1,6951 gold badge19 silver badges32 bronze badges

Comments

Prashant Pimpale · Accepted Answer · 2018-06-27 02:45:39Z

-1

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();

    }

edited Jun 27, 2018 at 2:45

answered Jun 26, 2018 at 4:52

Prashant Pimpale

10.7k10 gold badges52 silver badges88 bronze badges

3 Comments

Hoppeduppeanut Over a year ago

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 Over a year ago

Actually scrap that, in its current state it won't even compile because there's no variable with the name con.

Prashant Pimpale Over a year ago

Yes that was typo mistake. Changed it! I also vote to delete it.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related