Wednesday, October 31, 2012

Operation is not valid due to the current state of the object.


Error : Operation is not valid due to the current state of the object.

Stack Trace:



[InvalidOperationException: Operation is not valid due to the current state of the object.]
   System.Web.HttpValueCollection.ThrowIfMaxHttpCollectionKeysExceeded() +2692482
   System.Web.HttpValueCollection.FillFromEncodedBytes(Byte[] bytes, Encoding encoding) +61
   System.Web.HttpRequest.FillInFormCollection() +148

[HttpException (0x80004005): The URL-encoded form data is not valid.]
   System.Web.HttpRequest.FillInFormCollection() +206
   System.Web.HttpRequest.get_Form() +68
   System.Web.HttpRequest.get_HasForm() +8743911
   System.Web.UI.Page.GetCollectionBasedOnMethod(Boolean dontReturnNull) +97
   System.Web.UI.Page.DeterminePostBackMode() +63
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +133

This error occurs when form fields are very large in numbers.By default, the maximum value of MaxHttpCollection is 1000.

Solution:

To solve this error, increase MaxHttpCollection value. Try adding the following setting in your web.config's <appsettings> block. 

<appSettings>
        <add key="aspnet:MaxHttpCollectionKeys" value="3000" />

 </appSettings> 

you can change the value accordingly as per your need. 

Thursday, October 18, 2012

Count No of Table,View,Indexes,Stored Procedure

Sometime we need to count the no of table/view/indexes/stored procedure in Database.
 --Returns Total No of User Defined Table
select count(*) cntTables from sysobjects where type = 'U'

--Returns Total No of User Defined View
select count(*) cntView from sysobjects where type = 'V'

 --Returns Total No of Index.You may need to further filter,
 -- depending on which types of indexes you want.
select count(*) cntIndex from sysindexes

--Returns No of Stored Procredure
select Count(*) cntProc from sys.procedures


--Return numbers of non clustered indexes on any table in entire database.
SELECT COUNT(i.TYPE) NoOfIndex,
[schema_name] = s.name, table_name = o.name
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.TYPE IN ('U')
AND i.TYPE = 2
GROUP BY s.name, o.name
ORDER BY schema_name, table_name

Tuesday, October 9, 2012

Delete Duplicate Rows from Multiple Tables

There are various ways for removing duplicate rows from  table. In my scenario there are three table which from where I have to delete duplicate rows. All these tables have relation. These tables are-:

Purchase_Rcv -: Keeps information about each purchase receive.
Purchase_RcvDet -: Keeps information about purchased product  for each purchase receive
BarcodeDet -: Keeps Barcode Detail for each received product
There is one more table that is -:
POS_STOCK-: As its name exhibits, keeps stock information

So I have to also update stock in this table.

So, to implement this I wrote a select statement which fetches all the rows of table without duplicacy and the query is :
SELECT min(PoRcvDet_Id) as id   FROM [Purchase].[vw_PurchaseRcvDet] group by [Product_code]

Here Product_Code is the column on which I want to remove duplicacy. So, I used group by on Product_Code which returns distinct rows based on Product_Code.

I used min() to keep first entry.One can use max() also.

 Then I wrote the Delete Command which deletes all rows which are not in the selected rows.


Delete FROM [Purchase].[BarcodeDet]
      WHERE fkPoRcvDetId not in
      (SELECT min(PoRcvDet_Id) as id   FROM [Purchase].[vw_PurchaseRcvDet] group by [Product_code] )
           
Delete FROM Purchase.Purchase_RcvDet
WHERE PoRcvDet_Id not in
      (SELECT min(PoRcvDet_Id) as id  FROM [Purchase].[vw_PurchaseRcvDet] group by [Product_code] )
           
Delete FROM Purchase.Purchase_Rcv
WHERE PoRcv_Id not in
      (SELECT min(fkPoRcv_Id) as id  FROM [Purchase].[vw_PurchaseRcvDet] group by [Product_code] )



Update Stock--:


UPDATE [dbo].[POS_STOCK]
   SET [Stock] = b.[Qty]
  from [Purchase].[Purchase_RcvDet] as b
  left outer join  [dbo].[POS_STOCK] on b.[fkProductId]=[dbo].[POS_STOCK].[fkProductId]



In my scenario POS_STOCK has same no of rows as in Purchase_RcvDet. Thats why I have used  left outer join.