Datatable cell value update

Question

Hello experts,

I am trying to update a cell named value by encrypting value in it.

the current value in that cell is "False" and i want it to replave it "uZ6d2J/g784" , i am getting error that its is a boolean value and not allowing change. the table is made from an excel file and on a encrypt button press i am trying to chnage the values in this xl table.

i am getting error

System.ArgumentException: 'String was not recognized as a valid Boolean.Couldn't store in Value Column. Expected type is Boolean.'

can you please explain what wrong i am doing or how to do it correctly ?

Thanks.

id  Name    Value   Type    StartedOn
2   PAcount 0   enum    NULL
3   SecondaryStore  FALSE   bool    NULL
5   Ewaybill    SingleLocation  enum    NULL
6   pdi_after_invoice   No  enum    NULL
7   InventorySoftware   FALSE   bool    NULL
8   Json2CSV    FALSE   bool    NULL
9   MahindraPdf TRUE    bool    NULL
10  PoImportB2S2    FALSE   bool    NULL
11  TCSModule   TRUE    bool    NULL
12  Bill2Pdf    TRUE    bool    NULL
13  BillAdvice  FALSE   bool    NULL
14  DebitAdviceOnAmendmentNumber    FALSE   bool    NULL
15  DeveloperBench  TRUE    bool    NULL
16  GateOutEntry    FALSE   bool    NULL
17  IrnByGsp    FALSE   bool    NULL
18  Oa_Authorisation    FALSE   bool    NULL
19  RR  TRUE    bool    NULL
20  AdviceCutoffDate    31-12-9998 00:00    Date    NULL
21  EOT 31-12-9998 00:00    Date    NULL

private void cmbXlSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            OleDbDataAdapter daExcel = new OleDbDataAdapter();
            daExcel.SelectCommand = new OleDbCommand("SELECT * from [" + cmbXlSheet.Text + "]", conExcel);
            DataSet dsExcel = new DataSet();
            daExcel.Fill(dsExcel);
            if (dsExcel.Tables[0].Rows.Count <= 0)
            {
                MessageBox.Show("Zero Rows");
                btnOpenFile.Focus();
                return;
            }
            tabControl1.SelectTab(tpXl);
            xl = dsExcel.Tables[0];
            dgvProperties.DataSource = xl;
        }

        private void btnEncrypt_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < xl.Rows.Count; i++)
            {
                DataRow r = xl.Rows[i];
                string senc= Classes.IsValid.EncryptPass(r["value"].ToString());
                r["value"] = senc;
            }
        }

Answer

Try something like this:

var col1 = xl.Columns[ "value" ];
var col2 = xl.Columns.Add( "value2" );
col2.SetOrdinal( col1.Ordinal );

for( int i = 0; i < xl.Rows.Count; i++ )
{
   . . . .
   r[col2] = senc;
}

xl.Columns.Remove( col1 );
col2.ColumnName = col1.ColumnName;

Answer

Thanks Viorel-1 ,

i idid not try your solution, I am doughting that reading excel file need some more knowledge, even data shown after read by Microsoft.ACE.OLEDB is presentated in-correctly. its data type is decided unknowingly (string data as boolean)

so

i saved that excel file as .txt/.csv and resolved my problem, i procesed txt file diffrently , by calling method ProcessTxtFile attached below

can somebody explain/teach us how to do read excel files with proper data types and content so as to read them as real world table and use them for write/read/update/delete etc.

private void btnOpenFile_Click(object sender, EventArgs e)
        {
            //DataTable dtExlSheets;
            OpenFileDialog ofn = new OpenFileDialog();
            {
                ofn.Filter = "CSV(*.CSV),Text File(*.txt),HTML(*.html),Excel 97-2003 Workbook(*.xls),Excel >= 2007(*.xlsx),(*.xlsb),(*.xlsm)|*.csv*;*.txt;*.html;.xls;*.xlsx;*.xlsb;*.xlsm";
            }
            try
            {
                if (ofn.ShowDialog() == DialogResult.OK)
                {
                    label1.Text = ofn.FileName;
                    string filextntn = Path.GetExtension(ofn.FileName).ToUpper();
                    string strExcelCon;
                    switch (filextntn)
                    {
                        case ".XLS":
                            strExcelCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ofn.FileName + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                            break;
                        case ".XLSX":
                            strExcelCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofn.FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
                            break;
                        case ".XLSB":
                            strExcelCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofn.FileName + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                            break;
                        case ".XLSM":
                            strExcelCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ofn.FileName + ";Extended Properties=\"Excel 12.0 Macro;HDR=YES\"";
                            break;
                        case ".TXT":
                            //EDITL.txt for BAJAJ
                            cmbXlSheet.Visible = false;
                            ProcessTxtFile(ofn.FileName);
                            tabControl1.SelectTab(tpXl);
                            return;
                        case ".CSV":
                            //EDITL.txt for BAJAJ
                            cmbXlSheet.Visible = false;
                            ProcessTxtFile(ofn.FileName);
                            tabControl1.SelectTab(tpXl);
                            return;
                        default:
                            strExcelCon = " File Type:" + filextntn;
                            break;
                    }
                    DataTable exDT = new DataTable();
                    {
                        conExcel = new OleDbConnection(strExcelCon);
                        conExcel.Open();
                        exDT = conExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        //dgvProperties.DataSource = exDT;
                        string Table_Name = null;
                        //See it Excel Sheet Not Empty
                        if (exDT != null || exDT.Rows.Count > 0)
                        {
                            //Get Worksheets (Tables) From Excel File
                            cmbXlSheet.Items.Clear();
                            foreach (DataRow r in exDT.Rows)
                            {
                                cmbXlSheet.Items.Add(r["table_name"].ToString());
                                Table_Name = r["table_name"].ToString();
                            }
                            //Set 1st Worksheet(Tables) From Excel as selected
                            //here dgvProperties will get datasource assigned,
                            //becuase cmbXlSheet_SelectedIndexChanged will fire after next assignment
                        }
                        conExcel.Close();
                        //it will assign 1st worksheet to dgvProperties
                        cmbXlSheet.SelectedIndex = 0;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        private void cmbXlSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            OleDbDataAdapter daExcel = new OleDbDataAdapter();
            daExcel.SelectCommand = new OleDbCommand("SELECT * from [" + cmbXlSheet.Text + "]", conExcel);
            DataSet dsExcel = new DataSet();
            daExcel.Fill(dsExcel);
            if (dsExcel.Tables[0].Rows.Count <= 0)
            {
                MessageBox.Show("Zero Rows");
                btnOpenFile.Focus();
                return;
            }
            tabControl1.SelectTab(tpXl);
            if (xl != null)
                xl.Clear();
            xl = dsExcel.Tables[0];
            dgvProperties.DataSource = xl;
        }
        private void btnEncrypt_Click(object sender, EventArgs e)
        {
            string senc;
            for (int i = 0; i < xl.Rows.Count; i++)
            {
                DataRow r = xl.Rows[i];
                if (string.IsNullOrWhiteSpace(r["value"].ToString()))
                    continue;
                senc = Classes.IsValid.EncryptPass(r["name"].ToString()+"-"+r["value"].ToString());
                r["value"] = senc;
            }
        }
        private void btnDecrypt_Click(object sender, EventArgs e)
        {
            string senc;
            foreach (DataGridViewRow dgvrxl in dgvProperties.Rows)
            {
                if (dgvrxl.Cells["ModuleValue"].Value == null)
                    continue;
                senc = Classes.IsValid.DecryptPass(dgvrxl.Cells["ModuleValue"].Value.ToString());
                if(senc==null)
                    continue;
                senc = senc.Substring(dgvrxl.Cells["ModuleName"].Value.ToString().Length + 1);
                dgvrxl.Cells["ModuleValue"].Value = senc;
            }
        }

        private void ProcessTxtFile(string filename)
        {
            xl.Clear();
            dgvProperties.DataSource = null;
            StreamReader dr = new StreamReader(filename);
            string stringData = dr.ReadToEnd();
            char[] rowSplitter = { '
', '
' };
            string[] rowsInTextFile = stringData.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries);
            char[] columnSplitter = { '	' };
            string[] colname = rowsInTextFile[0].Split(columnSplitter);

            //becuase we want to skip header row, and table index starts from 0
            int cnt = -1;
            foreach (string row in rowsInTextFile)
            {
                if (cnt.Equals(-1))
                {
                    cnt++;
                    continue;
                }
                string[] rowValue = row.Split(',');
                xl.Rows.Add();
                xl.Rows[cnt]["id"] = rowValue[0];
                xl.Rows[cnt]["name"] = rowValue[1];
                xl.Rows[cnt]["value"] = rowValue[2];
                cnt++;
            }
            dgvProperties.DataSource = xl;
        }

Share via

Datatable cell value update

2 answers

Your answer