Getting webservice error - DataTable table, Boolean preserveChanges, MissingSchemaAction missingSchemaAction

daowdos 271 Reputation points
2021-08-02T14:53:54.347+00:00

What does this error means - " MissingSchemaAction missingSchemaAction" ?
Happens while trying to active webMethod - webService; the other function is working well,
only calling the SQL "view" is giving an error.

All the error:

System.ArgumentNullException: Value cannot be null

System.Data.DataSet.Merge(DataTable table, Boolean preserveChanges, MissingSchemaAction missingSchemaAction)
System.Data.DataSet.Merge(DataTable table)
Business_Logic_Layer.BLL.ProductList() C:\Users\Ht\Desktop\ShopWebSite1\Business_Logic_Layer\BLL.cs: 37
WebService.ProductList() C:\Users\Ht\Desktop\ShopWebSite1\WebSite1\App_Code\WebService.cs:38

Here is my function from there I get this error -
BLL.cs

public static string ProductList()
        {
            DataTable productsTable = DAL.GetProducts();
            DataSet ds = new DataSet();
            ds.Merge(productsTable);
            List<Product> productsList = new List<Product>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count ; i++)
                {
                    productsList.Add(new Product((int)ds.Tables[0].Rows[i]["ProductID"], // Error falls here System.InvalidCastException: 

                        (string)ds.Tables[0].Rows[i]["ProductName"],
                        (string)ds.Tables[0].Rows[i]["CategoryName"], (float)ds.Tables[0].Rows[i]["Price"],
                        (int)ds.Tables[0].Rows[i]["Stock"], (string)ds.Tables[0].Rows[i]["ProductDescription"],
                        (string)ds.Tables[0].Rows[i]["ProductOverview"],
                        (string)ds.Tables[0].Rows[i]["ProductImage"])); 
                }
            }
            return new JavaScriptSerializer().Serialize(productsList);
        }

DAL.cs -

public static DataTable GetProducts()
        {
            SqlConnection conn = new SqlConnection(connetionString);
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command;
            try
            {
                conn.Open();
                command = new SqlCommand("SELECT * FROM AllProducts", conn);
                adapter.SelectCommand = command;
                DataSet dataset = new DataSet();
                adapter.Fill(dataset);  // Should adapter.Fill has to have scrTable string ? didn't figure why

                if (dataset.Tables["Products"].Rows.Count != 0)
                {
                    return dataset.Tables["Products"];
                }
                return null;

            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }

            }
        }

** NEW 3/8 EDIT:**

When adding string to the Fill() method adapter.Fill(dataset, **"products"**).
I get a shorter error:

System.InvalidCastException: .
Business_Logic_Layer.BLL.ProductList() C:\Users\Ht\Desktop\ShopWebSite1\Business_Logic_Layer\BLL.cs:45
WebService.ProductList() C:\Users\Ht\Desktop\ShopWebSite1\WebSite1\App_Code\WebService.cs:38

I checked all the types - strings, ints and flaot are the equal to the SQL table types.

CREATE TABLE [Products]
(
   ProductID INT IDENTITY(1,1) PRIMARY KEY,
   ProductName NVARCHAR(60),
   CategoryName NVARCHAR(20),
   Price DECIMAL(4,2),
   Stock INT NULL DEFAULT NULL,
   ProductDescription NVARCHAR(75) NULL DEFAULT NULL,
   ProductOverview NVARCHAR(500) NULL DEFAULT NULL,
   ProductImage NVARCHAR(max) NULL DEFAULT NULL
)
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,318 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,911 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,391 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Yijing Sun-MSFT 7,071 Reputation points
    2021-08-03T03:25:20.753+00:00

    Hi @Elado ,
    I run and test your codes and maybe I have found your problem. What 's your Products:

    dataset.Tables["Products"]  
    

    I think you need to add a name when you do the fill operatation:

     public static DataTable GetProducts()  
            {  
                SqlConnection conn = new SqlConnection();  
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;  
                SqlDataAdapter adapter = new SqlDataAdapter();  
                SqlCommand command;  
                try  
                {  
                    conn.Open();  
                    command = new SqlCommand("SELECT * FROM Test", conn);  
                    adapter.SelectCommand = command;  
                    DataSet dataset = new DataSet();  
                    adapter.Fill(dataset, "Products");  // add a name  
      
                    if (dataset.Tables["Products"].Rows.Count != 0)  
                    {  
                        return dataset.Tables["Products"];  
                    }  
                    return null;  
      
                }  
                catch (Exception e)  
                {  
                    return null;  
                }  
                finally  
                {  
                    if (conn != null && conn.State == ConnectionState.Open)  
                    {  
                        conn.Close();  
                    }  
      
                }  
            }  
    

    Best regards,
    Yijing Sun


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  2. Karen Payne MVP 35,201 Reputation points
    2021-08-02T15:46:55.707+00:00

    Perhaps a column value is null which may be a constraint issue which can be checked e.g.

    foreach (DataRow dataRow in dataSet.Tables[tableName].Rows)
    {
        object productName = dataRow["ProductName"];
        if (productName == DBNull.Value)
        {
            Console.WriteLine("Null - do not try and access");
        }
        else
        {
            Console.WriteLine("Not null - ok to use");
        }
    }
    
    
    bool tableHasNull = false;
    foreach (DataRow row in dataSet.Tables[tableName].Rows)
    {
        foreach (DataColumn col in dataSet.Tables[tableName].Columns)
        {
            //test for null here
            if (row[col] == DBNull.Value)
            {
                tableHasNull = true;
            }
        }
    }
    
    if (tableHasNull)
    {
        //handle null in table
    }