Return All Dates of Selected Month

This article is dedicated to one of user who asked me how to return all dates of a selected month.
So, here is the function solving this purpose -:

CREATE FUNCTION GetAllDateOfMonth
(   
    @dbDate datetime
)
RETURNS @AllDates TABLE
(
 GenDate datetime not null
)
AS
BEGIN
    DECLARE @monthNo int;
    -- Set Month no of Selected Date
    SET @monthNo = datepart(MM, @dbDate);

    -- Set first day of month
    SET @dbDate = convert(datetime, convert(varchar,datepart(yy,@dbDate)) + '.' + convert(varchar,@monthNo) + '.01 00:00:00');

     WHILE datepart(MM,@dbDate) = @monthNo
     BEGIN
      INSERT INTO @AllDates VALUES (@dbDate);
      SET @dbDate = dateadd(dd, 1, @dbDate);
     END
   
    RETURN
END

And this is the implementation of this function -:

SELECT * FROM [dbo].[GetAllDateOfMonth] (GETDATE())

And the Output will be -:

Finding Duplicate Rows

Today I am going to describe the query which fetches duplicate rows.Its not a big task but when I started to think as a beginner I felt an urge to write about it.
This is the table structure I used for the query.Here "SD_Id"is unique where as other fields are repetitive.
CREATE TABLE [dbo].[SaleDispatch](
    [SD_Id] [bigint] IDENTITY(1,1) NOT NULL,
    [SO_Id] [bigint] NULL,
    [Date] [datetime] NULL,
    [TotAmt] [money] NULL,
   ) ON [PRIMARY]

I am going to find out the rows where  [SO_Id] is duplicate. We will use  COUNT() .
So as a beginner one can come up with this query -:
SELECT  [SO_Id], COUNT(*) AS dupeCount
FROM [dbo].[SaleDispatch]

And encountered by this error -:
Msg 8120, Level 16, State 1, Line 1
Column 'dbo.SaleDispatch.SO_Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This error occurs bcoz we are using COUNT() which is an aggregate function where as select list contains SO_Id which has no any aggregate function.So in this situation we have to use GROUP BY clause.Now the query will be -:
SELECT  [SO_Id], COUNT(*) AS dupeCount
 FROM [dbo].[SaleDispatch]
 GROUP BY [SO_Id]

This query returns all rows and we want duplicate only then we have to put condition.
SELECT  [SO_Id], COUNT(*) AS dupeCount
 FROM [dbo].[SaleDispatch]
 GROUP BY [SO_Id] 
 HAVING COUNT(*) > 1

Now we want other columns also then we have to use join.And now the final query arrives as this one -:
SELECT SD.[SD_Id],SD.[SO_Id],SD.[Date],SD.[TotAmt]
FROM [dbo].[SaleDispatch]  as SD  
inner join
( 
    SELECT  [SO_Id], COUNT(*) AS cnt
    FROM [dbo].[SaleDispatch]
    GROUP BY [SO_Id] 
    HAVING COUNT(*) > 1
) as SDC
on SD.SO_Id=SDC.SO_Id
order by SD.SO_Id

Barcode in Multiple Columns In RDLC

This article shows how to print barcode in rdlc in multiple columns. The final outcome of this article will be like this one -:

At first design the report than format it accordingly.I have used list control , Textbox and one Image control for this report.
Now, in this report three important things are present.
First one barcode, 2nd one Rupee symbol and the 3rd one is multiple column.
BARCODE :
Now for Barcode just install barcode font like 3o9 etc.And set the font of textbox to barcode font.One thing u must take care that the barcode is prefixed and suffixed by "*". For example if barcode is "000003" then it will be like "*000003*".

RUPEE SYMBOL :
Now for rupee symbol I have just put the image of it. Help for Image.
MULTIPLE COLUMNS :
For this u have to change columns in properties of Body.Rest is the matter of height n width of page and body.The height n width of body is for each column i.e. if here 3 columns are defined then finally page width will be of 3 inches.

Now, go to Report Properties.

Now, In this new window open the layout tab. And change the column to 3 and column spacing to 0.
In my scenario there is a scarcity of space thats why I have set column spacing to "0". You can change it a/c to ur need.

Then set the page width and height to ur need.Standard width=8.5in and height=11in. I have just saved my space by setting "0" to  left,right,top and bottom margin.
Now ur final design will look like this one :

When u run the report output will be like given image.To view the actual output u have to export it to PDF.

Image in RDLC

Show External Images In RDLC

This post is about "How to show images in RDLC?". 

Step 1 : In RDLC just drag n drop image from toolbox.

Step 2 : In property of Image change source to external and in value specify the path of image.

In path u have to put  "file:/// " before the absolute path for local resources and for Server "http://" .

Step 3 : In Code Behind we have to enable EnableExternalImages.

            ReportViewer1.LocalReport.EnableExternalImages = true;

Now, Run the report. If Image is not visible than we have to follow step 4.

Step 4 : We may need to bypass proxy settings to allow the external image to appear. Add following setting to web.config file.

 <system.net>
        <defaultProxy>
            <proxy proxyaddress ="http://proxyservername:80" usesystemdefault="False" bypassonlocal="True">    </proxy>
        </defaultProxy>
    </system.net>

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. 

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

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.