CODEWITHSUNDEEP
c#sqldatabase
user2448571
user2448571

Reputation: 21

C# SQL Server CE - Update won't work

I'm trying to finish a college project that requires a program to interact with a database.

Some of my naming is a little odd, but don't worry!

I'm trying to use a single submit button to either update or insert to the database.

Main issue is that I can't get an update to work though when I changed my code to try and fix it, I made it worse. Here is what I currently have.

private void btn_submit_Click(object sender, EventArgs e)
{
   using (SqlCeConnection con = new SqlCeConnection(@"Data Source=G:\Dropbox\HND\Visual Studio\Visual C#\TestForms\TestForms\Database1.sdf"))
   {
      con.Open();
      string taskSel = "SELECT TaskCode FROM TaskCode;";
      SqlCeCommand c1 = new SqlCeCommand(taskSel, con);
      SqlCeDataReader reader;
      reader = c1.ExecuteReader();

      if (reader.Read())
      {
         try
         {
            string taskUpdate = "UPDATE TaskCode SET TaskCode = @TaskCode, TaskDescription = @TaskDescription = WHERE TaskCode = @TaskCode;";
            SqlCeCommand c = new SqlCeCommand(taskUpdate, con);
            c.Parameters.AddWithValue("@TaskCode", cbx_taskCode.Text);
            c.Parameters.AddWithValue("@TaskDescription", txt_desc.Text);
            c.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Record has been updated");
            MainMenu.Current.Show();
            this.Close();
         }
         catch (SqlCeException exp)
         {
            MessageBox.Show(exp.ToString());
         }
      }
      else
      {
         try
         {
            string taskInsert = "INSERT INTO TaskCode VALUES (@TaskCode, @TaskDescription);";
            SqlCeCommand c = new SqlCeCommand(taskInsert, con);
            c.Parameters.AddWithValue("@TaskCode", cbx_taskCode.Text);
            c.Parameters.AddWithValue("@TaskDescription", txt_desc.Text);
            c.ExecuteNonQuery();
            con.Close();
            MessageBox.Show("Record has been added");
            MainMenu.Current.Show();
            this.Close();
         }
         catch (SqlCeException exp)
         {
            MessageBox.Show(exp.ToString());
         }
      }
   }
}

Has anyone got any ideas why I am getting an error on the c.ExecuteQuery line?

If I remove said line, it will not throw an exception, but it will not update the database.

Thanks

Upvotes: 0

Views: 1961

Answers (2)

Steve
Steve

Reputation: 216293

You have a simple syntax error in your update query just before the where statement.
There is an invalid equal sign

string taskUpdate = "UPDATE TaskCode SET TaskCode = @TaskCode, " + 
                    "TaskDescription = @TaskDescription " + 
                    "WHERE TaskCode = @TaskCode;";

Your query also could be simplified with

using (SqlCeConnection con = new SqlCeConnection(@"Data Source=G:\Dropbox\HND\Visual Studio\Visual C#\TestForms\TestForms\Database1.sdf"))
{
    con.Open();
    string taskSel = "SELECT COUNT(*) FROM TaskCode";
    string cmdText; 
    SqlCeCommand c1 = new SqlCeCommand(taskSel, con);
    int count = (int)c1.ExecuteScalar();
    if (count > 0)
    {
        // Here there is no point to update the TaskCode. You already know the value
        // Unless you have a different value, but then you need another parameter
        // the 'old' TaskCode.....
        cmdText = "UPDATE TaskCode SET " + 
                  "TaskDescription = @TaskDescription " + 
                  "WHERE TaskCode = @TaskCode;";
    }
    else
    {
        cmdText = "INSERT INTO TaskCode VALUES (@TaskCode, @TaskDescription);";
    }
    try
    {
        SqlCeCommand c = new SqlCeCommand(cmdText, con);
        c.Parameters.AddWithValue("@TaskCode", cbx_taskCode.Text);
        c.Parameters.AddWithValue("@TaskDescription", txt_desc.Text);
        c.ExecuteNonQuery();
        MessageBox.Show(count > 0 ? "Record has been updated" : "Record has been added");
        MainMenu.Current.Show();
        this.Close();
    }
    catch (SqlCeException exp)
    {
         MessageBox.Show(exp.ToString());
    }
}

Upvotes: 3

eternay
eternay

Reputation: 3814

Not sure if it is the only problem, but you have an equal (=) sign before the WHERE keyword.

Upvotes: 2

Related Questions