SSRS, ASP.NET, Android, SQL Server, SSIS, C# Solutions and Tips

Exclude Zero from SSRS Expression

noreply@blogger.com (Manoj Vishwakarma) — Fri, 17 Apr 2020 05:41:00 +0000

Hello Guys,

Please check out below tips for excluding the zero from the SSRS Expression.

=Avg(IIF(Field!FieldName.Value ="0", Nothing,Field!FieldName.Value))

Above expression is also works for the SUM, MIN, MAX and etc..

if you have any query feel free write on the comment.

Customer Page loader using Jquery

noreply@blogger.com (Manoj Vishwakarma) — Fri, 10 Jan 2020 04:15:00 +0000

Below post is to create simple page loading using JQuery & Html

SSIS Incremental Load Example - SQL Serve Integration Services

noreply@blogger.com (Manoj Vishwakarma) — Tue, 25 Dec 2018 05:30:00 +0000

Incremental Loads in SSIS are often used to keep data between two systems in sync with one another. They are used in cases when source data is being loaded into the destination on a repeating basis, such as every night or throughout the day

Below we have a

ll the steps are done in the SQL Server Integration Service (SSIS).

We don’t used the Lookup function because, while working with the large amount of records you must take care of the SSIS performance. Lookup will fetch all the records and then it will compare to your condition.

But in this solution, we had considered only those records which was the latest in the live tables.

1. Store Max Timestamp/Value

insert into ETL_Table_Last_Entry

   Select 'G_L_Entry' TableName,B.Company, isnull(max(timestamp),0x0000000000000000) TimeStamp

   from dbo.[G_L_Entry] B

   --Where [Posting Date] >@Date

       Group By B.Company

2. Find Last value and store in the variable

SELECT dbo.fnTS2VC([TimeStamp]) AS t

FROM ETL_Table_Last_Entry

WHERE (Company = 1) AND (TableName = 'G_L_Entry')

3. Truncate temporary table



truncate table tmp_GL_Entry

4. Add Data flow task and add latest records

"Select * from dbo.[" + @[User::Company_Name] + "$G_L Entry]

where timestamp > 0x" + @[User::PV1] + " order by timestamp"

In above query we taking those record which are greater then our stored value, We have stored above query in variable to get the dynamic execution based on the multiple tables.

In Source we have execute the query

Our Destination configuration

5. Delete duplicate record

"Delete a from G_L_Entry a inner join tmp_GL_Entry b on a.[Entry No_]=b.[Entry No_] and a.Company=b.Company where a.Company = " + @[User::Company_No]

Above query we have stored in the variable and added in the SQL task.

6. Reinsert the remaining records in actual table.

7.Rebuild the index for actual table.

8.Update latest record to get the new record while reading in the next read

Now execute the package and check your output.

Thanks for reading.

How to convert TIMESTAMP values to VARCHAR in T-SQL

noreply@blogger.com (Manoj Vishwakarma) — Wed, 17 Oct 2018 11:57:00 +0000

Hi Guys,

here I have function that return the conversion from timestamp to varchar.

USE [DataBase]

CREATE FUNCTION [dbo].[fnTS2VC]

(

@ts BINARY(8)

)

RETURNS VARCHAR(16)

BEGIN

RETURN SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) / 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) % 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) / 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) % 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) / 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) % 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) / 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) % 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) / 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) % 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) / 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) % 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) / 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) % 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) / 16, 1)

+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) % 16, 1)

END

Result :

TimeStamp	Varchar
0x00000001EDC9C070	00000001EDC9C070

SSRS Indicator decimal value and states

noreply@blogger.com (Manoj Vishwakarma) — Fri, 14 Sep 2018 09:20:00 +0000

We have getting issue sometime with SSRS Indicators,

while setting numerical logic between below 0 and above 0 some decimal value skipped by the indicators (Showing blank indicator instead of Yellow Circle).

Solution:

below expression will help to make it visible even if the decimal value starts from 0.00001.

Before:

Red Indicator Start : -1 End : -99999

Yellow Indicator Start: 0 End 0

Green Indicator Start : 1 End : 99999

After:

Red Indicator Start : -1E-06 End : -99999

Yellow Indicator Start: 0 End 0

Green Indicator Start : 1E-05 End : 99999

How to get a list of SSRS schedules for subscriptions

noreply@blogger.com (Manoj Vishwakarma) — Wed, 11 Jul 2018 05:10:00 +0000

Hi Guys,

below script gives you the list of subscription set on each and every report of SSRS.

use ReportServer

select c.Name,
c.Path,
s.StartDate,
s.NextRunTime,
s.LastRunTime,
s.EndDate,
s.RecurrenceType,
s.LastRunStatus,
s.MinutesInterval,
s.DaysInterval,
s.WeeksInterval,
s.DaysOfWeek,
s.DaysOfMonth,
s.[Month],
s.MonthlyWeek
from dbo.catalog c with (nolock)
inner join dbo.ReportSchedule rs
on rs.ReportID = c.ItemID
inner join dbo.Schedule s with (nolock)
on rs.ScheduleID = s.ScheduleID
order by s.LastRunTime desc

If you find any difficulty then reply on the comment box.
I will revert as soon as possible.

Thanks for the reading.

Replace Zero with Blank or Dash in SSRS

noreply@blogger.com (Manoj Vishwakarma) — Thu, 17 May 2018 05:03:00 +0000

Here is the solution to replace zero with the Blank(' ') or Dash(-)

select textbox and check the properties on right side pan as shown in image.

for blank use below format

''0.00;(''0.00);''

for dash use below format

''0.00;(''0.00);'-'

How to add the value of Barcode scanner in Textbox C#

noreply@blogger.com (Manoj Vishwakarma) — Sat, 12 May 2018 06:19:00 +0000

Hi Guys,

Before doing the C# code we have to design out mobile windows application as per given below.

Above screen has one Text box and one label to display our output return by the scanner machine.

C# Code:

public partial class MainPage : Form

{

public System.Windows.Forms.Timer tmrDelay;

public MainPage()

{

InitializeComponent();

}

private void textBox1_TextChanged(object sender, EventArgs e)

{

try

{

if (textBox1.Text.Trim().Length == 1)

{

tmrDelay.Enabled = true;

tmrDelay.Tick += new EventHandler(tmrDelay_Tick);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

void tmrDelay_Tick(object sender, EventArgs e)

{

try

{

tmrDelay.Enabled = false;

string strCurrentString = textBox1.Text.Trim().ToString();

if (strCurrentString != "")

{

//Do something with the barcode entered

lblOutput.Text="Output : "+strCurrentString;

textBox1.Text = "";

}

textBox1.Focus();

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

private void MainPage_Load(object sender, EventArgs e)

{

tmrDelay = new System.Windows.Forms.Timer();

tmrDelay.Interval = 1000;

tmrDelay.Enabled = false;

}

After adding above code we will get the output as given below.

Thanks for reading :)

SSRS Pass a Report Parameter Within a URL

noreply@blogger.com (Manoj Vishwakarma) — Sat, 02 Dec 2017 08:59:00 +0000

You can pass report parameters to a report by including them in a report URL. These URL parameters are not prefixed because they are passed directly to the report processing engine.

Reference :
https://docs.microsoft.com/en-us/sql/reporting-services/pass-a-report-parameter-within-a-url

http://localhost/Reports/Pages/Report.aspx?ItemPath=%2f%5bQuality+Sales+Report%5d%2fCP+Quailty+Sales+Details+Report+Preview&ViewMode=Detail

Above link is your report server link which provide you to make subscription and etc.
Instead of above link try below

Go to http://localhost/Reportserver find your report in above link and pass "&ID=(value) at the end of the url or

http://rpt.Server.local/Reportserver/Pages/Report.aspx?ItemPath=%2f%5bQuality+Sales+Report%5d%2fCP+Quailty+Sales+Details+Report+Preview&ID=(value)

Database name databasename ignored, referencing object in tempdb

noreply@blogger.com (Manoj Vishwakarma) — Mon, 20 Nov 2017 06:39:00 +0000

This type of warning you got while working with stored procedure.

Warning : Database name <databasename> ignored, referencing object in tempdb.

Causes: while working with the temporary table you might be used the temp table reference with schema.

ex: select * from databasename.dbo.#tmpTable

this syntax gives you a warning message

Solution:
Don't use reference in your query.

ex: select * from #tmpTable

Sql server will auto manage the temp table with the different different database temp table in tempdb.

Delete encryption key from report server

noreply@blogger.com (Manoj Vishwakarma) — Fri, 10 Nov 2017 06:13:00 +0000

If unable to delete encryption key from SQL server reporting service configuration manager, to delete it manually use below stored procedure.

use ReportServer

exec DeleteEncryptedContent

If you find any difficulty, feel free revert.

Above stored procedure is predefined in Report Server.

How to Find Second Highest or maximum Salary of Employee in Sql Server

noreply@blogger.com (Manoj Vishwakarma) — Fri, 21 Aug 2015 12:29:00 +0000

Suppose you want to get second highest salary or you want to find the highest salary.
Use following query to get small data in temp table.

select * into #tmpEmp

from

(select 'E1' EmpNo,'Emp1'Name,'Dept1' Dept,25000 Salary

union all

select 'E2' EmpNo,'Emp2'Name,'Dept1' Dept,2000 Salary

union all

select 'E3' EmpNo,'Emp3'Name,'Dept2' Dept,30000 Salary

union all

select 'E4' EmpNo,'Emp4'Name,'Dept2' Dept,2000 Salary

union all

select 'E5' EmpNo,'Emp5'Name,'Dept2' Dept,200000 Salary

union all

select 'E5' EmpNo,'Emp5'Name,'Dept2' Dept,15000 Salary)P

Result:

Now just set the Rank Column to the table data using below query.

Get the second highest salary from each department using this query:

select * from

(select ROW_NUMBER()Over(partition by Dept order by Salary desc)Rank,* from #tmpEmp)P

where Rank=2

Result:

Get highest salary from this query:

select * from

(select ROW_NUMBER()Over(order by Salary desc)Rank,* from #tmpEmp)P

where Rank=1

Result:

Calling web service from jquery ajax in asp.net

noreply@blogger.com (Manoj Vishwakarma) — Wed, 29 Jul 2015 07:32:00 +0000

Calling web service from jquery ajax in asp.net

Arrange your file in solutions explorer in given below manner.

File Hierarchy

wcUser.asmx

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Web.Script.Serialization;

using System.IO;

using System.Text;

using System.Web.Script.Services;

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[ScriptService]

public class wcUser : System.Web.Services.WebService

{

public List<User> objUser;

User user;

[WebMethod]

public string HelloWorld()

{

return "Hello World";

}

[WebMethod]

public List<User> getUserList()

{

try

{

objUser = new List<User>();

user= new User();

user.Username = "Manoj";

user.Password = "123";

objUser.Add(user);

user = new User();

user.Username = "Prashant";

user.Password = "456";

objUser.Add(user);

}

catch { }

return objUser;

}

Default.aspx

</head>

<div>

<tr>

<td>

</td>

<td>

<asp:Button ID="btnWebService" CssClass="btn btn-danger" Text="Web Service" runat="server" />

</td>

</tr>

</table>

<hr />

<asp:GridView ID="gvUsers" runat="server" CssClass="table-condensed" HeaderStyle-BackColor="#3AC0F2"

HeaderStyle-ForeColor="White" RowStyle-BackColor="#A1DCF2">

</asp:GridView>

</div>

<div>

</table>

</div>

</form>

</body>

</html>

User.cs

public class User

{

public string Username { get; set; }

public string Password { get; set; }

}

Default.js

$(function () {

$("[id*=btnWebService]").bind("click", function () {

$(this).val("Please Wait...");

$("#gvUsersTable").empty();

getUserListWebService();

$(this).attr('disabled', 'disabled');

return false;

});

function getUserListWebService() {

$.ajax({

type: "POST",

contentType: "application/json; charset=utf-8",

url: "WebService/wcUser.asmx/getUserList",

data: "{}",

dataType: "json",

success: function (data) {

if (data.d.length > 0) {

$("#gvUsersTable").append("<tr style='background-color:#3AC0F2'><th>UserName</th> <th>Password</th><th>Delete</th></tr>");

for (var i = 0; i < data.d.length; i++) {

$("#gvUsersTable").append("<tr><td>" + data.d[i].Username + "</td><td>" + data.d[i].Password + "</td><td align='center'>" + "<button style='background-color:#F99292;border:0px none;border-radius:50px' onclick='javascript:deleteUser(this.value);return false;' value='" + data.d[i].Username + "'>×</button>" + "</td></tr>");

}

resetForm();

error: function (request, status, error) { alert(request); alert(status); alert(error); }

});

}

function resetForm() {

//After Load

$("[id*=btnWebService]").removeAttr('disabled');

$("[id*=btnWebService]").val("Web Service");

}

Result:

noreply@blogger.com (Manoj Vishwakarma) — Sat, 25 Jul 2015 11:34:00 +0000

Insert Update Delete in ASP.NET using Jquery (Ajax/Json)

Create table UserList

Use following query to create table:

CREATE TABLE [dbo].[UserList](

[UserName] [varchar](100) NULL,

[Password] [varchar](100) NULL

)

Arrange your file in solutions explorer in given below manner.

Default.aspx

</head>

<div>

<tr>

<td>

Username:

</td>

<td>

<asp:TextBox ID="txtUsername" runat="server" Text="" />

</td>

</tr>

<tr>

<td>

Password:

</td>

<td>

<asp:TextBox ID="txtPassword" runat="server" TextMode="Password" />

</td>

</tr>

<tr>

<td>

</td>

<td>

<asp:Button ID="btnSave" CssClass="btn btn-success" Text="Save" runat="server" />

<asp:Button ID="btnMessage" CssClass="btn btn-info" Text="Load Data" runat="server" />

<%--<input type="button" value="click me" onclick="asyncServerCall(1);" />--%>

</td>

</tr>

</table>

<hr />

<asp:GridView ID="gvUsers" runat="server" CssClass="table-condensed" HeaderStyle-BackColor="#3AC0F2"

HeaderStyle-ForeColor="White" RowStyle-BackColor="#A1DCF2">

</asp:GridView>

</div>

<div>

</table>

</div>

</form>

</body>

</html>

Default.aspx.cs

* Add below namespaces

using System.Web.Services;

using System.Web.Script.Services;

using System.Configuration;

using System.Data.SqlClient;

using System.Data;

[WebMethod]

[ScriptMethod]

public static List<User> getUserData()

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

List<User> details = new List<User>();

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand("SELECT * FROM UserList"))

{

using (SqlDataAdapter sda = new SqlDataAdapter())

{

DataTable dt = new DataTable();

cmd.CommandType = CommandType.Text;

cmd.Connection = con;

sda.SelectCommand = cmd;

sda.Fill(dt);

foreach (DataRow dtrow in dt.Rows)

{

User user = new User();

user.Username = dtrow["UserName"].ToString();

user.Password = dtrow["Password"].ToString();

details.Add(user);

}

return details;

}

[WebMethod]

[ScriptMethod]

public static void SaveUser(User user)

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand("INSERT INTO UserList VALUES(@Username, @Password)"))

{

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@Username", user.Username);

cmd.Parameters.AddWithValue("@Password", user.Password);

cmd.Connection = con;

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

[WebMethod]

[ScriptMethod]

public static void deleteUser(string id)

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlCommand cmd = new SqlCommand("delete UserList where UserName=@userName"))

{

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@userName", id);

cmd.Connection = con;

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

User.cs

public class User

{

public string Username { get; set; }

public string Password { get; set; }

}

Default.js

/*submit*/

//Save into Database

$(function () {

$("[id*=btnSave]").bind("click", function () {

var user = {};

$(this).attr('disabled', 'disabled');

$(this).val('Please Wait...');

user.Username = $("[id*=txtUsername]").val();

user.Password = $("[id*=txtPassword]").val();

if (user.Username != "" && user.Password != "") {

$.ajax({

type: "POST",

url: "Default.aspx/SaveUser",

data: '{user: ' + JSON.stringify(user) + '}',

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function (response) {

alert("User has been added successfully.");

$("#gvUsersTable").empty();

getUserList();

resetForm();

}

});

}

else {

alert("Please fill the details !");

$(this).removeAttr('disabled');

$(this).val('Save');

}

return false;

});

//Reset Form

function resetForm() {

//After Save

$("[id*=btnSave]").removeAttr('disabled');

$("[id*=btnSave]").val("Save");

$("[id*=txtUsername]").val("");

$("[id*=txtPassword]").val("");

//After Load

$("[id*=btnMessage]").removeAttr('disabled');

$("[id*=btnMessage]").val("Load Data");

}

//Get grid by Button

$(function () {

$("[id*=btnMessage]").bind("click", function () {

$(this).val("Please Wait...");

$("#gvUsersTable").empty();

getUserList();

$(this).attr('disabled', 'disabled');

return false;

});

//Load grid after window Load

$(document).ready(function () {

$(window).load(function () {

getUserList();

});

//Get UserList and bind it into table

function getUserList() {

$.ajax({

type: "POST",

contentType: "application/json; charset=utf-8",

url: "Default.aspx/getUserData",

data: "{}",

dataType: "json",

success: function (data) {

if (data.d.length > 0) {

$("#gvUsersTable").append("<tr style='background-color:#3AC0F2'><th>UserName</th> <th>Password</th><th>Delete</th></tr>");

for (var i = 0; i < data.d.length; i++) {

}

resetForm();

error: function (result) {

//alert("Error"+result);

}

});

}

//delete User from List

function deleteUser(id) {

if (id == null || id == "") {

alert("Id not Proper");

}

else {

$.ajax({

type: "POST",

url: "Default.aspx/deleteUser",

data: '{id: ' + JSON.stringify(id) + '}',

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function (response) {

$("#gvUsersTable").empty();

getUserList();

alert("User has been deleted successfully.");

}

});

}

return false;

}

Result

Print / Export SSRS report in ASP.NET | Print Reportviewer Report using JavaScript / Jquery

noreply@blogger.com (Manoj Vishwakarma) — Mon, 06 Jul 2015 07:33:00 +0000

Create a report from following post
http://ssrsmegabits.blogspot.in/2015/06/ssrs-report-in-aspnet-example.html

Now we will update above solution for Print SSRS reports using JavaScript and Export it in PDF Format.

Design:

<asp:Button ID="btnExportPDF" runat="server" Text="PDF"

onclick="btnExportPDF_Click" />

<rsweb:ReportViewer Width="100%" ShowToolBar="false" ID="rptvMyReport" runat="server" AsyncRendering="false">

</rsweb:ReportViewer>

</div>

Here we have two div with id 'result' and 'content'.

In result div we will copy the content div in the print format and call print method in javascript.

One button added for export report in PDF Format.

javascript Code:

This code for Print ssrs report.

function PrintReport() {

var viewerReference = $find('<%=rptvMyReport.ClientID%>');

$('#result').empty();

var stillonLoadState = viewerReference.get_isLoading();

if (!stillonLoadState) {

var reportArea = viewerReference.get_reportAreaContentType();

if (reportArea == Microsoft.Reporting.WebFormsClient.ReportAreaContent.ReportPage) {

$('#rptvMyReport').clone().prependTo("#result");

//copy reportviewer report in div

$('#content').hide();

$('#result').show();

//hide reportviewer containing div and show copied div

window.print();

//Open Print dialog

$('#content').show();

$('#result').hide();

//Reset

}

</script>

Now we will added this code in code behind to get SSRS report in PDF Format.

protected void btnExportPDF_Click(object sender, EventArgs e)

{

Warning[] warnings;

string[] streamIds;

string mimeType = string.Empty;

string encoding = string.Empty;

string extension = string.Empty;

string Title = "UserList " + Convert.ToString(DateTime.Now);

byte[] bytes = rptvMyReport.LocalReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamIds, out warnings);

Response.Buffer = true;

Response.Clear();

Response.ContentType = mimeType;

Response.AddHeader("content-disposition", "attachment; filename=" + Title + "." + extension);

Response.BinaryWrite(bytes); // create the file

Response.Flush();

}

Note : If you want to get report in WORD and EXCEL format then update it as following.

For Excel:
byte[] bytes = rptvMyReport.LocalReport.Render("EXCEL", null, out mimeType, out encoding, out extension, out streamIds, out warnings);

For Word:
byte[] bytes = rptvMyReport.LocalReport.Render("WORD", null, out mimeType, out encoding, out extension, out streamIds, out warnings);

Now check the output, you will get the print and export working in ASP.NET.

SSRS Report in ASP.NET Example

noreply@blogger.com (Manoj Vishwakarma) — Fri, 26 Jun 2015 10:03:00 +0000

Following steps are given to create a SSRS(RDLC) Report in ASP.NET

1) Create table "UserList" in Database

2) Create Connection String in Web.Config

<add name="constr" connectionString="<Databaseconnection>" ProviderName="System.Data.SqlClient"/>

</connectionStrings>

3) Add httphandler to web.config.

<system.web>

<add path="Reserved.ReportViewerWebControl.axd" verb="*"

type="Microsoft.Reporting.WebForms.HttpHandler,

Microsoft.ReportViewer.WebForms, Version=10.0.0.0,

Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" validate="false"

</httpHandlers>

</system.web>

<system.webServer>

<add name="ReportViewerWebControlHandler"

preCondition="integratedMode"

verb="*" path="Reserved.ReportViewerWebControl.axd"

type="Microsoft.Reporting.WebForms.HttpHandler,

Microsoft.ReportViewer.WebForms, Version=10.0.0.0,

Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

</handlers>

</system.webServer>

4) Create empty ASP.NET Application.

5) Crate Report Folder, Named it “Reports”

6) Add SSRS Report(.rdlc) in report folder, Named it “MyReport”.

7) Add Dataset (.xsd) in report folder, Named it “MyReportUser”.

8) Design Dataset (add Datatable and add column in datatable as same in SQL Table.

9) Open Myreport.rdlc file and add DataSet “MyReportUser” in Report Data Section.

10) Design report as per your requirement.

11) Add script manager and report viewer to aspx page as shown below:

<asp:ScriptManager runat="server">

</asp:ScriptManager>

<div>

<rsweb:ReportViewer Width="100%" ShowToolBar="false" ID="rptvMyReport" runat="server">

</rsweb:ReportViewer>

</div>

</form>

12) Add following namespaces

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using Microsoft.Reporting.WebForms;

using SSRSReportExample.Reports; //To Access Reports folder Dataset

13) Add to Code Behind

string str = ConfigurationManager.ConnectionStrings["constr"].ToString();

DataSet ds;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

getUserList();//Call report at page load

}

private void getUserList()

{

SqlConnection con = new SqlConnection();//Create SQL Connection Object

MyReportUser dsUserList = new MyReportUser();//Create DataSet Object

try

{

con.ConnectionString = str;

SqlDataAdapter sda = new SqlDataAdapter("select * from UserList", con);

con.Open();

sda.Fill(dsUserList, "UserList");//bind data to DataSet

if (dsUserList.Tables[0].Rows.Count > 0)//if bind is successfull then execute

{

rptvMyReport.ProcessingMode = ProcessingMode.Local;

rptvMyReport.LocalReport.ReportPath = Server.MapPath("~/Reports/MyReport.rdlc");

rptvMyReport.LocalReport.DataSources.Clear();//Clear previous applied Datasource

ReportDataSource rptDataSource = new ReportDataSource("UserList", dsUserList.Tables[0]);//Create Report Datasource and add Dataset in it.

rptvMyReport.LocalReport.DataSources.Add(rptDataSource);

}

catch { }

finally { con.Close(); }

}

14) Build your application and check the result.

Prevent Button Double Click ASP.NET Code Behind

noreply@blogger.com (Manoj Vishwakarma) — Wed, 17 Jun 2015 08:25:00 +0000

Disable ASP.NET Button after click to prevent the double click and change button text to Loading/Please Wait.

Design :

<asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />

Code Behind :

//Here javascript added to the button at page load.

protected void Page_Load(object sender, EventArgs e)

{

try

{

string strProcessScript = "this.value='Processing...';this.disabled=true;";

Button1.Attributes.Add("onclick", strProcessScript + ClientScript.GetPostBackEventReference(Button1, "").ToString());

}

catch { }

}

//After Button click method wait for 5 Second to check the result.

protected void Button1_Click(object sender, EventArgs e)

{

Thread.Sleep(5000);

}

Then check you result.

If you have any issue then let me know.

Convert String to DateTime in ASP.NET using CultureInfo

noreply@blogger.com (Manoj Vishwakarma) — Wed, 10 Jun 2015 07:32:00 +0000

In textbox we have different different date formats for eg. 'dd-MMM-yy','yyyy-MM-dd' etc.
this format sometime throw errors like
string was not recognized as a valid datetime.

And for this type of error we are using following code which can convert all the date format into given Culture.

GetCultureInfo():Retrieves a cached, read-only instance of a culture using the specified culture name

Convert.ToDateTime(TextBoxID.Text,System.Globalization.CultureInfo.GetCultureInfo("hi-IN").DateTimeFormat);

Show Bootstrap Modal popup using ASP.NET

noreply@blogger.com (Manoj Vishwakarma) — Thu, 04 Jun 2015 05:12:00 +0000

Design:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="BootstrapModal.aspx.cs" Inherits="BootstrapModal_ASPNET.BootstrapModal" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Bootstrap Example</title>

</head>

<body>

<h2>Modal Example</h2>

<button type="button" class="btn btn-info btn-lg" data-toggle="modal" data-target="#myModal">Open Modal</button>

<asp:Button ID="btnOpenModal" runat="server" CssClass="btn btn-info btn-lg" Text="Open with ASP Button" onclick="btnOpenModal_Click" />

<h4 class="modal-title">Modal Header</h4>

</div>

<p>Some text in the modal.</p>

</div>

<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>

<asp:Button ID="btnCloseModal" CssClass="btn btn-default" runat="server" Text="Close & Reopen"

onclick="btnCloseModal_Click" />

</div>

</form>

</body>

</html>

Code Behind:

protected void btnOpenModal_Click(object sender, EventArgs e)

{

ScriptManager.RegisterStartupScript(Page, Page.GetType(), "myModal", "$('#myModal').modal();", true);

}

protected void btnCloseModal_Click(object sender, EventArgs e)

{

ScriptManager.RegisterStartupScript(Page, Page.GetType(), "myModalHide", "$('#myModal').hide();", true);

ScriptManager.RegisterStartupScript(Page, Page.GetType(), "myModalAgainShow", "$('#myModal').modal();", true);

}

Note : If sometime we want to hide parent modal and want to show child modal then we have to add following code to hide Modal.
and If bootstrap modal-backdrop doesn't disappear

ScriptManager.RegisterStartupScript(Page,Page.GetType(), "myModalHide", "$('body').removeClass('modal-open');$('.modal-backdrop').remove();$('#myModal').hide();", true);

Outoput:

Open Link in Disabled Window using JavaScript

noreply@blogger.com (Manoj Vishwakarma) — Tue, 02 Jun 2015 05:28:00 +0000

Below code is to open the link with disabled address bar and toolbar using javascript.

We have window.open() method to get the link in new window and before this we set some attributes to new window in strWindowFeatures.

Also, you can use this code for ASP.NET Hyperlink tag.

Copy this code and save it as filename.html to get the result.

<html>

<head>

var windowObjectReference;

var strWindowFeatures="height="+screen.height+",width="+screen.width+",fullscreen=yes,menubar=no,location=no,resizable=no,scrollbars=yes,status=no";

function openRequestedPopup()

{

windowObjectReference=window.open("http://www.google.co.in/","Google",strWindowFeatures);

}

</script>

</head>

<body>

<form>

<a href="#" onClick="openRequestedPopup()">Click</a>

</form>

</body>

</html>

Disable ASP.NET Button after click | Disable Double Click

noreply@blogger.com (Manoj Vishwakarma) — Thu, 28 May 2015 10:39:00 +0000

About UseSubmitBehavior:

Use the UseSubmitBehavior property to specify whether a Button control uses the client browser's submit mechanism or the ASP.NET postback mechanism. By default the value of this property is true, causing the Button control to use the browser's submit mechanism. If you specify false, the ASP.NET page framework adds client-side script to the page to post the form to the server.

OnClientClick:

Use the OnClientClick property to specify additional client-side script that executes when a Button control's Click event is raised. The script that you specify for this property is rendered in the Button control's OnClick attribute in addition to the control's predefined client-side script.

Source:

<asp:Button ID="btnSubmit" runat="server" Text="Submit" UseSubmitBehavior="false"

OnClientClick="this.disabled='true';this.value='Please Wait...'"

OnClick="btnSubmit_Click" />

Code Behind:

protected void btnSubmit_Click(object sender, EventArgs e)

{

Thread.Sleep(9000); // Wait for 9 Seconds

}

Output:

Show Binary Image in ASP.NET without using handler | Bind Binary Image in ASP.NET

noreply@blogger.com (Manoj Vishwakarma) — Tue, 28 Apr 2015 06:45:00 +0000

Here we are going to bind the binary image from database.

Our binary image in in the varbnary(max) formatted in the database.

We Need to Add HTML Image control in design view:

And in code behind we have to add this:

First bind the Dataset and write below this code

byte[] imageBytes=Convert.ToBase64String((byte[])'DataSet Image Field');//replace DataSet Image field with varbinary Field.

ProfileImage.Src = "data:image/png;base64," + imageBytes;

You are done all the steps. The image will be display.

SSRS - Check Report Rendering Time

noreply@blogger.com (Manoj Vishwakarma) — Mon, 30 Mar 2015 09:52:00 +0000

Here SQL Reporting Server have log our reports processing time in table and We have a View "ExecutionLog3" in SQL Server.

Following Query will gives us the total time (Minutes) taken by the SSRS report to generation.

This Query help us to find out the exact time of every stage.

TimeDataRetrieval : Give us time to data retrieval by SQL Server
TimeProcessing : Give us time processed to bind with the DataSet.
TimeRendering : Give us time to rendering(Formatting) the SSRS report.

use ReportServer

select top 10

InstanceName,

ItemPath,

UserName,

CAST((TimeDataRetrieval)as numeric(18,2))/60000 TimeDataRetrieval,

CAST((TimeProcessing)as numeric(18,2))/60000 TimeProcessing,

CAST((TimeRendering)as numeric(18,2))/60000 TimeRendering,

CAST((TimeDataRetrieval+TimeProcessing+TimeRendering)as numeric(18,2))/ 60000 [Total_Time(Minutes)]

from ExecutionLog3

SSRS - Temporary Disable All Subscriptions

noreply@blogger.com (Manoj Vishwakarma) — Tue, 17 Mar 2015 06:56:00 +0000

Temporary Disable Subscriptions

The InActiveFlags field in dbo.Subscriptions can be 1 or 0 (true or false).

--Code Snippet

--To Check Subscription Status.

USE ReportServer

SELECT dbo.Subscriptions.Description,

dbo.[Catalog].Name,

dbo.Users.UserName,

InactiveFlags

FROM dbo.Subscriptions

INNER JOIN dbo.[Catalog]

ON dbo.Subscriptions.Report_OID = dbo.[Catalog].ItemID

INNER JOIN dbo.Users

ON dbo.Subscriptions.OwnerID = dbo.Users.UserID

------------------------------------------------------------------------------------------

--Temporary disables subscriptions while data warehouse is unavailable

UPDATE dbo.Subscriptions

SET InactiveFlags = 1

--After this you can find, all the subscription where disabled until you again enable it.

Again enable subscriptions.

UPDATE dbo.Subscriptions

SET InactiveFlags = 0

SSRS wrap/line break row and column within group

noreply@blogger.com (Manoj Vishwakarma) — Thu, 26 Feb 2015 09:16:00 +0000

This SSRS solution is for making row/column break in grouping.

For the result we will consider following queary as an Example:

SQL Query :-

select '1' [Rank],'Product1' Product

union all

select '2','Product2'

union all

select '3','Product3'

union all

select '4','Product4'

union all

select '5','Product5'

union all

select '6','Product6'

and using above we get the result as shown below

Result :-

Rank Products

1 Product1
2 Product2
3 Product3
4 Product4
5 Product5
6 Product6

Now in SSRS we need to show the result as shown below and for that we will use report Builder and creating new report.

So we have to create a dataset using above query and create one table as shown below:

Now add Row Group and Column Group as shown

In Row Group

1. Go to the properties and add group expressions

2. Add this =(Fields!Rank.Value- 1) Mod 3

In Column Group

1. Go to properties of Column Group

2. Add this to expression =Floor((Fields!Rank.Value - 1) / 3)

See the result as shown Below:

SSRS, ASP.NET, Android, SQL Server, SSIS, C# Solutions and Tips

Exclude Zero from SSRS Expression

Customer Page loader using Jquery

Below post is to create simple page loading using JQuery & Html

SSIS Incremental Load Example - SQL Serve Integration Services

Below we have a

Our Destination configuration

"Delete a from G_L_Entry a inner join tmp_GL_Entry b on a.[Entry No_]=b.[Entry No_] and a.Company=b.Company where a.Company = " + @[User::Company_No] Above query we have stored in the variable and added in the SQL task. 6. Reinsert the remaining records in actual table.

7.Rebuild the index for actual table. 8.Update latest record to get the new record while reading in the next read

Now execute the package and check your output.

Thanks for reading.

How to convert TIMESTAMP values to VARCHAR in T-SQL

SSRS Indicator decimal value and states

How to get a list of SSRS schedules for subscriptions

Replace Zero with Blank or Dash in SSRS

How to add the value of Barcode scanner in Textbox C#

SSRS Pass a Report Parameter Within a URL

Database name databasename ignored, referencing object in tempdb

Delete encryption key from report server

How to Find Second Highest or maximum Salary of Employee in Sql Server

Calling web service from jquery ajax in asp.net

Print / Export SSRS report in ASP.NET | Print Reportviewer Report using JavaScript / Jquery

SSRS Report in ASP.NET Example

Prevent Button Double Click ASP.NET Code Behind

Convert String to DateTime in ASP.NET using CultureInfo

Show Bootstrap Modal popup using ASP.NET

Open Link in Disabled Window using JavaScript

Disable ASP.NET Button after click | Disable Double Click

Show Binary Image in ASP.NET without using handler | Bind Binary Image in ASP.NET

SSRS - Check Report Rendering Time

SSRS - Temporary Disable All Subscriptions

Temporary Disable Subscriptions

--Temporary disables subscriptions while data warehouse is unavailable

Again enable subscriptions.

SSRS wrap/line break row and column within group

"Delete a from G_L_Entry a inner join tmp_GL_Entry b on a.[Entry No_]=b.[Entry No_] and a.Company=b.Company where a.Company = " + @[User::Company_No]

Above query we have stored in the variable and added in the SQL task.

6. Reinsert the remaining records in actual table.

7.Rebuild the index for actual table.

8.Update latest record to get the new record while reading in the next read