Updating DB with Values on Windows Screen?

There are many things you can do. The first thing to do is to look at what cmd.CommandText is. The following will duplicate what your code is doing.

String t1 = "One";
String t2 = "Two";
String t3 = "Three";
String sql = "UPDATE BM_DISPUTES SET " + "DIS_LASTNAME = '" + t2 + "', " + "DIS_FIRSTNAME = '" + t3 + "', " + "WHERE " + "DIS_NO =" + t1;

The following is the result of that:

UPDATE BM_DISPUTES SET DIS_LASTNAME = 'Two', DIS_FIRSTNAME = 'Three', WHERE DIS_NO =One

See the problem? There are no delimters around the text at the end. That should be very easy to figure out. Now the next thing to do is to look at the code and simplify it; you are putting together text literals using "+" when you can put the text together in one literal. This makes the code easier for you and us to read and it is easier for the computer. So the following will work better; just replace t1, t2 and t3 with the corresponding variables that you need.

String sql = "UPDATE BM_DISPUTES SET DIS_LASTNAME = '" + t2 + "', DIS_FIRSTNAME = '" + t3 + "', WHERE DIS_NO = '" + t1 + "'";

One more thing. You really should use a parameterized SQL statement. For one reason, your update I think will fail if you have a last name with an apostrophe, such as "O'Heaney". If you were to use a TableAdapter, then the SQL will be created for you.

 Hey,  I finally figured out my problem with the code and updated the DB.  You got me on the way to recovery once so maybe you can help me again. Now I am getting an error where I am looking for a particular client.  The code is below and also the error:

sqlConnection1.Close();

sqlConnection1.Open();

int COUNT = (int)cmd.ExecuteNonQuery();//UPDATE A SPECIFIC RECORD...

cmd.CommandText =

cmd.ExecuteNonQuery();

sqlConnection1.Close();

"UPDATE BM_DISPUTES SET " + "DIS_LASTNAME = '" + textBox2.Text + "', " +"DIS_FIRSTNAME = '" + textBox3.Text + "', " +"WHERE " + "DIS_NO =" + textBox1.Text;
int numberOfAffectedRows = cmd.ExecuteNonQuery();

ERROR:  System.Data.SqlClient.SqlException was unhandled
  Message=Incorrect syntax near the keyword 'WHERE'.

Textbox1 = the record I want to update.  When I took the Where clause out it update every record with the same last and first name.



Thnaks Again,
Ben
 

 Thanks so much!  This is what I have been looking for.  I knew it had to do with the textbox, but I could not find an example of how to use it.  Again thanks for you help. Now I feel like I can complete this.

Ben

Hi Ben,

If we take your example

"UPDATE BM_DISPUTES SET DIS_LASTNAME = 'DOE', DIS_FIRSTNAME = 'JOHN'" +
"WHERE DIS_NO = 17";

then if DIS_LASTNAME is a text box on your screen you would do something like "UPDATE BM_DISPUTES SET DIS_LASTNAME = ' + TextBox1.Text + "', DIS_FIRSTNAME = '" + TextBox2.Text + "'"

Is this what you want to know?

Also note that a TableAdapter will do most everything you need to do. It will have a DataReader or equivalent and it will have SQL for an insert, an update and a delete.

Hi Ben,

If you need to go back and get the values, you can query the table with a dataadapter which will put the results in a dataset.  The other option is to use a data reader.

http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

In your case:

  SqlCommand command = new SqlCommand(
  "SELECT DIS_LASTNAME, DIS_FIRSTNAME FROM BM_DISPUTES WHERE DIS_NO=17;",
  connection);
  connection.Open();

  SqlDataReader reader = command.ExecuteReader();

  if (reader.HasRows)
  {
  while (reader.Read())
  {
  Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
  reader.GetString(1));
  }
  }
  else
  {
  Console.WriteLine("No rows found.");
  }
  reader.Close();
  }

 Hey Mike,
   Thanks for the help and this was a good example.  But I noticed on the example that the program is actually providing values to be updated, inserted or deleted.  Am I thinking wrong?  What I need to do is actually pull values that I enter and take those values and store then to the DB.  I am using VS2010 C# and using a Windows application.  Below is the code that I am using and it updates the DB, but with values I give.  I am not certain of how to get the values from the windows screen that I entered to be used. My code is below:
 

cmd.CommandText =

cmd.ExecuteNonQuery();

"UPDATE BM_DISPUTES SET DIS_LASTNAME = 'DOE', DIS_FIRSTNAME = 'JOHN'" + "WHERE DIS_NO = 17"; 
cmd.ExecuteNonQuery();

I would like to know how to update DIS_LASTNAME, which is the DB name with the value I enter on the screen.  I am really new to this C# but not to programming.  I understand logic, it's just what syntax to use to get what I want..
 
Thanks for you help.!!
Ben

I suggest looking at the articles in this web site. I hope you enjoy my Database Table Update in a DataGridView without Writing Code.

Here a DataGridView article..

DataGridView - Insert, Update, Delete and Retrieve records using stored procedure

I think you could get away with using ADO.NET (are you using Sql Server?)  If so, use the SqlClient in ADO.NET.
1) Create a SqlConnection using the connection string (You can figure out the connection string by connecting through Server Explorer)
2) Create a SqlCommand passing it the SqlConnection and set the command text to your update as you did above.
3) Open the connection.  
2) use the command method  ExecuteNonQuery
Below is a good example of this:
http://www.java2s.com/Code/CSharp/Database-ADO.net/UsetheExecuteNonQuerymethodtorunINSERTUPDATEandDELETEstatements.htm