CODEWITHSUNDEEP
c#sqlvisual-studiosqlconnection
Sanad
Sanad

Reputation: 25

"Exception Unhadled" error during SqlConnection open()

In my program I have two text input fields (in windows form) and a button to add/save those values into the DB table. The problem is once I click the button, it does not insert the inputs into the DB, instead it shows the error I have attached below as image.

What is wrong with my program?

My working code: 

public partial class Form1 : Form
{
    //original Connection string is exactly the following:
    //Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename="C:\Users\Sanad Al Nahaj\Documents\thesaurus.mdf";Integrated Security=True;Connect Timeout=30
    SqlConnection conn = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename=C:\Users\Sanad Al Nahaj\Documents\thesaurus.mdf;Integrated Security = True; Connect Timeout = 30");
    public Form1()
    {
        InitializeComponent();
    }

    //Save button
    private void button1_Click(object sender, EventArgs e)
    {
        conn.Open();//error pops up here after clicking the button
        SqlCommand command = conn.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "insert into Table values('"+wordbox.Text+"','"+synonymbox.Text+"')";
        command.ExecuteNonQuery();
        conn.Close();
        MessageBox.Show("Word and Synonym added!");
    }

    private void display_Click(object sender, EventArgs e)
    {
        //implement
    }
}

Error:

enter image description here

Database looks like:

enter image description here

UPDATE: My modification in Using pattern (referring to CDove's answer):

    var command = new SqlCommand();
            using (command = new SqlCommand(
                          "insert into......)
))

Upvotes: 0

Views: 314

Answers (1)

CDove
CDove

Reputation: 1950

There are four things you need to do. First, address this:

"insert into [Table] values('"+wordbox.Text+"','"+synonymbox.Text+"')"

In Microsoft SQL, if I recall correctly, values() syntax on an insert requires explicit declaration of the columns first. Also, "Table" is a reserved word, so you'll need to put it in brackets to use the word as a table name. In the future, avoid using reserved words in your table schemae. 

"insert into [Table] (word, synonym) values ('"+wordbox.Text+"','"+synonymbox.Text+"')"

Second, don't use string concatenation to build a query. Create parameters instead. 

"insert into [Table] (word, synonym) values (@word,@syn)"

And then 

 command.Parameters.AddWithValue("@word", wordbox.Text); 
 command.Parameters.AddWithValue("@syn", synonymbox.Text);
 command.ExecuteNonQuery();

Third, don't cache your connection. That's what this does at the top of your code, leaving you one connection you have to micromanage:

SqlConnection conn = new SqlConnection(@"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename=C:\Users\Sanad Al Nahaj\Documents\thesaurus.mdf;Integrated Security = True; Connect Timeout = 30");

While ideally, you'd read this from web.config or app.config, we'll roll with your hardcoded string; leave it just a string. 

string conn = @"Data Source = (LocalDB)\MSSQLLocalDB; AttachDbFilename=C:\Users\Sanad Al Nahaj\Documents\thesaurus.mdf;Integrated Security = True; Connect Timeout = 30";

Finally, use the using pattern. This is not only less smelly code, but it also includes implicit .Close() and .Dispose() in an implicit try-finally manner.

private void button1_Click(object sender, EventArgs e)
{
    using(var command = new SqlCommand(
             "insert into [Table] (word, synonym) values (@word,@syn)",
              new SqlConnection(conn)
          ))
    {
       command.Connection.Open();//Since we aren't reopening an old connection, errors are less likely.    
       command.CommandType = CommandType.Text;
       command.Parameters.AddWithValue("@word", wordbox.Text); 
       command.Parameters.AddWithValue("@syn", synonymbox.Text);                   

       if(command.ExecuteNonQuery() > 0 )
           MessageBox.Show("Word and Synonym added!");
    }
}

Note that I check the value of ExecuteNonQuery. That's because this function returns the number of rows affected; if the count is 0, the word and synonym weren't added.

Note: This is all off the top of my head on my lunchbreak so test it for yourself, see how it works out for you.

Upvotes: 5

Related Questions