Update Statement?

[pacheco]

Hi guys,

I have 2 boxes (UsernameComboBox and PasswordTextBox) and 1 button (UpdateButton) and i would like it to update based on the info inserted above. Can someone shed some light on what's wrong with my codes below?

private void UpdateButton_Click(object sender, EventArgs e)
        {
            PC001 f1 = new PC001();
            Username = UsernameComboBox.Text.ToString();
            Password = PasswordTextBox.Text.ToString();
            string SQLUpdateString;
            SQLUpdateString = "UPDATE PC SET PCUsername ='" + Username + "', PCPassword='" + Password + "' WHERE PCName='PC001'";

            OleDbCommand SQLCommand = new OleDbCommand();
            SQLCommand.CommandText = SQLUpdateString;
            SQLCommand.Connection = f1.DBconn;
            int response = SQLCommand.ExecuteNonQuery();
            MessageBox.Show("Update successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
            Close();
        }

[Momerath]

Lines 4,5: The Text property of both these controls is a string, don't call ToString on strings.

Lines 9, 10, 11 - You are mixing OleDB objects and SQLServer objects. Pick one set.

Line 12. You've not opened the connection that I can see.

[pacheco]

Lines 4,5: The Text property of both these controls is a string, don't call ToString on strings.

Lines 9, 10, 11 - You are mixing OleDB objects and SQLServer objects. Pick one set.

Line 12. You've not opened the connection that I can see.

Hi, thanks for the reply. Sorry i'm new to databases, hope you can bear with me.

Line 4,5: So do i remove these lines?

Lines 9,10, 11: Say if i want to stick to OleDB, how can i modify my codes?

Line 12: I opened connection at the top,

public PC001()
        {
            InitializeComponent();

            string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBtest.mdb;";
            try
            {
                DBconn = new OleDbConnection(ConnectionString);
                DBconn.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
           
        }

[Mitja Bonca]

1. You NEED parameters, if you want to do exactly what you have to do...
but it would be bette to use them in the parametrized query, that means that you add parameters with sql(oleDB) command object, And not insert parameters directly into the query. - So answer in NO - keep the parameters.

Further: take a look into tis example, and do exactly as its is here (you only chaange sqlCommand with OldDbCommand) AND change the query (insert, update tor select) and this is it:

//pass the parameters to this method: 
    private void InsertingData(string name, string password, string email)
    {
      string connString = @"server=x;uid=y;pwd=z;database=xyz"; //this is an example, you need your own
      using (SqlConnection sqlConn = new SqlConnection(connString))
      {
        string query = String.Format(@"INSERT INTO Users VALUES (@id, @name, @password, @email)");
        using (SqlCommand cmd = new SqlCommand(query, sqlConn))
        {
          cmd.CommandType = CommandType.Text;
          cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1; //FIND YOUR NEW ID IF YOU HAVE THIS COLUMN IN DATABASE
          cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = name;
          cmd.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = password;
          cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = email;
          cmd.Connection.Open();
          try
          { cmd.ExecuteNonQuery(); }
          catch (Exception ex)
          { 
            MessageBox.Show(ex.Message); 
          }
          finally
          { cmd.Connection.Close(); }
        }
      }
    }

Hope it helps,
Mitja