Tech Tips: 2016

Friday, December 30, 2016

Auto-Playing Slideshow

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication2.Account.WebForm2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>

<style>
#slideshow {
margin: 30px auto;
position: relative;
width: 640px;
height: 480px;
padding: 5px;
box-shadow: 0 0 20px rgba(0, 0, 0, 0.4);
}

#slideshow > div {
position: absolute;
top: 5px;
left: 5px;
right: 5px;
bottom: 5px;
}
</style>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.5.2/jquery.min.js"></script>
<script type="text/javascript" charset="utf-8">

$(document).ready(function () {
$("#slideshow > div:gt(0)").hide();

setInterval(function () {
$('#slideshow > div:first')
.fadeOut(1000)
.next()
.fadeIn(1000)
.end()
.appendTo('#slideshow');
}, 3000);
});

</script>
</head>
<body>
<form id="form1" runat="server">
<div id="slideshow">
<div>
<img src="../Images/1.jpg" />
</div>
<div>
<img src="../Images/2.jpg" />
</div>
<div>
<img src="../Images/3.jpg" />
</div>
<div>
<img src="../Images/4.jpg" />
</div>
</div>
</form>
</body>
</html>

Simple Auto-Playing Slideshow
Simple jQuery Slideshow

Thursday, November 24, 2016

Adding a grid view filter (Part II)

Add the following java scripts and styles in the header section.

[code] [/code]

[code] [/code]

[code] [/code]

Add this code for create the grid.

<div id="example_wrapper" class="dataTables_wrapper form-inline dt-bootstrap4">
<div class="row">
<div class="col-md-6">
<div class="dataTables_length" id="example_length"></div>
</div>
<div class="col-md-6">
<div id="example_filter" class="dataTables_filter">
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
<table id="tbAllStockBal" class="table table-striped table-bordered dataTable" role="grid" aria-describedby="example_info" style="width: 100%;">
<thead>
<tr role="row">
<th class="sorting_asc" tabindex="0" aria-controls="example" rowspan="1" colspan="1" aria-label="Position: activate to sort column descending" style="width: 0px;" aria-sort="ascending">Txn Date</th>
<th class="sorting" tabindex="0" aria-controls="example" rowspan="1" colspan="1" aria-label="Name: activate to sort column ascending" style="width: 0px;">Ref No</th>
<th class="sorting" tabindex="0" aria-controls="example" rowspan="1" colspan="1" aria-label="Office: activate to sort column ascending" style="width: 0px;">Memo</th>
<th class="sorting" tabindex="0" aria-controls="example" rowspan="1" colspan="1" aria-label="Age: activate to sort column ascending" style="width: 0px; text-align: right;">Line Description</th>
<th class="sorting_asc" tabindex="0" aria-controls="example" rowspan="1" colspan="1" aria-label="Position: activate to sort column descending" style="width: 0px;" aria-sort="ascending">Line Received</th>
<th class="sorting" tabindex="0" aria-controls="example" rowspan="1" colspan="1" aria-label="Name: activate to sort column ascending" style="width: 0px;">Line Payment</th>
<th class="sorting" tabindex="0" aria-controls="example" rowspan="1" colspan="1" aria-label="Office: activate to sort column ascending" style="width: 0px;">Total Received</th>
<th class="sorting" tabindex="0" aria-controls="example" rowspan="1" colspan="1" aria-label="Age: activate to sort column ascending" style="width: 0px; text-align: right;">Total Payment</th>
</tr>
</thead>
<tbody>
<%=getPayments()%>
</tbody>
</table>
</div>
</div>
</div>

In code behind, create the getPayments() method as below

[code] public string getPayments() { string data = ""; try { DataTable dt = searchPaymentService.getPayments(); if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; string TxnDate = Convert.ToDateTime(dr["TxnDate"]).ToString("dd-MM-yyyy"); string RefNo = dr["RefNo"].ToString(); string Memo = dr["Memo"].ToString(); string LineDescription = dr["LineDescription"].ToString(); string LineReceived = dr["LineReceived"].ToString(); string LinePayment = dr["LinePayment"].ToString(); string Received = dr["Received"].ToString(); string Payment = dr["Payment"].ToString(); [/code]

data += "<tr><td>" + TxnDate + "</td><td>" + RefNo + "</td><td>" + Memo + "</td><td>" + LineDescription + "</td><td>" + LineReceived + "</td><td>" + LinePayment + "</td><td>" + Received + "</td><td>" + Payment + "</td></tr>";

[code] } } return data; } catch (Exception ex) { return data; } [/code]

Bootstrap 3

Load multiple RDLC reports at the same time

When there are multiple RDLC reports and you want to load data to that reports at the same time, set AsyncRendering="false" in every report without one report.

Thursday, November 17, 2016

Find the index of current row in Grid View

Assume there is a control within a grid view template field. On the TextChanged event, we can get the selected index as below.

[code]
int index = ((GridViewRow)(((TextBox)sender).Parent.Parent)).RowIndex;
[/code]

Wednesday, November 16, 2016

Format RDLC report to export to an excel, group wise with expanded data

Bind data to a dataset.
Use a matrix to present the data.
If you want expanded data within the group, the highlighted check box should be unchecked.

If you want to export this report to an excel sheet wise by name, it can be done by changing page break options under Group Properties.

Format RDLC report to export to an excel, group wise

Show report header on all work sheets

Click that small drop down arrow at the right corner of column groups as below.

Select the first static member under Row Groups, and set "KeepWithGroup" as After and "RepeatOnNewPage" as True.

This will now copy the headers on all worksheets.

Reference: Generate Multiple Worksheets by Groups in SSRS Exported Excel Spreadsheets

Thursday, October 27, 2016

Format RDLC report to export to an excel, group wise

Bind data to a dataset.

Use a matrix to present the data.

If we want to export this report to an excel sheet wise by name, it can be done as follows.
Right click on the "Name" in Row groups and select Group Properties.
Mark the check box as follows.

After exporting to an excel it will be like this.

If we want to get the "Name" as sheet name, instead of Sheet1, Sheet2....it can be done with the following simple modification.
First select the "Name" group in the Row Groups pane so that its properties are displayed in the Properties pane. In the Properties pane, locate the Group > PageName property and specify the following expression:

[code] =Fields!Name.Value [/code]

Result will be like this.

Where The Sheets Have A Name

Format report based on conditions (SSRS) (Part2)

Assume that there are multiple datasets, and we want to do a calculation in a textbox using multiple values from multiple datasets.

Add a textbox to the report, and write the expression as fallowing.

[code]
=First(Fields!StartersIssueValue.Value, "StartersIssue")- First(Fields!StartersReturnValue.Value, "StartersReturn") -First(Fields!StartersClosingValue.Value, "StartersClosing") [/code]

When getting the total of a particular value, it can be written like this.

[code] =Sum(Fields!Value.Value, "Cost") [/code]

Change the background color based on a condition

Select the table row and set background properties as follows.

[code]
=SWITCH(Fields!Font.Value ="1B", "#dcf9db",Fields!Font.Value ="1N", "#dcf9db", Fields!Font.Value="2B", "#cee9c3",Fields!Font.Value="2N", "#cee9c3")
[/code]

Result will be like this.

Data Driven Colored Text for Reporting Services Reports

Wednesday, October 12, 2016

Using the JQuery Select2 with dropdown lists

Download select2 style sheets and java scripts. Add them in the header of the page.

[code]
<script src="../Scripts/select2.js"></script>
<script src="../Scripts/select2.min.js"></script>
<link href="../Styles/select2.css" rel="stylesheet" />
<link href="../Styles/select2.min.css" rel="stylesheet" />

<asp:DropDownList ID="ddlSupplier" runat="server" CssClass="form-control" Width="200px" AutoPostBack="True"></asp:DropDownList>
[/code]

Add the following java script to apply the autocomplete option.

[code]
<script>
function pageLoad() {
$("#" + "<%=ddlSupplier.ClientID%>").select2({
placeholder: "Select Supplier",
allowClear: true
});
}
</script>
[/code]

When the dropdownlist is in a gridview, some changes should be done in the above javascript.

[code]
<script>
function pageLoad() {
$("#<%=GridView1.ClientID %> select[id*='ddlItem']").select2({
placeholder: "Select an Item",
allowClear: true
});
}
</script>
[/code]

[code]
<asp:TemplateField HeaderText="Item">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("ItemName") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlItem" runat="server" AutoPostBack="True" CssClass="form-control input-sm" OnSelectedIndexChanged="ddlItem_SelectedIndexChanged" Width="200px">
</asp:DropDownList>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="lblItem" runat="server" Text='<%# Bind("ItemName") %>'></asp:Label>
</ItemTemplate>

</asp:TemplateField>
[/code]

Links: Using the Select2 JQuery Plugin with Bootstrap 3

Select2 3.5.3

Tuesday, October 4, 2016

Save and retrieve images from database using C# in ASP.NET

Create the interface with a file uploader and an image like this.

Create a table to save images.

We'll see how to save an image to the database. Add the following html code to the create the interface.

<head runat="server">
<title></title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">

function readURL(input) {
if (input.files && input.files[0]) {
var reader = new FileReader();
reader.onload = function (e) {
$('#<%=Image1.ClientID%>').prop('src', e.target.result)
}
reader.readAsDataURL(input.files[0]);
}
}
$("#uploadFile").change(function () {
readURL(this);
});
</script>
</head>
<body>
<form id="form1" runat="server">

<table>
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="Attach Image:" Style="font-weight: 700"></asp:Label>
</td>
<td>
<asp:FileUpload ID="uploadFile" runat="server" onchange="readURL(this)" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Image ID="Image1" runat="server" Height="100px" Width="100px" />
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnSave" runat="server" Text="Save" Width="75px" ValidationGroup="Save" OnClick="btnSave_Click" />
</td>
<td>
<asp:Button ID="btnRetrive" runat="server" Text="Retrive" OnClick="btnRetrive_Click" />
</td>
</tr>
</table>
</form>

</body>

After attaching an image, it shows the preview in the image control.

The image saving code is as follows.

protected void btnSave_Click(object sender, EventArgs e)

{

int imageId = 1;//Table's first row - default image

if (uploadFile.PostedFile.FileName != string.Empty)

{

imageId = imageUpload();

}

Image1.ImageUrl = "Image.aspx?ImageID=" + imageId + "";

}

private int imageUpload()

{

string filePath = uploadFile.PostedFile.FileName;

int imageId = 1;

string filename = Path.GetFileName(filePath);

string ext = Path.GetExtension(filename);

ext = ext.ToLower();

string contenttype = String.Empty;

switch (ext)

{

case ".jpg":

contenttype = "image/jpg";

break;

case ".png":

contenttype = "image/png";

break;

case ".gif":

contenttype = "image/gif";

break;

default:

break;

}

if (contenttype != String.Empty)

{

Stream fs = uploadFile.PostedFile.InputStream;

BinaryReader br = new BinaryReader(fs);

byte[] bytes = br.ReadBytes((Int32)fs.Length);

imageId = page6Service.UpdateImage(filename, contenttype, bytes);

}

return imageId;

}

We can retrieve a particular saved image with the following code

protected void btnRetrive_Click(object sender, EventArgs e)

{

Image1.ImageUrl = "Image.aspx?ImageID=1";

}

To retrieve an image like this, first we have to create a web page as Image.aspx with an empty form. In the Page_Load event of that page add this code.

protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["ImageID"] != null)
{
try
{
DataTable dt = page6Service.retrieveImage(Convert.ToInt32(Request.QueryString["ImageID"]));
if (dt.Rows.Count > 0)
{
byte[] bytes = (byte[])dt.Rows[0]["Data"];
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = dt.Rows[0]["ContentType"].ToString();
Response.AddHeader("content-disposition", "attachment;filename=" + dt.Rows[0]["Name"].ToString());
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
}
catch (Exception ex)
{
throw ex;
}
}
}

Monday, October 3, 2016

Adding date and time picker

[code]
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<script type="text/javascript" src="//cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
<script type="text/javascript" src="//cdn.jsdelivr.net/bootstrap.daterangepicker/2/daterangepicker.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.jsdelivr.net/bootstrap.daterangepicker/2/daterangepicker.css" />
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<div class="col-md-2">
<asp:TextBox ID="txtSessiontime" runat="server" CssClass="form-control" Width="200px" />
<script type="text/javascript">
$(function () {

$("[id$=txtSessiontime]").daterangepicker({
singleDatePicker: true,
timePicker: true,
timePickerIncrement: 15,
locale: {
format: 'DD-MM-YYYY h:mm A'
}
});
});
</script>
</div>
[/code]

Adding a grid view filter (Part I)

First add the grid view to the page.

Add the following java script in the header section

[code]
<script>
function filter2(phrase, _id) {
var words = phrase.value.toLowerCase().split(" ");
var table = document.getElementById(_id);
var ele;
for (var r = 1; r < table.rows.length; r++) {
ele = table.rows[r].innerHTML.replace(/<[^>]+>/g, "");
var displayStyle = 'none';
for (var i = 0; i < words.length; i++) {
if (ele.toLowerCase().indexOf(words[i]) >= 0)
displayStyle = '';
else {
displayStyle = 'none';
break;
}
}
table.rows[r].style.display = displayStyle;
}
}
</script>
[/code]

Then add a search text box with this html code.

[code]
<div class="input-group add-on" style="width: 60%">
<input name="filter" onkeyup="filter2(this, 'GridView1')" class="form-control" placeholder="Search" type="text" />
<div class="input-group-btn">
<asp:LinkButton ID="LinkButton1" runat="server" CssClass="btn btn-default"><i aria-hidden="true" class="glyphicon glyphicon-search"></i></asp:LinkButton>
</div>
</div>
[/code]

This filters texts in any column.

Full code

[code]
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<script>
function filter2(phrase, _id) {
var words = phrase.value.toLowerCase().split(" ");
var table = document.getElementById(_id);
var ele;
for (var r = 1; r < table.rows.length; r++) {
ele = table.rows[r].innerHTML.replace(/<[^>]+>/g, "");
var displayStyle = 'none';
for (var i = 0; i < words.length; i++) {
if (ele.toLowerCase().indexOf(words[i]) >= 0)
displayStyle = '';
else {
displayStyle = 'none';
break;
}
}
table.rows[r].style.display = displayStyle;
}
}
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<div class="input-group add-on" style="width: 60%">
<input name="filter" onkeyup="filter2(this, 'GridView1')" class="form-control" placeholder="Search" type="text" />
<div class="input-group-btn">
<asp:LinkButton ID="LinkButton1" runat="server" CssClass="btn btn-default"><i aria-hidden="true" class="glyphicon glyphicon-search"></i></asp:LinkButton>
</div>
</div>
<br />
<asp:GridView ID="GridView1" ClientIDMode="Static" runat="server" Width="60%" CssClass="table table-striped table-bordered table-hover" AutoGenerateColumns="False"
DataKeyNames="CustomerID" EmptyDataText="There are no data records to display."
Height="16px" AllowPaging="True">
<Columns>
<asp:BoundField DataField="Name" HeaderText="Patient Name" SortExpression="Name">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle Width="170px" />
</asp:BoundField>
<asp:BoundField DataField="Address" HeaderText="Address" HeaderStyle-HorizontalAlign="Right" SortExpression="Address" HeaderStyle-CssClass="visible-lg" ItemStyle-CssClass="visible-lg">
<ControlStyle CssClass="gridViewHeader" />
<HeaderStyle Width="450px" HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="left" />
</asp:BoundField>
<asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" HeaderStyle-CssClass="visible-lg" ItemStyle-CssClass="visible-lg">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle Width="100px" />
</asp:BoundField>
<asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" ItemStyle-CssClass="hidden-xs" HeaderStyle-CssClass="hidden-xs">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle Width="100px" />
</asp:BoundField>
</Columns>
</asp:GridView>
</asp:Content>

[/code]

Sunday, October 2, 2016

Format report based on conditions (SSRS) (Part1)

Bind the data set to the report.

Select the "Font" text box and set font properties as follows.

[code] =iif(Fields!Font.Value = "B","Bold",iif(Fields!Font.Value = "BU","Bold",iif(Fields!Font.Value = "BUU","Bold","Normal"))) [/code]

Select the "Balance" text box and set Border style properties as follows.

Top
[code] =iif(Previous(Fields!Font.Value) ="BUU","Double","Solid") [/code]

Bottom
[code] =iif(Fields!Font.Value = "BUU","Double","Solid") [/code]

For the double lines, set Border width properties as follows.

Top
[code] =iif(Previous(Fields!Font.Value) ="BUU","3pt","1pt")[/code]

Bottom
[code] =iif(Fields!Font.Value = "BUU","3pt","1pt") [/code]

Result will be like this.

You can see that the bottom row also should have double lines as the font is "BUU", but there is only a single line. Just simply add an extra row below the table.

Tip: When there are double lines ,

MSDN thread suggests a few requirements to get this running:

Double Line border turn to be single in Reporting Service

The border width must be 3pt at least.
There must be at least one row beneath the Double border text box.

One more note - this is not required for Excel - just add a 1pt Double border and it will be fine. The above series of steps are only required for Web, PDF, etc.

Tuesday, September 20, 2016

Dynamically bind data to an rdlc report and load report within a popup window (Part2)

To display the report within another popup window, you have to add some lines to the same code.

Just remove the last line of this method and modify the method as shown as below.

[code] private void getDataSetNames(DataTable[] datatables) { ReportDataSourceCollection dataSources = ReportViewer1.LocalReport.DataSources; this.ReportViewer1.LocalReport.DataSources.Clear(); IList dataSetNames; dataSetNames = this.ReportViewer1.LocalReport.GetDataSourceNames(); for (int i = 0; i < dataSetNames.Count; i++) { dataSources.Add(new ReportDataSource(dataSetNames[i], datatables[i])); } this.ReportViewer1.DataBind(); this.ReportViewer1.LocalReport.Refresh(); } [/code]

Modified method

[code] private void getDataSetNames(DataTable[] datatables) { ReportDataSourceCollection dataSources = ReportViewer1.LocalReport.DataSources; this.ReportViewer1.LocalReport.DataSources.Clear(); IList dataSetNames; dataSetNames = this.ReportViewer1.LocalReport.GetDataSourceNames(); for (int i = 0; i < dataSetNames.Count; i++) { dataSources.Add(new ReportDataSource(dataSetNames[i], datatables[i])); } this.ReportViewer1.DataBind(); string format = "PDF"; string deviceInfo = null; Byte[] results; string fileName = Server.MapPath("../") + "pdf\\report.pdf";//PDF location if (System.IO.File.Exists(fileName)) { System.IO.File.Delete(fileName); } results = ReportViewer1.LocalReport.Render(format, deviceInfo); string p; p = Request.Url.ToString(); string[] parts = p.Split('/'); string[] b = new string[4]; b[0] = parts[0]; b[1] = parts[1]; b[2] = parts[2]; b[3] = parts[3];//change the array items count according the path of the pdf file string a4 = string.Join("/", b); using (FileStream stream = File.OpenWrite(fileName)) { stream.Write(results, 0, results.Length); } Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "ClosePopup", "var popup=window.open('" + a4 + "/pdf/report.pdf','CustomerStatement','toolbar=no,menubar=no,status=yes');popup.focus();", true); ReportViewer1.Visible = false; } [/code]

Tuesday, August 9, 2016

Execute string inside a Stored Procedure

When there are multiple values in a single input parameter for a SP we can use sp_executesql .

Assume the query is like below.

select * from Item where ListId in ('IT/03218-ACC','IT/03253-ACC')

When we use this inside a SP,

CREATE PROCEDURE [dbo].[SP_ItemBlog]
(
@ItemList varchar(max)
)
AS

DECLARE @ItemQuery varchar(max) ='',
@Query nvarchar(max)

IF @ItemList <>''
BEGIN
SET @ItemList = REPLACE(@ItemList,'`','''')
END

BEGIN
SET @Query ='select * from Item where ListId in ('+@ItemList+')'

EXECUTE sp_executesql @Query
print @Query

END

Execute SP

SP_ItemBlog '`IT/03218-ACC`,`IT/03253-ACC`'