Updating Excel sheet with 1 or -1 in column 'stock'-CodePudding

https://imgur.com/a/OKZPMYl I am loading a excel file into a datagridview with this code

private void Loadexcel_Click(object sender, EventArgs e)
    {
        OpenFileDialog importexcel = new OpenFileDialog();
        importexcel.Filter = "Excel files | *.xlsx";
        if (importexcel.ShowDialog() == DialogResult.OK)
            filetextBox.Text = importexcel.FileName;

        String connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="   filetextBox.Text   ";Extended Properties=Excel 12.0;";
        OleDbConnection conn = new OleDbConnection(connStr);
        string strSQL = "SELECT * FROM [Sheet1$]";
        OleDbCommand cmd = new OleDbCommand(strSQL, conn);
        DataTable dT = new DataTable();
        
        conn.Open();
        try
        {
            OleDbDataReader dR = cmd.ExecuteReader();
            dT.Load(dR);
            excelviewgrid.DataSource = dT;
        }
        catch (Exception ex)

        {
            MessageBox.Show(ex.Message);
        }

        finally
        {
            conn.Close();
            NewOrder.Enabled = true;
        }
    
        
    }

i am adding items to my listbox with a textboxinput from the user that should match the Material number which adds the information from material description column and some extra hardcoded text. and also loops the input to the amount input in the combobox. so far this all works.

private void textBoxInput_KeyUp(object sender, KeyEventArgs e)
    {
        if (e.KeyCode == Keys.Enter)
        {
            string strCurrentString = textBoxInput.Text.Trim().ToString();

            if (strCurrentString != "")
            {
                if (radioButtonPick.Checked == true && radioButtonNew.Checked == true)
                {
                    foreach (DataGridViewRow row in excelviewgrid.Rows)
                    {
                        if (textBoxInput.Text == row.Cells["Material"].Value.ToString())
                        {
                            int value = Convert.ToInt32(comboBoxmultiply.Text);
                            for (int i = 0; i < value; i  )
                            {
                                listBoxinput.Items.Add(textBoxInput.Text   " | "   row.Cells["Material Description"].Value   " (-1 New Pick)");
                            }
                            comboBoxmultiply.SelectedIndex = 0;
                            textBoxInput.Clear();

                        }

                    }

                }
            }

but now i want to make it that for each item on the listbox https://imgur.com/a/soeENXs it updates the stock collumn by 1 or -1 depending on pick or receive(this is done with using the right event trigger(if radiobuttonxxx == true) and give an error if going below 0

 private void buttontest_Click(object sender, EventArgs e)
    {
        String connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="   filetextBox.Text   ";Extended Properties=Excel 12.0;";
        OleDbConnection conn = new OleDbConnection(connStr);

        conn.Open();
        OleDbCommand ojbCmdSelect = new OleDbCommand("UPDATE [sheet1$] set [Stock] = [Stock]   1 where [Material] = 114-6776");
        ojbCmdSelect.ExecuteNonQuery();
        conn.Close();
    }

FIXED look further I tried this code with a test button and just the material from one item(this needs to be changed to every item on the listbox) on the excel list.

but it throws me a error "System.InvalidOperationException: 'ExecuteNonQuery: Connection property was not initialized.'"

EDIT: the connection issue seems to be fixed with this code EDIT:2 massively improved and corrected by Cetin Basoz (look below)

conn.Open();
        string strSQL = "UPDATE [sheet1$] set Stock = stock 1 where Material = '114-6776'";
        OleDbCommand ojbCmdSelect = new OleDbCommand(strSQL,conn);
        ojbCmdSelect.ExecuteNonQuery();
        conn.Close();

now i need to figure out how i can make the "where material = listboxitems" EDIT:3 also provided by Cetin Basoz(look below)

I am new to coding so no experience.

CodePudding user response：

As the message says, ojbCmdSelect needs a connection and you didn't set it. Also if you pass that, there is another error in the OleDbCommand text itself. You should use parameters and you could do the update single or in a loop. ie:

private void buttontest_Click(object sender, EventArgs e)
{
    String connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="   filetextBox.Text   ";Extended Properties=Excel 12.0;";
    using (OleDbConnection conn = new OleDbConnection(connStr))
    using (OleDbCommand ojbCmdSelect = new OleDbCommand("UPDATE [sheet1$] set [Stock] = [Stock]   1 where [Material] = @material", conn))
    { 
        ojbCmdSelect.Parameters.Add("@material", OleDbType.VarChar);
        
    
        conn.Open();
        // this part could be in a loop
        // for ... {
        ojbCmdSelect.Parameters["@material"].Value = "114-6776";
        ojbCmdSelect.ExecuteNonQuery();
        // }
        conn.Close();
    }
}

EDIT: As per the listbox.items, you are adding items to listbox with their code appended extra space | and then description. You first need to get the code per listbox item:

private void buttontest_Click(object sender, EventArgs e)
{
    String connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="   filetextBox.Text   ";Extended Properties=Excel 12.0;";
    using (OleDbConnection conn = new OleDbConnection(connStr))
    using (OleDbCommand ojbCmdSelect = new OleDbCommand("UPDATE [sheet1$] set [Stock] = [Stock]   1 where [Material] = @material", conn))
    { 
        ojbCmdSelect.Parameters.Add("@material", OleDbType.VarChar);
        
    
        conn.Open();
        foreach (string item in listBoxinput.Items)
        {
            var itemcode = item.Split('|')[0].Trim();
            ojbCmdSelect.Parameters["@material"].Value = itemcode;
            ojbCmdSelect.ExecuteNonQuery();

        }
        conn.Close();
    }
}

PS: In a real world application, instead of having such items in a listbox, you would want to bind it to an object where it has Id and the display text at least. Splitting from a long string to get the Id is not feasible at all times.