How to insert data into a database table using a SqlCommand

Question

I am trying to insert data into a database that I have that has a table called EmployeeInfo

The user is prompted to enter a last name and select a department ID (displayed to the user as either marketing or development) The column ID automatically increments.

Here is my Code behind

    protected void SubmitEmployee_Click(object sender, EventArgs e)
{        
    var submittedEmployeeName = TextBox1.Text;
    var submittedDepartment = selectEmployeeDepartment.Text;
    if (submittedEmployeeName == "")
    {
        nameError.Text = "*Last name cannot be blank";

    }
    else
    {

        System.Data.SqlClient.SqlConnection sqlConnection1 =
        new System.Data.SqlClient.SqlConnection("ConnString");

        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = "INSERT INTO EmployeeInfo (LastName, DepartmentID ) VALUES ('" + submittedEmployeeName + "', " + submittedDepartment + ")";
        cmd.Connection = sqlConnection1;

        sqlConnection1.Open();
        cmd.ExecuteNonQuery();
        sqlConnection1.Close();

    }
}

The error I'm recieving is 'Arguement exception was unhandled by user code'

Here is a picture of it.

enter image description here

As requested. More details

errorDetails

PLease show the complete stack trace. Click on "View Details..." — OldProgrammer
– OldProgrammer, Commented Dec 18, 2014 at 17:43
Ooh, let me be the first to say, "Don't use inline SQL as it leaves you vulnerable to SQL injection attacks." :-) But seriously, use parameterized queries instead. Also, it looks like you're trying to insert the selected department's ID, but you'll need the selected VALUE for that....not the TEXT. — Matt M
– Matt M, Commented Dec 18, 2014 at 17:50
the error is with your connection string. not the insert statement. — ethorn10
– ethorn10, Commented Dec 18, 2014 at 17:50

JakobMiller · Accepted Answer · 2014-12-18 17:52:35Z

If I had enough reputation, I would rather post this as a reply, but it might actually be the solution.

The reason why it stops there is because you are not providing a legit SqlConnection, since your input is: "ConnString", which is just that text.

The connection string should look something like:

const string MyConnectionString = "SERVER=localhost;DATABASE=DbName;UID=userID;PWD=userPW;"

Which in your case should end up like:

System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection(MyConnectionString);

Besides that, you should build your connections like following:

using (SqlConnection con = new SqlConnection(MyConnectionString)) {

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = xxxxxx; // Your query to the database
    cmd.Connection = con;
    cmd.Connection.Open();
    cmd.ExecuteNonQuery();
}

}

This will do the closing for you and it also makes it easier for you to nestle connections. I did a project recently and did the connection your way, which ended up not working when I wanted to do more than one execute in one function. Just important to make a new command for each execute.

Collectives™ on Stack Overflow

How to insert data into a database table using a SqlCommand

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related