Binding Crystal Report with .XSD [Dataset] With Database Which is Required Password

SqlConnection con = new SqlConnection(“Conneection String”);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText=”Procedure Name”;

cmd.Parameters.AddWithValue(“@param1″, value);
cmd.Parameters.AddWithValue(“@param2″,value);

SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, “tab1″);

ds.Tables[0].TableName = “specialreport1″; //This will be Dataset[.Xsd] name

ReportDocument crystalReport = new ReportDocument();

crystalReport.Load(Server.MapPath(“~“) + “\\reports\\rptSpecialReport.rpt”);

crystalReport.SetDatabaseLogon(“USERNAME”,”PASSWORD”,

@”SERIP”,“DBASE”);

crystalReport.SetDataSource(ds);

CrystalReportViewer1.ReportSource = crystalReport;

To Search A Particular Text or Table in which locations userd

Checking usage of Table in a Store Procedure :

select * from sys.procedures where OBJECT_DEFINITION(OBJECT_ID) Like

‘%tablename%’

Checking of Text in stored procedure:

select * from syscomments where text like ‘%tablename%’

Accessing Ms Access File with Password

Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=C:\ACCESS.mdb;Jet OLEDB:database password=password;

Datagridview With CheckBox

/* To Create a DatagridView with checkbox */

<asp:TemplateField HeaderText=”Select”>

<HeaderTemplate>

<asp:CheckBox runat=”server” Text=”All”">All(this);” />

</HeaderTemplate>

<ItemTemplate>

<asp:CheckBox” chkSelect” runat=”server” />

</ItemTemplate>

</asp:TemplateField>

/* Javascript function  to select all check boxes */

<script language=”javascript” type=”text/javascript”>

function All(CheckBox)

{

TotalChkBx = parseInt(‘<%= this.GridView1.Rows.Count %>’);

var TargetBaseControl = document.getElementById(‘<%= this.GridView2.ClientID %>’);

var TargetChildControl = “chkSelect“;

var Inputs = TargetBaseControl.getElementsByTagName(“input”);

for(var iCount = 0; iCount < Inputs.length; ++iCount)

{

if(Inputs[iCount].type == ‘checkbox’ && Inputs[iCount].id.indexOf(TargetChildControl,0) >= 0)

Inputs[iCount].checked = CheckBox.checked;

}

}

</script>

In code Behind

/* To get which are the values are selected in checkbox */

protected void btnsub_Click(object sender, EventArgs e)

{

CheckBox cbk = new CheckBox();

GridViewRowCollection gv = GridView1.Rows;

//foreach (GridViewRowCollection gv in GridView1.Rows)

foreach (GridViewRow gr in gv)

{

cbk = (CheckBox)(gr.Cells[0].FindControl(“chkSelect”));

if (cbk.Checked == true)

{

//  Which is the value required from the Datagrid View gr.Cells[2].Text.Trim();

Response.Write(gr.Cells[2].Text);

System.Threading.Thread.Sleep(100);

}

}

}

Javascript Adding in Codeside in Asp.net To Check Date Ranges

this.btnLoad.Attributes.Add(“onclick”,”javascript:return CheckDate();”);

<script language=”javascript” type=”text/javascript”>
function CheckDate()
{
var date1=document.getElementById(‘txtFromDate’).value;
var date2=document.getElementById(‘txtToDate’).value;

var datDate1=new Date();
var datDate2=new Date();

datDate1= Date.parse(date1);
datDate2= Date.parse(date2);
datediff = ((datDate2-datDate1)/(24*60*60*1000))
//To Check whether Days are greater than 7 days or not
if(datediff>7)
{
alert(‘You can not select more than 7 days’);
return false;
}
else
{
return true;
}

if(date1 > date2)
{
alert(‘please select proper dates’);
return false;
}
else
{
return true;
}
}
</script>

To add Serial Number in Grid

For Data Grid 

<asp:templatecolumn headertext=”S.No” >
<itemtemplate>
<%# Container.ItemIndex+1 %>
</itemtemplate>
</asp:TemplateColumn> 

   

For DataGridView 

<asp:TemplateField HeaderText=”S.No”> <ItemTemplate>                                <%#Container.DataItemIndex + 1%></ItemTemplate>  <HeaderStyle HorizontalAlign=”Left” /> <ItemStyle HorizontalAlign=”Left” Width=”50px” /> </asp:TemplateField>

 

 

 

 

 

Transactions in Sql Server

To Check any active transactions which is not commited:

SELECT * FROM sys.dm_tran_session_transactions

/* This work like activity Monitor */

To See all Executing Queries:

sp_who2 active

DBCC inputbuffer(blkby) [To check sp_who2 blkby id executing query or procedure]

declare @table table(spid int,status varchar(50),[login] varchar(100),hostname varchar(100),
blkby varchar(100),dbname varchar(15),command varchar(100),cputime int,diskio int,
lastbatch varchar(100),programname varchar(100),spid1 int,requestid int)
insert into @table
exec sp_who2 active
select spid,status,login,hostname,cputime,command,blkby from @table where ltrim(rtrim(hostname))<>’.’ and dbname<>’master’
and ltrim(rtrim(blkby))<>’.’ and status<>’sleeping’ and isnull(login,”)<>”
order by hostname

DBCC inputbuffer(blkby)

How to get last date of a Month in Sql Server

declare @date varchar(10)

set @date=convert(varchar,year(getdate()))+’-'+convert(varchar,(month(getdate())+1))+’-01′

select dateadd(day,-1,@date)

Logic is which month we want to find the last date take that month.

Suppose i want to find last day of   14-04-2009

Add 1 month to this and create a format of next month first day

the next month will be 05 ,2009

2009-05-01

With dateadd function add -1 day to that function . So it will come to the previous month last day.

dateadd(day,-1,’2009-05-01′)

it will display   ’2009-04-31‘

Inserting using one insert statement

USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5
GO

creating tables from other tables in sql 2005

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
—-Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
—-INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
—-Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
—-Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
—-Clean Up Database
DROP TABLE TestTable
GO