C# Database issue

Question

Could somebody tell me why this isn't adding the values to the database. The form runs fine and doesn't return any errors.

    private void button1_Click(object sender, EventArgs e)
    {
        SqlConnection connection = new SqlConnection();
        SqlCommand command = new SqlCommand();

        connection.ConnectionString = (@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\John\Documents\Setup.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");

        command.Parameters.AddWithValue("@userName", textBox1.Text);
        command.Parameters.AddWithValue("@passWord", textBox2.Text);
        command.CommandText = "INSERT INTO Setup (userName, password) VALUES(@userName, @passWord)";



        try
        {
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            // handle exception
        }
        finally
        {
            connection.Close();
        }


    }

FYI: I'm a "newbie" My database is called Setup. I've manually added a table called myTable with 2 columns of userName and another one called password both set at nchar(50)

Tip - it's better (and shorter) to use using() {} for example - using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Do work here; connection closed on following line. } — sinelaw
– sinelaw, Commented Apr 27, 2012 at 18:29

LarsTech · Accepted Answer · 2012-04-27 18:56:05Z

6

You need to specify the Table, not the database (which gets used in the connection string). Added the schema prefix to the table name:

command.CommandText = "INSERT INTO dbo.myTable (userName, password) VALUES (@userName, @passWord)";

And add:

command.Connection = connection;

to associate your Command object with the connection object.

edited Apr 27, 2012 at 18:56

answered Apr 27, 2012 at 18:29

LarsTech

81.9k14 gold badges161 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

user1353517 Over a year ago

This still doesn't work unfortunately. Just so i'm thinking correctly, once i've ran the form and closed it when I click 'show table data' the newly added values should be there.

LarsTech Over a year ago

@user1353517 The "posted" code should work. Make sure your button click event is wired up. When you click on button1, does it run the code?

LarsTech Over a year ago

@user1353517 Missed that. You need to associate the command object with the connection. Updated answer.

user1353517 Over a year ago

thanks, I've added it but now I get the erro 'Invalid object name 'myTable'.' on line 'int rowsAffected = command.ExecuteNonQuery();'

LarsTech Over a year ago

@user1353517 Then you don't have a table called myTable in your database. Double check the database you are referencing in the connection string.

|

user756519 · Accepted Answer · 2012-04-27 18:46:45Z

1

Your code should look something like this:

Set the connection object.
Specify the table name as @LarsTech has mentioned.
It is a best practice to use two part notation when specifying table names like [Schema name].[Table Name]. So, you have to specify your table name like dbo.MyTable

Code snippet:

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = (@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\John\Documents\Setup.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;");

    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = "INSERT INTO dbo.MyTable  (userName, password) VALUES (@userName, @passWord)";
    command.Parameters.AddWithValue("@userName", textBox1.Text);
    command.Parameters.AddWithValue("@passWord", textBox2.Text);

    try
    {
        connection.Open();
        int rowsAffected = command.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        //handle exception
    }
    finally
    {
        connection.Close();
    }
}

answered Apr 27, 2012 at 18:46

user756519

1 Comment

user1353517 Over a year ago

Thanks, but it still doesn't add the values to the database :(

Mark Brackett · Accepted Answer · 2012-04-27 18:59:36Z

1

The form runs fine and doesn't return any errors.

That's probably because you're swallowing them. Get rid of (or log) your catch (Exception ex).

In general, the .NET BCL is well-designed - if a method isn't going to work, you will get an exception.

[Now] I have the error 'ExecuteNonQuery: Connection property has not been initialized.'

Right. You need to pass the SqlConnection to the SqlCommand:

SqlCommand command = new SqlCommand();
command.Connection = connection;

edited Apr 27, 2012 at 18:59

answered Apr 27, 2012 at 18:37

Mark Brackett

85.9k17 gold badges113 silver badges156 bronze badges

3 Comments

user1353517 Over a year ago

Okay, now I have the error 'ExecuteNonQuery: Connection property has not been initialized.' on line int 'rowsAffected = command.ExecuteNonQuery();' Any Idea?

user1353517 Over a year ago

Added it an I get the error 'the best overloaded method match for 'system.data.sqlclient.sqlcommand.sqlcommand(string)' has some invalid arguments

Mark Brackett Over a year ago

@user1353517 - Oops. Can't pass the connection to ctor. Set the SqlCommand.Connection property instead

Collectives™ on Stack Overflow

C# Database issue

3 Answers 3

14 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

14 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related