CODEWITHSUNDEEP
c#sqlrelational-database
user3707078
user3707078

Reputation:

Inserting values into a table in C#

I am trying to add a user to two different tables, running from SQL Server Management Studio Express, the details add for the one session only, please could people help with an answer to how i would go about making this data last in the database after the session (I apologize for poor code, I am currently only an apprentice software engineer)

namespace AddingANewUser
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void AddNewUser(String employeeID, String firstName, String lastName, String username, String password, String managerID, String accessLevelID, String phoneNumber, String departmentID)
    {
        using (var connection = new SqlConnection(Properties.Settings.Default.CitySecretHRSystemConnectionString))
        {
            connection.Open();

            using (var updateEmployees = connection.CreateCommand())
            {
                updateEmployees.CommandText = "INSERT INTO Employees (EmployeeID, FirstName, LastName, PhoneNumber, AccessLevelID, ManagerID, DepartmentID) VALUES (@employeeID, @firstName, @lastName, @phoneNumber, @ accessLevelID, @managerID, @departmentID)";

                updateEmployees.Parameters.AddWithValue("@firstName", firstName);
                updateEmployees.Parameters.AddWithValue("@lastName", lastName);
                updateEmployees.Parameters.AddWithValue("@employeeID", employeeID);
                updateEmployees.Parameters.AddWithValue("@phoneNumber", phoneNumber);
                updateEmployees.Parameters.AddWithValue("@accessLevelID",   accessLevelID);
                updateEmployees.Parameters.AddWithValue("@managerID", managerID);
                updateEmployees.Parameters.AddWithValue("@departmentID", departmentID);
            }

            using (var updateLogIn = connection.CreateCommand())
            {
                updateLogIn.CommandText = "INSERT INTO LogInDetails (EmployeeID, Username, KeyWord) VALUES (@employeeID, @username, @keyWord)";

                updateLogIn.Parameters.AddWithValue("@employeeID", employeeID);
                updateLogIn.Parameters.AddWithValue("@username", username);
                updateLogIn.Parameters.AddWithValue("@keyWord", password);
            }
        }
    }

    private void btnSubmit_Click(object sender, EventArgs e)
    {
        try
        {
            AddNewUser(tbEmployeeID.Text, tbFirstName.Text, tbLastName.Text, tbUsername.Text, tbPassword.Text, tbManagerID.Text, tbAccessLevel.Text, tbPhoneNumber.Text, tbDeptID.Text);
        }
        catch (Exception err)
        {
            MessageBox.Show(err.Message);
        }
    }
}

Upvotes: 2

Views: 195

Answers (3)

apomene
apomene

Reputation: 14389

I guess you are missing the execute command:

using (var updateEmployees = connection.CreateCommand())
{
    //...
    //... 
    updateEmployees.ExecuteNonQuery();
    //...
}

As other people correctly point out have this in mind:

The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called.

In simple words no need to call connection.Close() as using will take care of it and close the connection when you are done.

Upvotes: 5

Dhaval Patel
Dhaval Patel

Reputation: 7591

you have to use below menioned code 

 using (var connection = new SqlConnection(Properties.Settings.Default.CitySecretHRSystemConnectionString))
    {
        if (connection.State == ConnectionState.Closed)
            connection.Open();

        using (var updateEmployees = connection.CreateCommand())
        {
            updateEmployees.CommandText = "INSERT INTO Employees (EmployeeID, FirstName, LastName, PhoneNumber, AccessLevelID, ManagerID, DepartmentID) VALUES (@employeeID, @firstName, @lastName, @phoneNumber, @ accessLevelID, @managerID, @departmentID)";

            updateEmployees.Parameters.AddWithValue("@firstName", firstName);
            updateEmployees.Parameters.AddWithValue("@lastName", lastName);
            updateEmployees.Parameters.AddWithValue("@employeeID", employeeID);
            updateEmployees.Parameters.AddWithValue("@phoneNumber", phoneNumber);
            updateEmployees.Parameters.AddWithValue("@accessLevel", accessLevel);
            updateEmployees.Parameters.AddWithValue("@managerID", managerID);
            updateEmployees.Parameters.AddWithValue("@departmentID", departmentID);
            updateEmployees.ExecuteNonQuery();
        }


        using (var updateLogIn = connection.CreateCommand())
        {
            updateLogIn.CommandText = "INSERT INTO LogInDetails (EmployeeID, Username, KeyWord) VALUES (@employeeID, @username, @keyWord)";

            updateLogIn.Parameters.AddWithValue("@employeeID", employeeID);
            updateLogIn.Parameters.AddWithValue("@username", username);
            updateLogIn.Parameters.AddWithValue("@keyWord", password);
            updateLogIn.ExecuteNonQuery();
        }
    }

you have already implemeted using block so no need to close the connection

Upvotes: 3

Kabulan0lak
Kabulan0lak

Reputation: 2136

You have written @phoneNumber, @ accessLevelID, @managerID, : No space between @ and accessLevel. Also, once you call accessLevel and other time accessLevelID, I think accessLevel doesn't have ID ...

Upvotes: -1

Related Questions