Question 87177 in ASP.NET Question posted in
The official documentation can be found here.

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

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.

excel

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search