question

Elado avatar image
0 Votes"
Elado asked Elado edited

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

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
 )






sql-server-generaldotnet-csharpdotnet-aspnet-general
table.png (4.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The original code base you shared on GitHub contains a lot logical bugs. For example, the original code checks if dataset.Tables["Products"].Rows.Count has row but you never named a table "Products".

Please use Visual Studio debug tools to set a break point and single step through your code. The bugs should be very obvious if you take the time to run your code through the debugger.

Can I make a suggestion? Consider using a SqlRepeater to fill a strongly typed object rather than filling a DataSet in the DAL. Filling a DataSet/DataTable and converting to a List<T> does not make a lot sense since you can simply create the List<T> directly in the DAL.

Keep in mind, your BLL does not contain actual business logic. Converting a DataSet/DataTable to JSON is not business logic. Simply return the strong type from the DAL. After fixing the DAL design of course.

 public static List<Product> ProductList()
 {
     return DAL.GetProducts();
 }

ASMX WebMethod framework is designed to serialize the return type. You are serializing a JSON string which makes little sense. Plus, this design hides the actual type form the WSDL (Web Service Definition Language) clients use to generate the service proxy.

The WebMethod pattern should look similar to the BLL.

 [WebMethod]
 public List<Product> ProductList()
 {
     return BLL.ProductList();
 }

I strongly recommend you start follow standard patterns and practices found in any beginning level tutorial rather than making up your own patterns.

0 Votes 0 ·

Hi @AgaveJoe
I got few things to clear and to understand from your comment:

1) The DAL GetProducts() function return DataTable It can't return a list, and don't know how. I take the table data from the SQL.

2) What do you mean by - "never named a table "Products"". where should I name it ?

3) I return a JSON for my front-end JavaScript.

4) What is SqlRepeater ? there is no such a thing.

What I'm doing here I learned in college.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered Elado edited

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
 }
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @karenpayneoregon
object productName = dataRow["ProductDescription"] suppose to give an empty value as you can see in the SQL screenshot I gave above in my question
But it doesnt give a null and it doesn’t print to the console nothing /:

In your second foreach get an error -

  System.NullReferenceException: '
  System.Data.DataTableCollection.this[string].get returned null.


0 Votes 0 ·
YijingSun-MSFT avatar image
1 Vote"
YijingSun-MSFT answered Elado edited

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.

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

My GitHub - https://github.com/Eladossa/ShopWebSiteForGit.git
And email notifiaction are on (!)

Hi @YijingSun-MSFT, I wish to accept your answer but after we solve my problem and I will realize what was wrong & why.

So I did named my adapter.Fill(dataset, "Products") and It removed some the errors.
But I still gets this erorr -

System.InvalidCastException "not legal"
HResult=0x80004002





0 Votes 0 ·

And where do you get this error? On which statement? I assume that you have traced this in the debugger?

0 Votes 0 ·
Elado avatar image Elado ErlandSommarskog ·

I solved this problem by casting the decimal value from the SQL to decimal and float -

  (float)(decimal)ds.Tables[0].Rows[i]["Price"]`

and naming the dataset



0 Votes 0 ·
Show more comments