-1

I'm using the code below in the attempt to take text from a textbox to add it to a SQL table called 'Name'. The commented code is the code provided by default as the button's code which I attempted to replace below. The program runs fine however the SQL table isn't updated upon pressing the button nad I was hoping you could provide an insight as to why.

private void nameBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
  //this.Validate();
  //this.nameBindingSource.EndEdit();
  //this.tableAdapterManager.UpdateAll(this.cLIENTDB3DataSet);

  string cmdString = "INSERT INTO Name VALUES (@val1)";

  using (connection = new SqlConnection(connectionString))
  {
    using (SqlCommand comm = new SqlCommand(cmdString,connection))
    {
      connection.Open();
      comm.Connection = connection;
      comm.CommandText = cmdString;
      comm.Parameters.AddWithValue("@val1", nameTextBox.Text);

      comm.ExecuteNonQuery();
    }
  }

}

Thanks!

user2874417
  • 151
  • 1
  • 11
  • No exception? Do you have the database file listed between your project items? If yes what is the value for the property _Copy to the output directory_ – Steve Dec 23 '15 at 23:10
  • 2
    I believe `comm.Connection = connection;` and `comm.CommandText = cmdString;` are both redundant with the SqlCommand constructor you are using. Does the code above cause any exceptions when you click the button? – Arin Taylor Dec 23 '15 at 23:32
  • Did you debug if that code is actually called? Your sql statement should produce an exception if it's executed at all. – René Vogt Dec 23 '15 at 23:50
  • Yeah I've stepped through and every line is executed and nowhere is it telling me that any errors or exceptions were found. – user2874417 Dec 23 '15 at 23:52
  • So if you add _int nrow = command.ExecuteNonQuery():_ what is the value of nrow returned by the command method? – Steve Dec 23 '15 at 23:53
  • wrap `comm.ExecuteNonQuery();` around a try catch also your using is incorrect..`comm.CommandText = cmdString;` is incorrect.. it should be `comm.CommandType = Command.Text` – MethodMan Dec 23 '15 at 23:54
  • Steve the value of nrow in that context is 1 after the line has run. – user2874417 Dec 23 '15 at 23:57
  • Ok Now we know the record has been inserted probably not in the database which you are looking through the Server Explorer window. – Steve Dec 23 '15 at 23:58
  • This answer explains what is probably happening http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails/17147460#17147460 – Steve Dec 23 '15 at 23:58
  • Ok thanks for that link, setting to Copy if newer seems to have no effect and Do not copy throws an exception error: Additional information: An attempt to attach an auto-named database for file .... A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. – user2874417 Dec 24 '15 at 00:08
  • Okay I changed the connection string to the one found in the properties of the .mdf instead of using `ConfigurationManager.ConnectionStrings["DBPRAC3.Properties.Settings.CLIENTDB3ConnectionString"].ConnectionString`, set it to 'copy if newer' and the table seems to be updating! Thanks for the help! – user2874417 Dec 24 '15 at 00:12

1 Answers1

-1

EDIT: You need to choose which column in the table you would like to put the value in your INSERT string. In the edit I added ColName to the insert string.

ORIGINAL POST:

Try Changing CommandText to CommandString

 private void nameBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
  //this.Validate();
  //this.nameBindingSource.EndEdit();
  //this.tableAdapterManager.UpdateAll(this.cLIENTDB3DataSet);

  string cmdString = "INSERT INTO Name (ColName) VALUES (@val1)";

  using (connection = new SqlConnection(connectionString))
  {
    using (SqlCommand comm = new SqlCommand(cmdString,connection))
    {
      connection.Open();
      comm.Connection = connection;
      comm.CommandString = cmdString;
      comm.Parameters.AddWithValue("@val1", nameTextBox.Text);

      comm.ExecuteNonQuery();
    }
  }

}
Rickest Rick
  • 1,519
  • 1
  • 15
  • 28
  • I've tried adding the column name to the cmdString and still have no luck. I'm not sure about comm.CommandString though, visual studio isnt giving me that as an option when I type it out and the closest I found was CommandText – user2874417 Dec 23 '15 at 23:50
  • There is no property called CommandString in the SqlCommand object. – Steve Dec 23 '15 at 23:56
  • CommandString might not be the correct syntax, but you were missing the table column name. Try CommandText with the column name. This should do the trick. – Rickest Rick Dec 24 '15 at 00:20