c# - Update excel spreadsheet with textbox text

Question

I’m trying to update a spreadsheet data by textbox. I tried the code below and it did not generate any exception, but did not update the spreadsheet.

 private void button2_Click(object sender, EventArgs e)
        {

            try
            {

                OleDbCommand command = new OleDbCommand();
                command.CommandText = "update [Designa$] set Nome = '" + textBox1.Text + "' where Cod = '"+textBox2.Text+"'";
                command.Parameters.Add(new OleDbParameter("@Nome",textBox1.Text));
                command.Parameters.Add(new OleDbParameter("@Cod", textBox2.Text));

                MessageBox.Show("Dados Atualizados....");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

Answer 1

You can use the OleDbCommand.Parameters.AddWithValue(string parameterName, Object value) Try it this way:

private void button2_Click(object sender, EventArgs e) {

        try
        {

            OleDbCommand command = new OleDbCommand();
            command.CommandText = "UPDATE [Designa$] SET Nome = @Nome WHERE Cod = @Cod";
            command.Parameters.AddWithValue("@Nome",textBox1.Text);
            command.Parameters.AddWithValue("@Cod", textBox2.Text);

            MessageBox.Show("Dados Atualizados....");
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

    }

Answer 2

Are you looking for the Cod as if it were a string and it is not a string but a number. Remove the quotes and should work:

command.CommandText = "update [Designa$] set Nome = '" + textBox1.Text + "' where Cod = " + textBox2.Text;

Answer 3

worked here! Follow the code:

string sFileXLSX = "path of your.xlsx"; string strConnXLSX ="Provider=Microsoft.ACE.OLEDB.12. 0;Data Source='" + sFileXLSX + "';Extended Properties=Excel 12.0;";

        using (OleDbConnection conn = new OleDbConnection(strConnXLSX))
        {
            
            string strSQL = "UPDATE [Tabela$] SET Coluna = @ultima WHERE nome = '" + textBox1.Text +"'";
            OleDbCommand cmd = new OleDbCommand(strSQL, conn);
            cmd.Parameters.AddWithValue("@ultima", "22/03/2021");
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }