Prompt message if Excel column has some value in asp.net c#

Question

I have a criteria where user uploads excel. So in that I want to check if column Is Replacement (Y/N) has values as Y then I want Replacement SAP ID cannot be blank. If the column is blank then prompt a alert message. Below is the image for the same.

System.Data.OleDb.OleDbConnection connExcel = new System.Data.OleDb.OleDbConnection(conStr);
            System.Data.OleDb.OleDbCommand cmdExcel = new System.Data.OleDb.OleDbCommand();
            System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
            cmdExcel.Connection = connExcel;
            connExcel.Open();

            System.Data.DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            System.Data.DataTable dtExcelColumnsTable = connExcel.GetSchema("Columns");
            string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Replace('\'', ' ').Trim();  //nadeem
            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
            oda.SelectCommand = cmdExcel;
            oda.Fill(dtExcelRows);

I take all columns in dtExcelRows

Do you need a single alert after finding the first failure or a message with list of all rows that don't comply? — user8172679
– user8172679, Commented Apr 13, 2022 at 17:18
finding the first failure and after that stop the code to proceed — HEEN
– HEEN, Commented Apr 13, 2022 at 19:59

marc_s · Accepted Answer · 2022-07-13 13:32:35Z

5

+25

I think that It's good to use your Business Exception and when the Replacement SAP ID is blank you should raise the Exception.

foreach(var item in dtExcelRows["ColumnName"]){
    if(string.IsEmptyOrNull(item){
           throw new HttpException((int)HttpStatusCode.BadRequest, "SAP ID is Empty.");
      }
}

I just write some pseudo Code I'm not sure about reading data from a column.

And if you handle BadRequest exception in the front-end, you can prompt an alert message to the client.

edited Jul 13, 2022 at 13:32

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 14, 2022 at 16:37

Mohammad Azizkhani

3424 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HEEN Over a year ago

getting error as cannot apply indexing with to an expression of type datatable

wilpeers · Accepted Answer · 2022-04-19 15:42:15Z

Index

Example answer is almost at the end - just above references section. Hope it helps you.

Unclear scope

It is hard to grasp exact tech stack You are referring to. Are you limited to OleDb? or can you use any nuget package? What are the restrictions / technical base for "promt a alert message" ? webforms? ASP.NET 2.0? I mean, is it an AJAX control toolkit alert or a simple

window.alert("message");

type Javascript ? maybe one within jQuery which should only open after the page ( if rendered ) is rendered :

 jQuery(document).ready(function() {window.alert("message");})

??

Tip how to get a clearer view about what is happening

Basically at

 cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dtExcelRows);

it seems for me that you actually fill the dataset with whole sheet data, not only the names from the first row

If you have Visual Studio, write something trivial just after the oda.Fill line, like

String t= "";

place a breakpoint on this trivial line (or any line with code just after that Fill one

then press F5.

After IISExpress is launched, in the page go to the upload scenario where this code will run, and then when debugger pauses on the debug point you just set,

select word dtExcelRows then right click Add to Watch

lower you will see Watch toolbar there near the dtExcelRows is magnifiying glass.

That is DataSet visualizer. Click on that and you will see what was filled actually inside the dtExcelRows object

Now you can figure out what exactly you need.

Answer to Your question

Unfortunately unless I know more exact details about approximate technical limits (version of ASP.NET or other technology, it will be long to try to write all the possible variants how this could be implemented.)

Therefore I will limit myself to a "pseudocode"

 // i think you could alse refer to an index
//(dtExcelRows as DataSet).Tables[0].Rows[0].ToString();
DataSet dtExcelRowsShoulBeDataSet = (dtExcelRows as DataSet);

if (dtExcelRowsShoulBeDataSet != null) {
  if (dtExcelRowsShoulBeDataSet.Tables.Length > 0) {
    int numberOfColumns = dtExcelRowsShoulBeDataSet.Tables[0].Columns.Count;
    String columnNameDoYouNeedItOrNumberIsEnoug_Question_Column = "";
    String columnNameDoYouNeedItOrNumberIsEnoug_REPLACEMENTSAPID_Column = "";

    foreach (DataColumn column in dtExcelRowsShoulBeDataSet.Tables[0].Columns) {
      if (row [column]
              .ToString()
              .Trim()
              .StartsWith("Is Replacement")) {
        columnNameDoYouNeedItOrNumberIsEnoug_Question_Column =
            column.ColumnName;
      } else if (row [column]
                     .ToString()
                     .Trim()
                     .StartsWith("Replacement SAP")) {
        columnNameDoYouNeedItOrNumberIsEnoug_REPLACEMENTSAPID_Column =
            column.ColumnName;
      }
    }
//now you know the column names, so can use them alike dictionary indexes
    for (int i = 1; i < dtExcelRowsShoulBeDataSet.Tables[0].Rows.Count; i++) {
      if (row [columnNameDoYouNeedItOrNumberIsEnoug_Question_Column]
                  .ToString()
                  .Trim()
                  .ToUpper() != "Y" &&
          row [columnNameDoYouNeedItOrNumberIsEnoug_Question_Column]
                  .ToString()
                  .Trim()
                  .ToUpper() != "N") {
        // later .net versions you can use IsNullOrWhiteSpace method here
        if (String.IsNullOrEmpty(
                row
                [columnNameDoYouNeedItOrNumberIsEnoug_REPLACEMENTSAPID_Column]
                    .ToString()
                    .Trim())) {
          // if AJAX then registerscript, addstartupscript etc..
          Response.Write(
              "<scipt type=\"text/javascript\">window.alert(\"hey, you forgot to specify the Replacement SAP ID at Excel row Nr " +
              (i) + " !\")</script>");
          Response.End();
        }
      }
    }
  }
}

References

Microsoft .NET C# documentation pages.

Just add the viewFallbackFrom parameter (or simply edit the view= parameter from current default 6 to your older tech stack version. So you can currently do in any Microsoft documentation site to see what is avaliable for your version_

https://learn.microsoft.com/en-us/dotnet/api/system.data.datacolumn?view=net-6.0&viewFallbackFrom=net-3.0

I suppose that comment was supposed to be here, right? What is your .NET version then? Because Microsoft documentation shows that indexing vas apply-able to the DataTablesCollection since its invention.... user-images.githubusercontent.com/85872382/… Are you sure that error is about the indexing on a DataTables? Its plural, with ending s, That is System.Data.DataTableCollection from Microsoft .Net library ("Default"). Do you have any overrides, extensions that could have same name and are being used instead of the Microsoft
System.Data.DataTable dtExcelSchema - is a Data Table, so it has rows and columns ⠀⠀System.Data.DataTable dtExcelColumnsTable - also is a Data Table, so it has rows and columns, no indexing without first typig which collection. Rows[0].⠀⠀⠀⠀dtExcelRows <- is a DataSet - consists of DataTables ⠀⠀⠀⠀(whom I applied the indexing) and relations between them. In this case there is one table the [0] one. That is why I applied indexing to the DataSet Tables collection. Not to any of the DataTables (which has Rows collection and Columns collection) Only thre Dataset may have a Collection of DataTables.

Collectives™ on Stack Overflow

Prompt message if Excel column has some value in asp.net c#

2 Answers 2

1 Comment

Index

Unclear scope

Tip how to get a clearer view about what is happening

Answer to Your question

References

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Index

Unclear scope

Tip how to get a clearer view about what is happening

Answer to Your question

References

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related