I cannot figure out why my update statement will not work? I figure it has to be something small that Im missing.

private void button2_Click(object sender, EventArgs e)
        {
      
            string strconn = "SERVER=15.5.7.9;User=sa;password=JFK3G785!340b;Database=addressstagingtable";
            SqlConnection conn = new SqlConnection(strconn);

            conn.Open();

            try
            {
              

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "UPDATE address" + "Set unitnumber =" + Addresstxt.Text + "WHERE streetnumber=148";
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    MessageBox.Show("Success");
                }
            }

            catch (Exception)
            {
                MessageBox.Show("An error was generated, please contact your C.A.R.T.S System Administrator","MPL");
            }


        }
cmd.CommandText = "UPDATE address" + "Set unitnumber =" + Addresstxt.Text + "WHERE streetnumber=148";

Why are you building this out of parts of strings? What you are actually getting is:
"UPDATE addressSet unitnumber = " + Addresstxt.Text + "WHERE streetnumber=148"

Notice that you did not put a space between address and Set and that there is also no space between the text from your Addresstxt control and the WHERE clause.

I'm still getting the error. Sorry I'm very new to C# I just picked it up about two weeks ago. How would you recommend me writing the statement?

cmd.CommandText =  "UPDATE address" + "  " + "Set unitnumber = " + Addresstxt.Text + " " + "WHERE streetnumber= 148";

You cannot use an UPDATE statement and then use the Datareader method. This one is for reading data (when doing SELECT statement).

Aynway, I dont know what exactly are you trying to do, but lets say you want to do an update on some table. So you have to do it this way (I have repaired your code, to look more suitable, more fine :) ):

string strconn = "SERVER=15.5.7.9;User=sa;password=JFK3G785!340b;Database=addressstagingtable";
using(SqlConnection conn = new SqlConnection(strconn))
{            
      try
      {
                string sqlQuery = @"UPDATE address SET unitnumber = @address WHERE streetnumber = @number";
                SqlCommand cmd = new SqlCommand(sqlQuery, strconn);
                cmd.Parameters.Add8("@address", SqlDbType.VarChar, 50).Value = Addresstxt.Text;
                cmd.Parameters.Add8("@number", SqlDbType.int).Value = 148;  
                conn.Open();          
                cmd.ExecuteNonQuery(); //if you want to do an update, you have to use this method!!
      }                    
      catch (Exception)
      {
              MessageBox.Show("An error was generated, please contact your C.A.R.T.S System Administrator","MPL");
      }
}

Edited 5 Years Ago by Mitja Bonca: n/a

This article has been dead for over six months. Start a new discussion instead.