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

Question

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
)

Answer 1

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.

Answer 2

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
}