.Net Articles

Steps to create crystal reports in Asp.Net

Step1.
Create an aspx page :
Incase you need a date range add any, add in the optional parameters for the report like query by employee name,department name etc:


Query = "Select name,id from tablename where id=10"
Dim myConnection As New SqlConnection
myConnection.ConnectionString = Constr
Dim MyCommand As New SqlCommand
MyCommand.Connection = myConnection
MyCommand.CommandText = query
MyCommand.CommandType = CommandType.Text
Dim MyDA As New SqlDataAdapter
MyDA.SelectCommand = MyCommand
Dim myDS As New dsDaily
'Below tablename is the same name to be used in the dataset which we create
MyDA.Fill(myDS, "TABLENAME")
oRpt1.Load(AppPhyPath & "/folder\filename.rpt")
oRpt1.SetDataSource(myDS)
'Below is the code to create parameters in crystal reports
oRpt1.SetParameterValue("date1", ValDate1)
oRpt1.SetParameterValue("date2", ValDate2)
CrystalReportViewer1.ReportSource = oRpt1
CrystalReportViewer1.DataBind()
MyDA.Dispose()
myConnection.Close()
myConnection = Nothing


After adding the code above, create a dataset using the same query for what you have mentioned in the dataset(Not the schema one).Add in the query and get the fields in the dataset.

Once created add the same dataset in the crystal reports.
Below are the steps:
1)Create a blank report
2)Database fields -> create a new connection -> Project Data-> Ado.net datasets ->
choose your xsd file ie dataset file -> and click ok.
Once the dataset is added to the field explorer, drag and drop the fields as you require and design the same.Do formatting for the font, date etc...
3)About the crystal reports parameter, in the parameter fields -> add new -> create two date fields Date1, Date2 which is of datetype
Note: Parameter field Scenario is used when we are passing two date ranges choosed by the user and we need to display the same in the report ie .
Date from : 12/10/2009 Date to: 12/12/2009
4)Run your code and Voila the report works.....

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

WITH Exmple_CTE (myID, Name, addedDate)
AS
(
SELECT p.myID, p.Name, u.addedDate)
FROM users u innerjoin
profile p on u.myID=p.myID
)
SELECT *
FROM Exmple_CTE
ORDER BY myID ASC,Name ASC, addedDate ASC

GO

This query creates a CTE named Exmple_CTE that returns the name, category name,id and addedDate. Once the CTE has been defined, it must then immediately be used in a query. The query treats the CTE as if were a view or table in the system, returning the three fields defined by the CTE (ProductName, CategoryName, and UnitPrice).

CTE's are very helpful incase you want to do paging using stored procedure methods.

Sometimes there are situations, where we'll have to delete the duplicate records in a table.
CREATE TABLE Users
(
FirstName nvarchar(50),
LastName nvarchar(50)
)
GO

INSERT INTO users (Fname, Lname) VALUES (N'Tarana',N'Abbas')
INSERT INTO users (Fname, Lname) VALUES (N'Jane',N'Eyre')
INSERT INTO users (Fname, Lname) VALUES (N'Julia',N'Fonda')
INSERT INTO users (Fname, Lname) VALUES (N'Jane',N'Eyre')
Here "Jane Eyre" is a duplicate record.

Identify which are the rows duplicating:
SELECT Fname, Lname, count(*)
FROM users
GROUP BY Fname, Lname
HAVING count(*) > 1

Methods to delete the duplate records:
Method1:
SET ROWCOUNT

SET ROWCOUNT 2
DELETE FROM Users WHERE FirstName = N'Jane' AND LastName = N'Eyre'
SET ROWCOUNT 0
(1 row(s) affected)
Above is incase of manual deletion of the row. Say incase of automatic deletion, we'll have to open the cursor , fetch values from the table and delete one by one by iterating.
Note: while you are declaring the cursor for dublicate rows, you should select the count of identical rows minus one, since we want one of the duplicated records to be existing in the table after delete processes.
ie:

DECLARE @Count int
DECLARE @fname nvarchar(50)
DECLARE @lname nvarchar(50)

DECLARE duplicate_cursor CURSOR FAST_FORWARD FOR
SELECT fname, lname, Count(*) - 1
FROM Users
GROUP BY fname, lname
HAVING Count(*) > 1

OPEN duplicate_cursor

FETCH NEXT FROM duplicate_cursor INTO @fname, @lname, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM Users WHERE fname = @Fname AND lname = @Lname
SET ROWCOUNT 0

FETCH NEXT FROM duplicate_cursor INTO @Fname , @Lname, @Count
END

CLOSE duplicate_cursor
DEALLOCATE duplicate_cursor
Note:***** Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server.
Method 2:
TOP

DELETE TOP(@Count) FROM Users WHERE FirstName = @fname AND LastName = @lname
This statement can be used inside the cursor, for deleting the values atonce.

Method 3:
Adding Identity column:
ALTER TABLE users ADD Id int IDENTITY(1,1)
and then use direct delete statement using id value
DELETE FROM users WHERE Id IN (2,4)

ALternatively we can use Common Table Expression:
WITH Duplicates_CTE(Fname, Lname, Id)
AS
(
SELECT Fname, Lname, Min(Id) Id
FROM users
GROUP BY Fname, LastName
HAVING Count(*) > 1
)
DELETE FROM users
WHERE Id IN (
SELECT users.Id
FROM Users
INNER JOIN Dublicates_CTE
ON users.Fname= Dublicates_CTE.Fname
AND users.Lname= Dublicates_CTE.Lname
AND users.Id <> Dublicates_CTE.Id
)
Method 4:
Say tht you want to keep according to the inserted date first, then use ROW_NUMBER() OVER (PARTITION BY columnname1, columnname2 ORDER BY columnname3 DESC) syntax
WITH [CTE DUPLICATE] AS
(
SELECT
RN = ROW_NUMBER() OVER (PARTITION BY name ORDER BY addedDate DESC),
Id, Fname, Lname, addedDate
FROM users
)
DELETE FROM [CTE DUPLICATE] WHERE RN > 1

Incase you dont know what is CTE ie Common Table Expression, please look forward for my next article regarding the same.

In SQL Server2005, we can use row_number which returns the sequential number of a row ,starting at 1 for the first row in each partition.
SELECT row_number() Over (order by profile_date desc)
AS PageNumber,profile_id
FROM profiles

Result:
PageNumber profile_id
1 1235
2 1200
3 1189

We all have instances where we want to change the url without reloading the page. its easy and simple to do. just append '#' to the url
ie:

 window.location.hash = "test";
it will add http://whiteboard1900.blogspot.com/#test
at the end of the url.
or alternatively use:
window.location.href=window.location.href+"#testval=1";

In Some cases we need to pass values from aspx page to aspx.vb or aspx.cs page for alternating values, calculation etc (according to the scenario of the program) to display in the html page


<@asp:TextBox ID="txtname" runat="server" Text='<%# GetStatus(DataBinder.Eval(Container.DataItem, "status" )) %>' >

Declare a public funtion called GetStatus:
Here status value from the database is of type integer, so declare the same integer type in the function
Public Function GetStatus(ByVal type As Integer) As String
Dim ret As String = ""
Select Case type
Case 0
ret = "Complaints"
Case 1
ret = "Enquiry"
Case 2
ret = "Suggestions"
Case 3
ret = "Comments"
End Select

GetStatus = ret
End Function

Get the HTMLInputFile contenttype to get the extension and validate the same which is stored in an array..
Note: Add in all the image types available into the array

Dim extension as string =MyFile.PostedFile.ContentType
Dim IsImage As Boolean = False
Dim estr() As String = {"image/gif", "image/pjpeg", "image/bmp", "image/jpeg", "image/png", "image/jpg"}
Dim i As Integer
For i = 0 To estr.Length - 1
If estr(i) = extension Then
IsImage = True
End If
Next