My Professional IT Blog

Redirects handled by Google Analytics

2011-05-24T07:19:00.000-07:00

Google Analytics won't track a visit to a page unless that page runs the analytics tracking code. Although, there are at least 2 ways to track the redirects:
-Tracking a redirect using 301 redirect.
-Tracking a redirect using a JavaScript redirect.

In both instances, make sure the directory actually exists as a file (/it/growthmodel.aspx). I always use the JavaScript redirect so, let's talk about it:
Let say, you have a pdf file (growthmodel.pdf), you want to post it online and also want to count the number of visitors and their behaviours. For this, create a page growthmodel.aspx under the /it directory and redirect this page to pdf file using javascript redirect. Put the below code at growthmodel.aspx page:

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script type="text/javascript">

var _gaq = _gaq [];
_gaq.push(['_setAccount', 'UA-XXXXXX-X']);
_gaq.push(['_trackPageview']);

(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>

</head>
<body onLoad="_gaq.push(['_trackEvent', 'category', 'action', 'opt_label'])">
<script type="text/javascript">

function redirect()
{
window.location = "/growthmodel.pdf"
}

var temp = setInterval("redirect()", 1000);

</script>
</body>
</html>

setInterval=> 1000 so that tracking doesn't get missed.
'UA-XXXXXX-X' => please replace xxxx with your actual analytics account number.

Happy Programming!!

Startup JavaScript Code from Content Page to Master Page

2011-05-19T13:38:00.000-07:00

Let say, you have Master and Content pages and you want to add some JavaScript code from your Content pages that would run when the page is loaded. Content pages do not have the HTML elements like body tag to add your script on its onload event.

For this, you can use RegisterStartupScript method. Add the following code to your Content Page code behind Page_Load Event:

protected void Page_Load(object sender, EventArgs e)
{
Type type = GetType();
const string scriptName = "alertPopup";
if (!ClientScript.IsStartupScriptRegistered(type,scriptName))
{
ClientScript.RegisterStartupScript(type, scriptName, "alert('Hello World!')", true);
}
}
Happy Programming!!

Reset MSSQL Table Identity

2011-04-14T14:28:00.000-07:00

We can use DBCC CHECKIDENT to reset the indentity value of SQL table. Let Say, you have a SQL table with Auto-Increment ID. If you delete the records from ID numbers 10-15 and insert the new record, it'll take the next ID i.e. 16.

But if you want the new record starts from ID: 11, you can do this with the help of simple single query:

DBCC CHECKIDENT ('TableName', RESEED, 10)

So, it'll reset the indentity to 10 and next ID will be autoIncrement by 1 i.e. 11.

Happy Programming !!

ROW_NUMBER Function--Paging Records Using SQL Server 2005 Database

2011-02-04T07:45:00.000-08:00

ROW_NUMBER returns a sequential number for each row returned in a resultset, starting from 1. It can help with paging records for the database applications.
Let see the Example:

Select name, sender, date from (select ROW_NUMBER() over (order by date ASC)
as row, name, date from table) as table_Row_Numbers

If you have lots of records on your database and you want certain number of records to retrieve (at a time) from database to make it faster and efficient, then this is the best idea.

Select name, sender, date from (select ROW_NUMBER() over (order by date ASC)
as row, name, date from table) as table_Row_Numbers where row>=1 and row<=50

Let say you have 100 thousands records and you made a interface to access those records. If you try to get all the records at a time, it might crash the internet explorer. In this situation, you can write stored procedure, that would accept 'startRowIndex' and 'MaximumRows' (where you define the maximum rows to display in a page). In the mean time, if you display those records in a Gridview control and want to do the header sorting when click on it, you can pass sortExpression parameter in your SP. I have written this SP for my database search application last week:

Create procedure RajSearch
@StartRowIndex INT,
@MaximumRows INT,
@SortExpression nvarchar(100),
@StringPass nvarchar(100)

As
declare @RajTable table
(rowId int identity(1,1), ID int,
Name nvarchar(255),
Sender nvarchar(255),
Recipient nvarchar(255),
date varchar(255),
Reel nvarchar(255),
Reference_URL nvarchar(255) )

Declare @ID int,
@Name nvarchar(255),
@Sender nvarchar(255),
@Recipient nvarchar(255),
@Date nvarchar(255),
@Reel nvarchar(255),
@Reference_URL nvarchar(255)

--Define a cursor
Declare Search cursor Fast_Forward for

with FixedList as(
SELECT ID,Name,Sender,Recipient,Date,Reel,Reference_URL,
ROW_NUMBER() OVER
(ORDER BY
(case when @sortExpression = 'Sender ASC' THEN Sender END) ASC,
(case when @sortExpression = 'Sender DESC' THEN Sender END) DESC,
(case when @sortExpression = 'Recipient ASC' THEN Recipient END) ASC,
(case when @sortExpression = 'Recipient DESC' THEN Recipient END) DESC,
(case when @SortExpression = 'Date ASC' THEN Date END) ASC,
(case when @SortExpression = 'Date DESC' THEN Date END) DESC,
(case when @sortExpression = 'Reel ASC' THEN Reel END) ASC,
(case when @sortExpression = 'Reel DESC' THEN Reel END) DESC )

AS [RowNo] from TestTable

where (Name like '%' + @stringPass + '%' or
Sender LIKE '%' + @stringPass + '%' or
Recipient LIKE '%' + @stringPass + '%' or
Date LIKE '%' + @stringPass + '%' or
Reel LIKE '%' + @stringPass + '%' or
Reference_URL LIKE '%' + @stringPass + '%'))

Select Sender, Recipient, Date, Reel, Reference_URL from FixedList
where RowNo BETWEEN @StartRowIndex AND @StartRowIndex + @MaximumRows

open Search
while 1=1
begin
fetch next from Search
into @Sender, @Recipient,@Date,@Reel,@Reference_URL

if @@FETCH_STATUS<>0
break
if(@Reel is not null and @Reference_URL is not null)
begin
--open in a new window
set @Reel = '<a href=' + '"' + @Reference_URL + '"' + ' target="_blank">' + @Reel + '</a>'
end

insert into @RajTable(ID,Name, Sender,
Recipient, Date, Reel,Reference_URL)
values
(@ID,@Name,@Sender, @Recipient, @Date@Reel,@Reference_URL)

end
close Search
deallocate Search

select Sender,Recipient,Date,Reel from @RajTable order by Date
return

Happy Programming!!

Encode and Decode the QueryString parameter value

2011-01-11T07:24:00.000-08:00

Let say, I have a web application where I send data from a page to another page via QueryString. If the Querysting value contains '&' sign, it'll truncate the data after this sign and we won't get the matched data:

<td>
<asp:HyperLink ID="HyperLink1" runat="server"
NavigateUrl='<%#DataBinder.Eval(Container.DataItem,"book_name","chapter_Matches.aspx?book_name={0}")%>'>
<%# DataBinder.Eval(Container.DataItem, "book_name")%></asp:HyperLink>

</td>

and in chapter_Matches.aspx:

str = Request.QueryString["book_name"].ToString();

Example: If the book_name is 'Chemistry', it'll work on this case but if the book_name is 'Chemistry & Physics', it'll truncate the value after '&' sign and only pass 'Chemistry' and the URL is chapter_matches.aspx?book_name=chemistry.

to pass this value(Chemistry & Physics) via QueryString, encode the specific parameter value, by itself and on the receiving page, do the opposite:

<td>
<asp:HyperLink ID="HyperLink1" runat="server"
NavigateUrl='<%# string.Format("chapter_Matches.aspx?book_name={0}"), SanitizeURLString(DataBinder.Eval(Container.DataItem, "book_name").ToString()))%>' Text ='<%# DataBinder.Eval(Container.DataItem, "book_name")%' </asp:HyperLink>

</td>

on aspx.cs page:

protected string SanitizeURLString(string RawURLParameter)
{

string Results;

Results = RawURLParameter;

Results = Results.Replace("<", "%3C");

Results = Results.Replace(">", "%3E");
Results = Results.Replace("#", "%23");
Results = Results.Replace("%", "%25");
Results = Results.Replace("{", "%7B");
Results = Results.Replace("}", "%7D");
Results = Results.Replace("", "%7C");
Results = Results.Replace("\\", "%5C");
Results = Results.Replace("^", "%5E");
Results = Results.Replace("~", "%7E");
Results = Results.Replace("[", "%5B");
Results = Results.Replace("]", "%5D");
Results = Results.Replace("`", "%60");
Results = Results.Replace(";", "%3B");
Results = Results.Replace("/", "%2F");
Results = Results.Replace("?", "%3F");
Results = Results.Replace(":", "%3A");
Results = Results.Replace("@", "%40");
Results = Results.Replace("=", "%3D");
Results = Results.Replace("&", "%26");
Results = Results.Replace("$", "%24");

return Results;

}

On receiving aspx.cs page:

str = DeSanitizeURLString(Request.QueryString["book_name"].ToString());

protected string DeSanitizeURLString(string RawURLParameter)
{

string Results;

Results = RawURLParameter;

Results = Results.Replace("%3C", "<");

Results = Results.Replace("%3E", ">");
Results = Results.Replace("%23", "#");
Results = Results.Replace("%25", "%");
Results = Results.Replace("%7B", "{");
Results = Results.Replace("%7D", "}");
Results = Results.Replace("%7C", "");
Results = Results.Replace("%5C", "\\");
Results = Results.Replace("%5E", "^");
Results = Results.Replace("%7E", "~");
Results = Results.Replace("%5B", "[");
Results = Results.Replace("%5D", "]");
Results = Results.Replace("%60", "`");
Results = Results.Replace("%3B", ";");
Results = Results.Replace("%2F", "/");
Results = Results.Replace("%3F", "?");
Results = Results.Replace("%3A", ":");
Results = Results.Replace("%40", "@");
Results = Results.Replace("%3D", "=");
Results = Results.Replace("%26", "&");
Results = Results.Replace("%24", "$");

return Results;

}

Happy Programming!!

Save and display images to and from SQL Database C#

2010-09-23T11:03:00.000-07:00

Save images on the Database
Let say, I've a fileupload control to browse images and a button to upload images on SQL database.

<asp:FileUpload ID="FileUpload1" runat="server"/>

<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click"/>

write this code inside the button click event:

protected void btnSubmit_Click(object sender, EventArgs e)
{
//to store image into sql database.
if (FileUpload1.PostedFile != null &&
FileUpload1.PostedFile.FileName != "")
{
byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);

// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Pictures(ImageName,Image)" +
" VALUES (@ImageName,@Image)";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;

SqlParameter ImageName = new SqlParameter
("@ImageName", SqlDbType.VarChar, 50);
ImageName.Value = strImageName.ToString();
cmd.Parameters.Add(ImageName);

SqlParameter UploadedImage = new SqlParameter("@Image", SqlDbType.Image, imageSize.Length);
UploadedImage.Value = imageSize;
cmd.Parameters.Add(UploadedImage);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
if (result > 0)
lblMessage.Text = "File Uploaded";
lblSuccess.Text = "Successful !";

}
}

Display the images from database:
Let's display the image on Gridview
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1"
AutoGenerateColumns="False" DataKeyNames="ID" Width="245px">
<Columns>
<asp:BoundField DataField="TreatmentID" HeaderText="ID" Visible="false"
SortExpression="TreatmentID" />
<asp:BoundField DataField="imageName" HeaderText="ImageName"
SortExpression="imageName" />
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<asp:Image ID="Image1" runat="server"
ImageUrl='<%#"Handler.ashx?ID=" + Eval("ID")%>'/>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TreatmentConnectionString %>"
SelectCommand="SELECT * FROM [Pictures]">

and add a handler class with the below code:

using System;
using System.Web;
using System.Data.SqlClient;

public class Handler : IHttpHandler {

public void ProcessRequest (HttpContext context)
{

try
{
SqlConnection con = new SqlConnection(GetConnectionString());

// Create SQL Command
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select ID,imageName, Image from Pictures" +
" where ID =@ID";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;

SqlParameter ImageID = new SqlParameter
("@ID", System.Data.SqlDbType.Int);
ImageID.Value = context.Request.QueryString["ID"];
cmd.Parameters.Add(ImageID);
con.Open();
SqlDataReader dReader = cmd.ExecuteReader();
dReader.Read();
context.Response.BinaryWrite((byte[])dReader["Image"]);
dReader.Close();
con.Close();
}

catch(Exception ex)
{
ex.Message.ToString();
}
}

public bool IsReusable {
get {
return false;
}
}

}

Mobile Device detection in ASP.NET and use CSS to display

2010-08-20T07:25:00.000-07:00

There are basically two ways to detect mobile devices:
-in terms of display (480px/800px)
-in terms of user agent.

My code used to detect using the display but last week Android updated their software (changed the display from 480px to 800px) so all the mobile tweaks I made for our apps are no longer work on Droid Phones.

That's why I want to follow the second method to detect the mobile devices and in future no matter whether they channged the display setting or not.

Let's identify the User Agent(e.g. Android) and switch style sheets depending. (I don't want to redirect to another page, just switch style sheets).

In aspx.cs page:

protected override void OnInit(EventArgs e)
{
string userAgent = Request.UserAgent;
if (userAgent.Contains("BlackBerry")
(userAgent.Contains("iPhone") (userAgent.Contains("Android"))))
{
//add css ref to header from code behind
HtmlLink css = new HtmlLink();
css.Href = ResolveClientUrl("~/mobile.css");
css.Attributes["rel"] = "stylesheet";
css.Attributes["type"] = "text/css";
css.Attributes["media"] = "all";
Page.Header.Controls.Add(css);
}
}
Mobile.cs:

#header {
display: none;
}
#body {
margin: 0;
width: 100%;
min-width: 100%;
font-size: 12px;
background-image: none;
empty-cells: hide;

}
#column_left {
float: none;
width: 100%;
padding-left: 5px;
}
#column_right {
float: none;
width: 100%;
padding-left: 5px;
}
#menuwrapper {
display: none;
}
#sssubsearch {
display: none;
}
h2 {
font-size: 16px;
text-align: center;
}
h3, h4 {
font-size: 1em;
/* border-bottom-width: 1px;
border-bottom-style: solid;
border-bottom-color: #666;*/
}
h3 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #E6E6E6;*/
font-size: 16px;
padding-left: 0;
margin: 0;
padding-top: 10px;
padding-bottom: 0;

}
h4 {
/* background-repeat: repeat-x;
background-position: center top;
background-color: #FFC;*/
font-size: 16px;
color: #600;
padding-left: 0;
margin-left: 0;
empty-cells: hide;
}
table {
width: 98%;
font-size: 14px;
empty-cells: hide;
}
#navigation {
display: none;
}
.left {
float: left;
vertical-align:top;
}
.right {
float: right;
vertical-align:top;
}

hr {display: none;}

Happy Coding !!

Multiple SQL rows merge into single row if the ID is same

2010-07-19T13:09:00.000-07:00

Let's create with a table example:

create table mytable (id int identity(1,1), PersonID int, unit varchar(10))
insert into mytable values (1,'Che YYYY')
insert into mytable values (1,'Mat')
insert into mytable values (1,'Phy XXXX')

--Replace space in your column with a special character and remove it in your select statement
UPDATE mytable
SET unit=REPLACE(unit,' ','')

SELECT PersonID, REPLACE(Units,'', ' ') as Units
FROM (SELECT t1.PersonID,
Units =REPLACE( (SELECT Unit AS [data()]
FROM mytable t2
WHERE t2.PersonID = t1.PersonID
ORDER BY Unit
FOR XML PATH('')
), ' ', ',')
FROM mytable t1
GROUP BY PersonID)
t0 ;
drop table mytable

Merge SQL tables

2010-06-10T13:16:00.000-07:00

Let say, I have two tables:
table1:(Fields:PersonID, FirstName, LastName, Role, Department)
table2:(fields: PersonID, Unit).

and I want to create a new table with these fields:
table: ( Fields:PersonID, FirstName, LastName, Role, Department, Unit).

here is the query:

SELECT table1.*, table2.Unit
INTO new_table_name
FROM table1 inner join table2
on table1.PersonID = table2.PersonID

If table1 and table2 has one-to-many relation then first use this postto make it one-to-one relaion; otherwise it'll create multiple PersonID into new table, which you don't want.

Bind stored procedure data into Gridview

2010-05-26T13:33:00.000-07:00

First, let's create a stored procedure with a name: 'Dynamic_table'

and sqlconnection 'conn' and then do the following:

SqlCommand cmd = new SqlCommand("Dynamic_table", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@tableName",

SqlDbType.VarChar).Direction = ParameterDirection.Input;
cmd.Parameters["@tableName"].Value = Session["tableValue"];

cmd.Connection.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

GridView1.DataSource = dt;
GridView1.DataSourceID = string.Empty;
GridView1.DataBind();
cmd.Connection.Close();

Happy Programming !!!

Stored Procedure to select dynamic table @ runtime

2010-05-26T13:26:00.000-07:00

Create Proc Dynamic_table
@tableName varchar(50)

as

begin

declare @sql nvarchar(100)

Set @sql='Select * from '+@tableName

exec sp_executesql @sql

end

SQL query to add Zero prefix

2010-05-25T14:19:00.000-07:00

Let say,
I have a Database column 'ISSN' and data in it, should be 8 digits long. When I import data from excel to SQL table, it truncates the initial 1-3 zeros because it doesn't like zero as first digits when you mark the column as int but if you mark as text that's fine.
Now, let's write SQL query so that if the ISSN is 5 digits long then add 3 zeros to prefix.
e.g If the ISSN is 45678 then it should be '00045678'
If it is 6 digits long, then add two zeros to prefix....If 8 digits long keep as it is.

Hence, the SQL query is:

update table
set ISSN = RIGHT('00000000'+ISSN,8)

Happy Programming !!!

Special characters globally changes in SQL server

2009-12-18T06:49:00.000-08:00

I wrote a SQL query in this blog to replace special characters globally. Now , I am going to modify this query with another ASCII UTF-8 table concept.

Look ASCII UTF-8 table and find the character whose description is 'Acute accent, spacing acute', it's not apostrophe.

If you have this character in your database table data, it doesn't allow you to update your table. It can be replaced manually but if you have this character in many places you should have query to replace those bad characters.

Let's look that table, you can see the Raw Encoding for that bad character is:

0xB4

which is in hexdecimal notation.

let's convert to base 10 value:

B means 11=>16*11=176

B4=>176+4=180

so,

180 = ' (x b4)

147, 148 = " (x 93, 94)

Hence the queries:

//replace by apostrophe
UPDATE table
SET ColumnName= replace(ColumnName, CHAR(180), '''')

UPDATE table
SET ColumnName= replace(ColumnName, CHAR(148), '"');

UPDATE table
SET ColumnName= replace(ColumnName, CHAR(147), '"');

Happy Coding !!!

Repeater Hyperlink navigate with condition

2009-11-25T08:48:00.000-08:00

Let say, we have a repeater to display the sql table data. When Somebody search in web page it displays the title field data with hyperlink to go to details page.

We can do like this way:

<asp:Repeater>

<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%#DataBinder.Eval(Container.DataItem,"Identifier","TitleDetail.aspx?Identifier={0}" ) %>'> <%# DataBinder.Eval(Container.DataItem,"Title")%></asp:HyperLink>

</ItemTemplate>

</asp:Repeater>

It works fine for a single detail page.

but

let say, I want to go to details page according to the identifier's value.

The Identifier is also from the database. It consists combination of characters and numbers(IM234, MC45, XY67). All the identifiers have same characters but different numbers like (IM234, IM235..., MC45,MC46...XY67,XY68...)

I want to naviagate the page with repect to these identifers e.g

if identifiers contains IM, go to titleIMdetails.aspx? identifier={0}

if identifiers contains MC go to titleMCdetails.aspx ? identifier={0}

[The purpose of this is to have separate header for IM, MC identifiers]

We can handle this way:

Put this code in your aspx page:

<asp:Repeater>

<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl='<%#returnURL(DataBinder.Eval(Container.DataItem, "Identifier"))%>'><%# DataBinder.Eval(Container.DataItem,"Title")%></asp:HyperLink>

</ItemTemplate>

</asp:Repeater>

In Aspx.cs page:

protected string returnURL(object paramID)
{

string m_paramID;
m_paramID = (string)paramID;
string retURL = "";
if (m_paramID.Contains("IW"))
retURL = "IWTitleDetail.aspx?Identifier=" + paramID;
else
retURL = "MsTitleDetail.aspx?Identifier=" + paramID;

return retURL;
}

Happy Coding !!!

Unable to cast object of type system.DBNull to type system.string

2009-11-05T08:31:00.000-08:00

Last week, I got this error when I was working on my web apps.

The code which has error:

protected string returnURL(object paramID)

{

string m_paramID;

m_paramID = (string)paramID;

string retURL = "";

if (m_paramID.Contains("IWA"))

retURL = "IWTitleDetail.aspx?Identifier=" + paramID;

else

retURL = "MsTitleDetail.aspx?Identifier=" + paramID;

return retURL;

}

Then, I changed to this and it solved the problem:

protected string returnURL(object paramID)

{

string retURL = "";

string m_paramID;

if (paramID == DBNull.Value)

{ }

else

{

m_paramID = (string)paramID;

if (m_paramID.Contains("IWA"))

retURL = "IWTitleDetail.aspx?Identifier=" + paramID;

else

retURL = "MsTitleDetail.aspx?Identifier=" + paramID;

}

return retURL;

}

Happy Coding!!

Clear default text onClick - restore if nothing entered

2009-10-25T08:32:00.000-07:00

Let's write a JavaScript function which will clear the "default" text in a field when the user clicks into it and the second function will replace the default text in the field if the field was left blank.

Put this inside the <head> </head> tag:

<script type="text/javascript">

function clickclear(thisfield, defaulttext) {

if (thisfield.value == defaulttext) {

thisfield.value = "";

}

function clickrecall(thisfield, defaulttext) {

if (thisfield.value == "") {

thisfield.value = defaulttext;

}

</script>

Then add the following onclick, onblur events to your field:

<asp:TextBox ID="rajTextBox" runat="server" Text="Rajendra Sedhain"

Width="369px" Height="18px" onclick="clickclear(this, 'Rajendra Sedhain')" onblur="clickrecall(this,'Rajendra Sedhain')"></asp:TextBox>

Happy Programming !!!

Disabling an ASP.Net Validator through Javascript

2009-10-05T14:08:00.000-07:00

Let say, I have two dropdown lists, both have required field validation controls and use Javascript to hide the second dropdown when choose the specific items from the first dropdown list. It works fine but the problem is when choose the specific items from the first dropdown it hides the second one but it still displays the required field validator.

So, the solution:

When we hide, we can disable the validator at the same time using ValidatorEnable().

function validate()
{

var myValidator = document.getElementById('myValidatorClientID');
ValidatorEnable(myValidator, false);

}

To make enable replace that 'false' by 'true'.

Happy Programming !!!

Change Hidden Field Value

2009-09-25T14:33:00.000-07:00

Let say, you have a Textbox with some value. When you choose some items from a dropdown list, this textbox will be hidden but the value will be there. When you submit the form, this textbox's value will be submitted.

To avoid this, we can use javascript so that when the textbox hidden, we can make it empty or assign something specific value whatever you want.


function assignSomeValue()
{
// to hide the textbox
document.getElementById('<%= rajTextBox.ClientID%>').style.visibility = "hidden";
// to assign somevalue after hidden
documnet.getElementById('<%= rajTextBox.ClientID%>').value="assign something";

}

Happy Programming !!!

Calling Stored Procedure from C# & Display the records

2009-09-05T06:52:00.000-07:00

Let's create a sample stored procedure first,

Create procedure GetSchoolName (@ID int)

select SchoolName, Date from School where ID like @ID;

return;

The code includes @ID parameter which is an input parameter that obtains the search string to perform a "like" search in school table.

In our c# code, we are going to pass variable ID (at run time) to this procedure and getting back the records that matches with that ID

Hence, the c# code:

try

{

SqlDataReader rdr = null;

//Create a connection to the SQL Server

SqlConnection conn = new SqlConnection(DataAccess.GetConnectionString());

//Create a command object & then set to the connection

SqlCommand cmd = new SqlCommand("dbo. GetSchoolName", conn);

//Set the command type as storedProcedure

cmd.CommandType = CommandType.StoredProcedure;

//get the variable ID from textbox control

int id = Convert.ToInt32(id.Text);

//Create & add a parameter to parameters collection for stored procedure

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;

//Open the connection

conn.Open();

//execute command & read the data using SqlDataReader

rdr = cmd.ExecuteReader();

//display records into listbox

while (rdr.Read())

{

listBox1.Items.Add(rdr.GetValue(0).ToString());

}

//Records display in a table

TableRow tr;

TableCell tc;

while (rdr.Read())

{

tr = new TableRow();

tc = new TableCell();

tc.Text = rdr["SchoolName"].ToString() + " " + rdr["Date"].ToString();

tr.Cells.Add(tc);

table1.Rows.Add(tr);

}

conn.Close(); //close connection

rdr.Close(); // close SqlDataReader

}

Happy Coding !!!

Sqlparameter is already contained by another Sqlparameter Collection

2009-08-25T07:36:00.000-07:00

"Sqlparameter is already contained by another Sqlparameter Collection" --This

is the problem I faced last week while I created two SqlCommand. First one, to

find the variable Id 'a' and second one pass that 'a' to the stored procedure.

The solution, I added the SqlCommand.Parameters.Clear() to the code and the problem gone.

As you can in the code, I created SqlCommand Command and should be destroyed at the end because

C# is the managed language that handles garbage collections.

Code example:

SqlConnection conn =

new SqlConnection(DataAccess.GetConnectionString());

string strSQLCommand =

"select Id from Schools WHERE SchoolName= '" + name + "'";

SqlCommand command =

new SqlCommand(strSQLCommand, conn);

int a = Convert.ToInt32(command.ExecuteScalar());

//write this line of code before using another command execute function

command.Parameters.Clear();

...................................................

SqlDataReader rdr = null;

SqlCommand cmd = new SqlCommand("dbo.GetSchoolName", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@ID", SqlDbType.Int).Value = a;

rdr = cmd.ExecuteReader();

...................................................

Happy Coding !!!

A potentially dangerous Request.Form value was detected from the client

2009-08-05T07:59:00.000-07:00

I had a run time error on my web application last week . It was a request form application whichhas user input testboxes. When I looked into event viewer on the server, the actual problem was:

A Potentially dangerous Request.Form value was detected from the client.

Basically it means that you can't post values containing HTML ( or script ) tags to the server.

In other words - if you have a textbox and the visitor enters something like :

and then presses Submit button this error will occur since the posted value(s) contains HTML tags.

There are different solutions to solve thhis problem, you can read more at ASP.Net site. Many People suggests changing web.config file which is a bad solutions since it'll affect all pages on your Site.

The simple solution is add a tag validateRequest="false" into the Page-directive on top of the page. When request validation is disabled, content can be submitted to a page but developer should ensure that content is properly encoded or processed.

Happy Programming!!

Paramaterized Queries.

2009-07-25T07:54:00.000-07:00

Parameterized queries prohibit the sql injection in your web application.

It allows you to safely write code like the following:

Let say, I have a SqlDataReader function to get the details of the particular ID:

public static SqlDataReader GetDetail(int id)

{

string sql = "select * from Raj_table where id = @id ";

SqlParameter paramUserId = new SqlParameter("id", SqlDbType.Int);

paramUserId.Value = id;

// I like to use the SqlHelper class

return SqlHelper.ExecuteReader(GetConnectionString(), CommandType.Text, sql, paramUserId);

}

Firstly, this parameter ensures that the paramater is an INT so a string value would throw an exception here. Also, if we were using a VARCHAR parameter, the SqlParameter value assignment automatically escapes the string for us.

If you run SQL Profiler and observe the queries, you'll notice that they are actually execute via a Stored Procedure (sp_executesql)

Happy Programming!!

Sending Email with ASP.NET

2009-07-10T12:26:00.000-07:00

Email consists:

-From

-To

-CC

-BCC

-Subect

-Body

depend on these fields, below is the ASP.NET code to send the message:

First, add this namespace 'using System.Web.Mail' to your aspx.cs page.

then inside the button_click event, add this code:

MailMessage mail = new MailMessage();

mail.From = "someone@google.com";

mail.To = "somebody@google.com";

mail.Cc = "badgirl@google.com";

mail.Subject = "Just to say hi";

mail.Bcc = "badboy@google.com";

mail.Body = "Happy B'day to you!!";

//If you are using different mail server, please replace this one by your own...

SmtpMail.SmtpServer = "smtp.gmail.com";

SmtpMail.Send(mail);

Sending Attachments:

mail.From = "someone@google.com";

mail.To = "somebody@google.com";

mail.Cc = "badgirl@google.com";

mail.Subject = "Just to say hi";

mail.Bcc = "badboy@google.com";

mail.Body = "Happy B'day to you!!";

mail.BodyFormat = MailFormat.Text;

mail.Attachments.Add(new MailAttachment("c:\\temp\\test.pdf"));

SmtpMail.SmtpServer = "smtp.gmail.com";

SmtpMail.Send(mail);

Happy Coding!!!

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

2009-06-25T08:58:00.000-07:00

I keep getting the following errors on one of my application sites after clicking for many times:

"Timeout expired: The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

Below was my code:

SqlConnection conn = new SqlConnection(DataAccess.GetConnectionString());

conn.Open();

SqlDataSource1.SelectCommand = sql;

SqlDataSource1.SelectParameters.Clear();

Repeater1.DataSource = SqlDataSource1;

Repeater1.DataSourceID = string.Empty;

Repeater1.DataBind();

conn.Close();

I found later, this typically happens when connections are not closed after they are used. You should put everything in a try/catch/finally, with the connection being closed in the Finally to ensure that it always gets closed no mater what.

Hence the solution is:

SqlConnection conn = new SqlConnection(DataAccess.GetConnectionString());

conn.Open();

try

{

SqlDataSource1.SelectCommand = sql;

SqlDataSource1.SelectParameters.Clear();

Repeater1.DataSource = SqlDataSource1;

Repeater1.DataSourceID = string.Empty;

Repeater1.DataBind();

}

finally

{

SqlConnection.ClearPool(conn);

SqlConnection.ClearAllPools();

}

Note: If there is any return statement before you close the connection, there might be a connection leak ; be careful on that.

Return value from stored procedure to c#

2009-06-10T08:45:00.000-07:00

Suppose you have a stored procedure which returns a bit value 0 or 1. If you want to call that procedure from c# and get that bit value, do like this below:

let say, the stored procedure has:

Name: dbo.sp_dateCheck

Variables:

@laptop varchar(50),

@startdate DateTime,

@enddate DateTime

and return bit variable: @CondFlag

SqlCommand com = new SqlCommand("dbo.sp_dateCheck", conn);

com.CommandType = CommandType.StoredProcedure;

try

{

conn.Open();

com.Parameters.Add(new SqlParameter("@laptop", Laptop.Text));

com.Parameters.Add(new SqlParameter("@startdate", startDate. DateTime));

com.Parameters.Add(new SqlParameter("@enddate", endDate. DateTime));

//read the returned value from stored procedure

com.Parameters.Add("@CondFlag", SqlDbType.Bit);

com.Parameters["@CondFlag"].Direction = ParameterDirection.ReturnValue;

com.ExecuteNonQuery();

int ReturnedVal = (int)com.Parameters["@CondFlag"].Value;

if (ReturnedVal == 0)

{ // do this

}

else

{//do this

}

catch

{

}

Happy Programming!!!

Running a SQL query to replace certain characters in a table

2009-05-25T14:16:00.000-07:00

Let say,

You have a database that contains many characters and symbols. If you want to replace those , just run the following SQL query:

(Here, suppose a symbol ~! need to replace by - then your query will be:)

UPDATE table

SET ColumnName= replace(LTRIM(RTRIM(ColumnName)), '~!', '-')

Happy Programming!:)

Master page with Searchbox

2009-05-10T10:15:00.000-07:00

Consider a master page with a search functionality and other number of content pages derived from that master page, so that searchbox appears in all pages. If you want to display a search results in Search.aspx page no matter where you are( in other content pages),add this code:

In MasterPage.cs:

Protected void search_button_Click(object sender, EventArgs e)

{

//redirect textbox's value to search.aspx page

Response.Redirect(@"~/search.aspx?q=" + Server.UrlEncode(txtSearch.Text));

}

In Search.aspx.cs:

protected void Page_Load(object sender, EventArgs e)

{

//read the master page's redirected value

string str = Request.QueryString[@"q"];

}

--Happy Coding

Disable Browser Back Button

2009-04-15T11:24:00.000-07:00

If you want to disable going back in the previous page, I am going to show you very easy method to do this:

Suppose you are in Page 1 and go to page 2. If you want to go back to page 1 , you are able to go. But now, if you want to disable that going back features, add the following script in the body tag of the page 1:

body onload=”if(history.length>0)history.go(+1);”

This works only for IE and not for Mozilla Firefox. For Mozilla do this:

add this script within head:

function noBack()
{
window.history.forward()
}
noBack();
window.onload=noBack;
window.onpageshow=function(evt)
{
if(evt.persisted)noBack()
}
window.onunload=function()
{
void(0)
}

It works not only with Firefox but also with IE, Safari, Opera.

Close the popup image when click on it

2009-03-15T11:03:00.000-07:00

To close the popped up image when click on , do the following steps:

Create ShowImage.aspx page and add a image control with onclick property like:

asp:Image ID="Image1" runat="server" onclick = "window.open('','_self');window.close();"

And in the page load of ShowImage.aspx do this way:

if (Request.QueryString["image"] != null)
{
Image1.ImageUrl = Request.QueryString["image"];
}

Now, on the parent page write this script:

var Popup;
function popUp(url)
{
Popup = window.open(url, "Popup", 'toolbar=0,scrollbars=0,location=0,statusbar=0,menubar=0,resizable=0,width=100,height=100,left = 490,top = 262');
Popup.focus();
}
function ChangeImg(obj)
{
//Show in popup
var radio = obj.getElementsByTagName("input");
for (var i=0;i less than radio.length;i++)
{
if (radio[i].checked)
{
popUp("ShowImage.aspx?image=images/" + radio[i].value + ".jpg");
}
}
}

Note: Use the general sign for 'Less than' in the for loop. I am unable to publish here because it acts like HTML tags and won't let me go.

Images changing when clicking the radiobutton on the radiobutton list

2009-02-25T10:31:00.000-08:00

If you have a ASP.NET C# form and use a radiobutton list. Suppose there are 4 radiobutton on the list.Default, no images loaded and when click the first radiobutton in the list, it displays a "1.jpg" and when click second radiobutton changes the image and so on...

For this, use the onclick function of the radiobuttonlist.

onclick = "ChangeImg(this);

and try this javascripts:

var Popup;
function popUp(url)
{
Popup = window.open(url, "Popup", 'toolbar=0,scrollbars=0,location=0,statusbar=0,menubar=0,resizable=0,width=100,height=100,left = 490,top = 262');
Popup.focus();
}
function ChangeImg(obj)
{
//Show in popupvar radio = obj.getElementsByTagName("input");

for (var i=0;i less than radio.length;i++)
{
if (radio[i].checked)
{
popUp("images/" + radio[i].value + ".jpg");break;
}
}
}

Auto close Popped up images after certain time

2009-02-01T11:15:00.000-08:00

If you want to close your pop up image after 10 sec. just follow me:

Add a javascript function to the popup page
function Close()
{
window.close();
}
And to the body tag onload event add this

onload = "setTimeout('Close();', 10000)"

it will close the popup after 10 secs.

NOT save value on PostBack

2009-01-25T11:39:00.000-08:00

If you have different ASP.NET controls in your application e.g checkbox, listbox, textbox, dropdowns etc. and you want to clear the value that it holds on PostBack. Then do the

EnableViewStateProperty to False.

or you can do

Textbox1.Text="";

after submitting form data.

Import excel data into SQL Server table using ASP.NET

2009-01-15T11:31:00.000-08:00

string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;";

using (OleDbConnection connection = new OleDbConnection(xConnStr))
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();

// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString =DataAccess.GetConnectionString() ;

// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "dbo.ExcelTest";
bulkCopy.WriteToServer(dr);
}
}
}

Insert data into multiple tables-Dynamic Query

2009-01-02T10:51:00.000-08:00

If you have multiple related tables and need to insert data on those tables. You can write stored procedures or the dynamic query. I am going to explain here the dynamic query.

First, insert the data into primary table and read the auto generated ID from that table and insert other data and that ID where it is foreign key. Each different query should be separated by semicolon(;).

To find ID of the row when data is inserted on a table, run a sql command " scope_identity()" right after insert the data.

Hence the Dynamic query is:

string sql = " DECLARE @ID int;@ID1 int; @ID3 int; @ID4 int;@ID5 int;";
sql = sql + "Insert into table1(Url,head,foot) values ";
sql = sql + "(@url,@head,@foot);";
sql = sql + " SET @ID = SCOPE_IDENTITY();";
sql = sql + " Insert into table2(table2_Name) values(@table2Name);";
sql = sql + " SET @ID1 = SCOPE_IDENTITY();";
sql = sql + " Insert into table3(tabl3_Name) values(@table3Name);";
sql = sql + " SET @ID2 = SCOPE_IDENTITY();";
sql = sql + " Insert into table2_table3(table1_ID,table2_ID) values (@ID1,@ID2);";
sql = sql + " SET @ID3 = SCOPE_IDENTITY();";
sql = sql + " INSERT INTO dbo.table1_table2(table1_ID,table2_table3_ID) VALUES (@ID,@ID3);";

SqlConnection conn = new SqlConnection(GetConnectionString());
conn.Open();
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;

IDbDataParameter userParam = cmd.CreateParameter();
userParam.ParameterName = "@url";
userParam.Value = formReserve.URL;
userParam.DbType = System.Data.DbType.String;
cmd.Parameters.Add(userParam);
IDbDataParameter userParam = cmd.CreateParameter();

......

.....

.....

cmd.ExecuteNonQuery();

}

The GridView 'GridView1' fired event Sorting which wasn't handled

2008-12-15T11:53:00.000-08:00

If you got this error message when sorting a Gridview:
The GridView 'GridView1' fired event sorting which wasn't handled.

Then do the following add/changes in your code behind.

Aspx.cs Page:
public SortDirection GridViewSortDirection
{
get
{
if (ViewState["sortDirection"] == null)
ViewState["sortDirection"] = SortDirection.Ascending;
return (SortDirection)ViewState["sortDirection"];
}
set { ViewState["sortDirection"] = value; }
}

protected void Gridview1_Sorting(object sender, GridViewSortEventArgs e)

{
string strSortDir = null;switch (GridViewSortDirection)
{
case SortDirection.Ascending:GridViewSortDirection = SortDirection.Descending;
strSortDir = "DESC";
break;
case SortDirection.Descending:GridViewSortDirection = SortDirection.Ascending;
strSortDir = "ASC";
break;
}
SqlDataSource1.SelectCommand = "SELECT ...... FROM tableName order by " + e.SortExpression + " " + strSortDir;
}

More: http://forums.asp.net/t/1286994.aspx

Parameterized SQL Query in sqldatasource and bind with Gridview

2008-11-15T12:19:00.000-08:00

To avoid SQL injections you have to write stored procedure or the parametrized query. If you have a sqldatasource, want to pass the paramterized query to that datasource and finnally display with Gridview...Just customize your code like follow:

string sql = "SELECT Guid as Guid,[ID], [Family Name] AS Family_Name, [Given Name] AS Given_Name, [Region], [MacroRegion], [Country], [Years], [Date] FROM [Author] where [Given Name] LIKE '%' + @Given_Name + '%'";

SqlDataSource1.SelectCommand = sql;
SqlDataSource1.SelectParameters.Clear();
SqlDataSource1.SelectParameters.Add("Given_Name", TypeCode.String, searchTextBox.Text.Trim());
GridView1.DataSource = SqlDataSource1;
GridView1.DataSourceID = string.Empty;
GridView1.DataBind();

More:http://forums.asp.net/t/1309562.aspx

Creating A-Z lists

2008-10-28T11:09:00.000-07:00

If you want to creat A-Z lists in a Repeater control and implement so that people can click that charater and get value from database which start from that clicked charater.

ASPX Page:

Aspx.cs Page:
protected void Repeater1_RowCreated(object sender, RepeaterItemEventArgs e)
{
//Create A-Z listArrayList ab = new ArrayList();for (int i = 0; i < datasource =" ab;Repeater2.DataSourceID">

protected void Repeater1_RowCommand(object sender, RepeaterCommandEventArgs e)
{
if (e.CommandName.Equals("AlphaPaging"))
{
//convert ASCII value to char/string.i.e. A=>65

int index;
index = Convert.ToInt32(e.CommandArgument);
String abc = Char.ConvertFromUtf32(index);
String sql = "select Title,Scope,CollectionAbstract,CollectionIdentifier" +
" from tblCollections_Collections" +" where RepositoryID in( " + repository + " ) and Title LIKE '" + abc + "%'";

Response.Write(sql);
SqlConnection conn = new SqlConnection(DataAccess.GetConnectionString());
conn.Open();
SqlDataAdapter ad = new SqlDataAdapter(sql, conn);
ad.SelectCommand = new SqlCommand(sql, conn);
DataSet ds = new DataSet();
ad.Fill(ds);

Repeater1.DataSource = ds;
Repeater1.DataSourceID = string.Empty;
Repeater1.DataBind();
conn.Close();
}
}
}

Result:

Both DataSource and DataSourceID are defined on ‘GridView1′. Remove one definition.

2008-10-15T09:07:00.000-07:00

This is the message that you get most of the times when you try to bind your gridview or any other bindable controls to multiple Datasources.When you bind your Gridview control to an ObjectDataSource or SqlDataSource on the design surface and then you try to bind the same Gridview in the code behind using the manual coding.

The Gridview has both Datasource and DatasourceID properties.Datasource referes to the ID of another component such as SqlDataSource/XMLDataSource/AccessDataSource, whichever applies, or ObjectDataSource to act as the Datasource.

When you have the following code:

SqlDataSource1.DataBind();
GridView1.DataSource = SqlDataSource1;
GridView1.DataBind();

You'll get this error:
Both DataSource and DataSourceID are defined on ‘GridView1′. Remove one definition.

To make working,

Just add a line of code before DataBind():
GridView1.DataSourceID = string.Empty;

Now, you are fine, no more error......

Happy Programming!!

SQL Trigger-To check the data at Notes Field

2008-09-15T10:45:00.000-07:00

I am going to write a Trigger to validate the column field in the table name 'LenderAddressAll' and the column name is 'Notes'.My trigger checks the data entered into that column and if it valid that's ok otherwise it'll pop up 'error'.The data format should be 000 xxxxxx.First three numbers should be 0's and then empty space and after that it should be any six digits(Total varchar(10)).

Create trigger tri

LenderAddressesALL

after

insert

AS
select lender.Notes from LenderAddressesALL lender join inserted i on i.Notes = lender.Notes

where i.Notes is NOT null
declare @temp varchar(10)

select @temp = Notes from inserted

if(len(@temp) = 10 and substring(@temp,1, 4) = '000 '

and cast(substring(@temp, 5, 10) as int) > 0

and cast(substring(@temp, 5, 10) as int) <>

return

else

begin print 'error!!!!'

RAISERROR ('Error!!', 16, 1)

rollback transaction

end
GO

Happy Programming!!