CODEWITHSUNDEEP
c#sql-server
joey.e
joey.e

Reputation: 23

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

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

Upvotes: 2

Views: 3586

Answers (3)

Prashant Pimpale
Prashant Pimpale

Reputation: 10697

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

    }

Upvotes: -1

marc_s
marc_s

Reputation: 754220

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

Upvotes: 3

AaronHolland
AaronHolland

Reputation: 1675

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

Upvotes: 1

Related Questions