Click here to Skip to main content
14,694,371 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem with my project


I want to check and validate my excel data before insert to database sql server,

I wish when one of data is false however other is true,the condition would return false ,
and its only return true when all data is true

how i can solve this ,?

What I have tried:

this is my code


 protected void check(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                string[] arr = new String[15];
                string filename = "Hr_Report_" + DateTime.Now.ToString("dddd_dd_MMMM_yyyy") + ".xls";
 label1.Text = filename;
FileUpload1.SaveAs(temp_file + filename);
string tempfile = temp_file + filename;
string finalfile = final + "final.xls";
string c = @tempfile;
string b = @finalfile;
DataTable table = new DataTable();
                FileInfo existingFile = new FileInfo(tempfile); //+ FileUpload1.FileName);
                using (ExcelPackage package = new ExcelPackage(existingFile))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int colCount = worksheet.Dimension.End.Column;
                    int rowCount = worksheet.Dimension.End.Row;
                    table.Columns.Add("emp_fullname", typeof(DateTime));
                    table.Columns.Add("emp_nik", typeof(string));
                    table.Columns.Add("Timer_Finger", typeof(string));
                    for (int i = 1; i < rowCount; i++)
                    {
                        DateTime d;
                        bool chValidity = DateTime.TryParseExact(
                        worksheet.Cells[i + 1, 1].Value.ToString(),
                         "MM/dd/yyyy h:mm:ss",
                         CultureInfo.InvariantCulture,
                         DateTimeStyles.None,
                         out d);
                            TableRow row = new TableRow();
                            TableCell cell1 = new TableCell();
                            if (chValidity == true){  // the problem on this line,i have 5 row data in excel ,4 true ,1 false ,and its return true first the false 
                                label1.Text="Suskses";
                            }
                            else{
                            cell1.Text = worksheet.Cells[i + 1, 1].Value.ToString() + "  " + worksheet.Cells[i + 1, 2].Value.ToString() + " " + worksheet.Cells[i + 1, 2].Value.ToString() + " Baris ke " + i;
                            row.Cells.Add(cell1);
                            myTable.Rows.Add(row);
                            File.AppendAllText("E:\\test.txt", label1.Text);
                            }
                        }
                    }
                }
            else
            {
                Response.Write("<script>window.alert('File Belum diupload')</script>");
            }
        }
Posted
Updated 7-May-20 22:35pm

1 solution

You need to move the validity test outside the loop so the success message only appears if all rows are true.
bool chValidity = true; // assume all checks will succeed
for (int i = 1; i < rowCount; i++) // iterate all rows
{
    DateTime d;
    chValidity = DateTime.TryParseExact(
    worksheet.Cells[i + 1, 1].Value.ToString(),
    "MM/dd/yyyy h:mm:ss",
    
    if (chValidity == false)
    {
        cell1.Text = worksheet.Cells[i + 1, 1].Value.ToString() + "  " + worksheet.Cells[i + 1, 2].Value.ToString() + " " + worksheet.Cells[i + 1, 2].Value.ToString() + " Baris ke " + i;
        row.Cells.Add(cell1);
        myTable.Rows.Add(row);
        File.AppendAllText("E:\\test.txt", label1.Text);
    }
} // end of for loop

if (chValidity == true){  // all 5 rows are valid 
    label1.Text="Suskses";
}


[edit]
Removed extra condition test in the for statement.
[/edit]
   
v2
Comments
Member 14760154 8-May-20 4:13am
   
now the problem is ,its break and only insert the first false row , the next row that was false not inserted into the table ,

Can you help me ,solve this ?

I wish like this
15/10/2020 Wrong at line 2
16/10/2020 Wrong at line 3
16/10/2020 Wrong at line 6

and my program show this
15/10/2020 Wrong at line 2
Richard MacCutchan 8-May-20 4:17am
   
Then just remove the extra test in the for statement.
Member 14760154 8-May-20 4:47am
   
i have tried it,it return true also ,

when i remove "ch_validity==true" in the "for" statement
Richard MacCutchan 8-May-20 4:49am
   
Does that mean that it works or not? If not, then you need to edit your question and show us the modified code.
Member 14760154 8-May-20 5:41am
   
for some problem ,it works ,but it cause another problem ,

protected void check(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string[] arr = new String[15];
string filename = "Hr_Report_" + DateTime.Now.ToString("dddd_dd_MMMM_yyyy") + ".xls";
FileUpload1.SaveAs(temp_file + filename);
string tempfile = temp_file + filename;
string finalfile = final + "final.xls";
string c = @tempfile;
string b = @finalfile;
DataTable table = new DataTable();
FileInfo existingFile = new FileInfo(tempfile); //+ FileUpload1.FileName);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int colCount = worksheet.Dimension.End.Column;
int rowCount = worksheet.Dimension.End.Row;
table.Columns.Add("emp_fullname", typeof(DateTime));
table.Columns.Add("emp_nik", typeof(string));
table.Columns.Add("Timer_Finger", typeof(string));
bool chValidity =true;
for (int i = 1; i < rowCount && chValidity==true; i++)
{
DateTime d;
chValidity = DateTime.TryParseExact(
worksheet.Cells[i + 1, 1].Value.ToString(),
"MM/dd/yyyy h:mm:ss",
CultureInfo.InvariantCulture,
DateTimeStyles.None,
out d);
if (chValidity == false)
{
TableRow row = new TableRow();
TableCell cell1 = new TableCell();
cell1.Text = worksheet.Cells[i + 1, 1].Value.ToString() + " " + worksheet.Cells[i + 1, 2].Value.ToString() + " " + worksheet.Cells[i + 1, 2].Value.ToString() + " Baris ke " + i;
row.Cells.Add(cell1);
myTable.Rows.Add(row);
File.AppendAllText("E:\\test.txt", label1.Text);
}
}
if (chValidity == true)
{
label1.Text = "suskses";
}
}
}
else
{
Response.Write("window.alert('File Belum diupload')");
}
}
Richard MacCutchan 8-May-20 5:46am
   
You have not changed the for statement, it should now be:
for (int i = 1; i < rowCount; i++) // iterate all the rows

Also, you have not explained what other problem you have.
Member 14760154 8-May-20 6:41am
   
Actually ,my problem is I want to insert schedule into my database by using excel ,because it will be taken long time if i use form to insert it ,

but sometimes i write wrong datetime format , and its make an error ,

now i try to solve it , i will validate my data before inserted to database,

I wish ,when i have 1 row error and 4 not error, all row would not inserted to database ,

but my current condition after change it is ,when i row error it failed to insert to database ,but another 4 rows is successfully inserted to database
Richard MacCutchan 8-May-20 8:43am
   
You have the following:
if (chValidity == false)
{
TableRow row = new TableRow();
TableCell cell1 = new TableCell();
cell1.Text = worksheet.Cells[i + 1, 1].Value.ToString() + " " + worksheet.Cells[i + 1, 2].Value.ToString() + " " + worksheet.Cells[i + 1, 2].Value.ToString() + " Baris ke " + i;
row.Cells.Add(cell1);
myTable.Rows.Add(row);
File.AppendAllText("E:\\test.txt", label1.Text);
}

So if the validity check is false you are adding the row. Is that correct or not?
Member 14760154 8-May-20 6:47am
   
I am sorry ,actually i just copied the wrong code ,
i have changed the code into (for int i=1;i
BillWoodruff 9-May-20 5:05am
   
+5 for infinite patience !
Richard MacCutchan 9-May-20 5:37am
   
Thanks Bill. Sometimes I just want to find an actual working solution for my own satisfaction.
BillWoodruff 9-May-20 9:35am
   
and that, Sir, is a gift to the "rest of us" !
Richard MacCutchan 9-May-20 11:38am
   
Maybe sometimes ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900