Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Archive for March, 2009

date-time-functions

Posted by Viral Sarvaiya on March 31, 2009


1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>’ + CONVERT(CHAR(19),GETDATE())

2) HERE IS MM-DD-YY FORMAT ==>’ + CONVERT(CHAR(8),GETDATE(),10)

3) HERE IS MM-DD-YYYY FORMAT ==>’ + CONVERT(CHAR(10),GETDATE(),110)

4) HERE IS DD MON YYYY FORMAT ==>’ + CONVERT(CHAR(11),GETDATE(),106) PRINT ‘5)

HERE IS DD MON YY FORMAT ==>’ + CONVERT(CHAR(9),GETDATE(),6) PRINT ‘

HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>’ + CONVERT(CHAR(24),GETDATE(),113)

Here is the output from the above script:

1) HERE IS MON DD YYYY HH:MIAM (OR PM) FORMAT ==>Feb 5 2003 5:54AM

2) HERE IS MM-DD-YY FORMAT ==>02-05-03

3) HERE IS MM-DD-YYYY FORMAT ==>02-05-2003

4) HERE IS DD MON YYYY FORMAT ==>05 Feb 2003

5) HERE IS DD MON YY FORMAT ==>05 Feb 03

6) HERE IS DD MON YYYY HH:MM:SS:MMM(24H) FORMAT ==>05 Feb 2003 05:54:39:567

************************************************

select GETDATE()

select month(GETDATE())

select year(GETDATE())

select day(GETDATE())

************************************************

SELECT DAY(‘1/1/2003’), MONTH(‘1/1/2003’), YEAR(‘1/1/2003’), DATEPART(DAY, ‘1/1/2003’), DATEPART(MONTH, ‘1/1/2003’), DATEPART(YEAR, ‘1/1/2003’) ***************************************************

select GETDATE()

SELECT DATEPART(day, GETDATE())

SELECT DATEPART(month, GETDATE())

SELECT DATEPART(year, GETDATE())

SELECT DATEPART(hour, GETDATE())

SELECT DATEPART(minute, GETDATE())

SELECT DATEPART(second, GETDATE()) **************************************************

select CONVERT(CHAR(10),GETDATE(),101) 12/19/2007

select CONVERT(CHAR(10),GETDATE(),103) 19/12/2007 ***************************************************

lblDate.Text = String.Format(“{0:dd.MM.yyyy}”, System.DateTime.Now)

Advertisements

Posted in General, Sql Server | Tagged: , , , , , | Leave a Comment »

How to perform case sensitive searches in SQL Server?

Posted by Viral Sarvaiya on March 31, 2009


This is another one of those questions, that gets asked at least once a day in the Microsoft SQL Server programming newsgroups. Over the years, different programmers have come up with different solutions, and I’ll be presenting some of those in this article.

A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters. That is, “Oracle” is the same as “ORACLE” or “oracle”. Let’s see what controls this case sensitiveness of SQL Server. In SQL Server 6.5 and 7.0, you can select a ‘sort order’, while installing SQL Server. Sort order defines how the characters supported by the code page are compared and evaluated. The default sort order is ‘case insensitive’. If you want SQL Server to perform case sensitive searches, you need to select binary sort order while installing SQL Server (6.5 or 7.0). In these versions (SQL Server 6.5 and 7.0), sort order is defined at the server level, and cannot be manipulated at the database level. If you want to change the sort order of an existing SQL Server 6.5 or 7.0 instance, you must rebuild the master database using rebuildm utility. Not a pleasant thing to do.

Terminology has changed, and now a sort order is called ‘collation’ in SQL Server 2000. Also ANSI SQL:99 uses the word ‘collation’. By default SQL Server 2000 gets installed with case insensitive collation. You must change the collation of the server while installing, if you want case sensitiveness. But the good news is, SQL Server 2000 lets you specify collation at the database as well as column level. That means, you can have a default SQL Server installation with case insensitive collation, but a specific database or a set of columns can have case sensitive collation.

Lets see why one would want to perform case sensitive searches. Classic example is, password comparisons. People use a combination of upper and lower case (mixed case) characters in their passwords, just to make the passwords difficult to guess. But there is no point in doing that, if the database disregards the case. To keep the users happy, and their passwords secure, programmers prefer case sensitive comparisons in this case.

Here is another example: Some people tend to get the MOST out of their database. Yes, from a performance point of view, a binary sort order or case sensitive collation will perform faster, but only at the expense of the usability. Think about a user searching your table for the phrase “fish and chips”, and not being able to find a match, just because, that phrase is actually stored in the database as “Fish and Chips”. If this happens to be a database serving an online catalog, you will lose some sales as well. One way of avoiding this problem would be, to store everything in lower case, and covert user input to lower case before comparing with the table data. Too much hassle, isn’t it? I wouldn’t recommend this approach. Not worth the performance you gain.

Here are some methods you can employ for case sensitive searching in SQL Server:

All the examples in this article, are based on the following table and data. Requirement is to implement case sensitive search for CustID and CustPassword columns:

CREATE TABLE dbo.Customers(	CustID char(8) NOT NULL PRIMARY KEY CLUSTERED,	CustPassword varchar(15) NOT NULL,	CustName varchar(25) NOT NULL,	CustEmail varchar(60) NOT NULL,	CustAddress varchar(100) NOT NULL)GO

INSERT INTO dbo.Customers VALUES('USA00001', 'TheUnbreakable', 'John', 'John.Smith@SomeDom.com', '10932,Bigge Rd., Menlo Park, CA')

INSERT INTO dbo.Customers VALUES('USA00002', 'ABCxyz012789', 'Heather', 'HGraham@SomeDom.com', '18 Broadway Av. San Francisco, CA')

INSERT INTO dbo.Customers VALUES('EUR00001', 'MagicNSparkle', 'Vyas', 'VyasKN@SomeDom.com', '18 Grove Mead, Herts, AL09 7JK, UK')

INSERT INTO dbo.Customers VALUES('ASA00001', 'aAbBcCXyZ', 'Franklin', 'FMayer@SomeDom.com', '22 Main Street, Singapore')

INSERT INTO dbo.Customers VALUES('OZ000001', 'DOWNundah', 'Steve', 'SWaugh@SomeDom.com', '321, Cricket Way, Melbourne, Australia')

--NOTE: I omitted the VALUES list in the above INSERT statements, for simplicity.--This is not a best practice, and you should always include a VALUES list in your INSERT statements.

Consider the following example, that searches the Customers table for a given Customer ID and password. On a server/database with case insensitive collation, this query will disregard case, and will find a row, as long as the values match, no matter what case is used in the input. Since the @CustID ‘usa00001’ matches a CustID of ‘USA00001’ and @CustPassword ‘theunbreakable’ matches the CustPassword of ‘TheUnbreakable’, the following query prints ‘Customer Found!’:

DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE 	CustID = @CustID		AND CustPassword = @CustPassword)BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO

Now our goal is to write queries that perform case sensitive comparisons. I will introduce you to some popular techniques, and show you how to make those methods efficient. It is important to note that, if and when you migrate the following queries onto a case sensitive database, it is better to get rid of the additional bits from the WHERE clause, and compare normally.

Method 1: Converting data to binary type before comparison
(Works in both SQL Server 7.0 and 2000)
When you convert a character to binary or varbinary datatype, that character’s ASCII value gets represented in binary. Since, ‘A’ and ‘a’ have different ASCII values, when you convert them to binary, the binary representations of these values don’t match, and hence the case sensitive behavior.



DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE 	CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8))		AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15)))BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO

The above example will print ‘Invalid Customer ID or Password!’, because the input provided is in all lower case, while the data in the table is stored in mixed case. Everything looks good. But if you observe the execution plan for this query (By pressing Ctrl + K in Query Analyzer, or by running SET SHOWPLAN_TEXT ON command), you will see an ‘index scan’. An index scan is no good from performance point of view, as it means, scanning the whole index — which is as bad as a table scan.

If you modify the above query as shown below, you will see an ‘index seek’, which is faster. You will realize this performance difference easily, on a huge table.

DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE 	CAST(CustID AS varbinary(8)) = CAST(@CustID AS varbinary(8))		AND CAST(CustPassword AS varbinary(15)) = CAST(@CustPassword AS varbinary(15))		AND CustID = @CustID		AND CustPassword = @CustPassword)BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO

Curious, why the first query scanned the index? Here’s why: When a column used in the WHERE clause is enclosed within a function (in this case CAST), the Query Optimizer (QP) cannot predict the outcome of the function in advance, and hence it has to scan the whole index and see if there’s a match. So, to avoid the problem we added “AND CustID = @CustID AND CustPassword = @CustPassword” to the WHERE clause, and Optimiser made use of the clustered index.

Method 2: Using the COLLATE clause to dictate the case sensitiveness of the query
(Works only in SQL Server 2000)
The COLLATE clause allows us to specify a particular collation for an expression. In the following examples, we will use COLLATE to make our search case sensitive. We need to specify a case sensitive collation along with COLLATE. The following example uses the collation SQL_Latin1_General_CP1_CS_AS. If you are working with non-English data, choose an appropriate collation.



DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE 	CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS		AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS)BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO

The above example will print ‘Invalid Customer ID or Password!’, because the input provided is in all lower case, while the data in the table is stored in mixed case. But again, the execution plan shows an index scan. We can turn this into an index seek, by following the same trick, as in Method 1: So, let’s rewrite the query:

DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE 	CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS		AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS		AND CustID = @CustID		AND CustPassword = @CustPassword)BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO
Method 3: Using BINARY_CHECKSUM function
(Works only in SQL Server 2000)
I’ve seen many people using BINARY_CHECKSUM function to perform case sensitive searches. BINARY_CHECKSUM() function accepts input, and returns a checksum value for that input. Though this is a useful function for tracking changes and verifying integrity of data, I don’t think it is the appropriate for making case sensitive searches. It works for simple comparisons though. Here’s an example:



DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE 	BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID)		AND BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword))BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO

The above example will print ‘Invalid Customer ID or Password!’ and you will see an index scan in the execution plan. The following example will convert that into an index seek.

DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE 	BINARY_CHECKSUM(CustID) = BINARY_CHECKSUM(@CustID)		AND BINARY_CHECKSUM(CustPassword) = BINARY_CHECKSUM(@CustPassword)		AND CustID = @CustID		AND CustPassword = @CustPassword)BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO

As you can see, this technique works for comparing smaller strings. But the following script will prove that BINARY_CHECKSUM can return the same checksum value for different input values. This is very bad, especially when it comes to validating user names and passwords. Someone can bypass the authentication/authorization by specifying a password, which is not correct, but produces the same checksum value as the correct password. Try this script and find out why I don’t recommend this approach:

SET NOCOUNT ON

DECLARE @i varchar(500)

CREATE TABLE #t (CharValue varchar(500), BinaryChecksum int)

SET @i = 'A'

WHILE @i <> REPLICATE('A', 500)BEGIN	INSERT #t SELECT @i, BINARY_CHECKSUM(@i)	SET @i = @i + 'A'END

SELECT CharValue, COUNT(*) AS 'Times Repeated' FROM #t GROUP BY CharValueSELECT BinaryChecksum, COUNT(*) AS 'Times Repeated' FROM #t GROUP BY BinaryChecksumSELECT	BINARY_CHECKSUM('A') AS [Checksum value for 'A'],	BINARY_CHECKSUM('AAAAAAAAAAAAAAAAA') AS [Checksum value for 'AAAAAAAAAAAAAAAAA']

DROP TABLE #t
Method 4: Changing the collation of the column permanently, so that all comparisons are case sensitive by default
(Works only in SQL Server 2000)
SQL Server 2000 lets you specify collation at the column level also. So, you could make your CustID and CustPassword columns case sensitive by default. This saves a lot of effort, as you don’t have to employ any special techniques in your queries, to get case sensitive behavior. Here’s an example:

/* Since we have a primary key constraint defined on the CustID column, we cannot alter it directly.First we need to drop the constraint. So we need the constraint name. The following procedure willgive you the primary key constraint name */

EXEC sp_help 'dbo.Customers'GO

/* Substitute your primary key constraint name into the following ALTER TABLE command, to drop the constraint */

ALTER TABLE dbo.Customers DROP CONSTRAINT PK__Customers__71F07EBEGO

/* Change the collation of the CustID column */

ALTER TABLE dbo.Customers ALTER COLUMN 	CustID char(8) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULLGO

/* Add the primary key constraint back in */

ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers PRIMARY KEY(CustID)GO

/* Change the collation of the CustPassword column as well */

ALTER TABLE dbo.Customers ALTER COLUMN 	CustPassword varchar(15) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULLGO

/* Now try this query and observe the case sensitive behavior */

DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE	CustID = @CustID		AND CustPassword = @CustPassword)BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO
Method 5: Using computed columns
(Works in both SQL Server 7.0 and 2000)
You could add a computed column to your table, that exhibits case sensitivity. A computed column is a virtual column that derives its value from the existing columns. SQL Server 2000 allows you to create an index on a computed column to make searches on these columns faster. We will exploit that feature in this example:

/* Adding two computed columns -- one for CustID and the other for CustPassword */

ALTER TABLE dbo.Customers ADD CustID_CS AS (CAST(CustID AS varbinary(8))),CustPassword_CS AS (CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS)GO

/* Create a nonclustered index on the computed column CustID_CS */

CREATE NONCLUSTERED INDEX NC_NI_Customers_CustID ON dbo.Customers(CustID_CS)GO

/* The following example will make use of the index on the computed column to perform case sensitive searches efficiently */

DECLARE @CustID char(8), @CustPassword varchar(15)

SET @CustID = 'usa00001'SET @CustPassword = 'theunbreakable'

IF EXISTS(	SELECT 1	FROM dbo.Customers	WHERE 	CustID_CS = CAST(@CustID AS varbinary(8))		AND CustPassword_CS = @CustPassword)BEGIN	PRINT 'Customer Found!'ENDELSEBEGIN	PRINT 'Invalid Customer ID or Password!'ENDGO
Method 6: Make use of client side languages like VB or VBScript for case sensitive comparisons
(Works in both SQL Server 7.0 and 2000)
Instead of trying perform case sensitive comparisons on the backend, try taking advantage of your client side applications. This may not be appropriate for all situations, but I’m mentioning it here for the sake of completeness. The following code can be used in a Visual Basic application, to perform case sensitive searches:

Dim cn As New ADODB.Connection, rs As New ADODB.RecordsetDim CustID As String, CustPassword As String

CustID = "usa00001"CustPassword = "theunbreakable"

cn.Open "Server=(local);Integrated Security=SSPI;Database=tempdb;Provider=SQLOLEDB"

Set rs = cn.Execute("SELECT CustID, CustPassword FROM dbo.Customers WHERE CustId = '" + CustID + "' AND CustPassword = '" + CustPassword + "'")

If CustID = rs("CustID") And CustPassword = rs("CustPassword") Then    MsgBox "Customer Found!"Else    MsgBox "Invalid Customer ID or Password!"End If

Set rs = NothingSet cn = Nothing

Hope you find the above techniques useful. Here is some additional information that might help you deal with collations: The following command shows you your SQL Server’s default collation:

SELECT SERVERPROPERTY(‘Collation’) AS ‘Server Level Collation’

To see your default database collation:

SELECT DATABASEPROPERTYEX(‘Pubs’, ‘Collation’) AS ‘Database Level Collation’

To see column level collations of Customers table:

EXEC sp_help ‘dbo.Customers’

To see server level collation settings in SQL Server 2000 as well as the previous versions:

EXEC sp_helpsort

To a listing of all available collations in SQL Server 2000:

SELECT * FROM ::fn_helpcollations()

For further information about specific collations:

SELECT COLLATIONPROPERTY(‘German_PhoneBook_CI_AS’, ‘CodePage’)

SELECT COLLATIONPROPERTY(‘French_CI_AS’, ‘LCID’)

SELECT COLLATIONPROPERTY(‘Latin1_General_CI_AS’, ‘ComparisonStyle’)

Posted in Sql Server | Tagged: , , , | Leave a Comment »

Datalist Custom Paging

Posted by Viral Sarvaiya on March 13, 2009


In this article i will explain a method for providing custom paging for datalist or repeater.

As you know the datalist is a very powerful control with one drawback that it does not have built-in paging capability, a feature the DataGrid offers. to provide paging to datalist or repeater we can either use “PagedDataSource” class, found in the System.Web.UI.WebControls namespace for auto paging like the datagrid or implement custom paging functionality.

But I describe new things for datalist paging, i use plasecontrol for paging as below.

Step 1 :  Make a Default.aspx page

<head>

</head>

<body>

<asp:PlaceHolder ID=”phtopsort” runat=”server”></asp:PlaceHolder>

<asp:DataList ID=”dlFabricsort” runat=”server” DataKeyField=”Fabricid” ShowFooter=”false” EnableViewState=”false”
ShowHeader=”false” RepeatColumns=”3″ RepeatDirection=”Horizontal” HorizontalAlign=”left”
CellPadding=”0″ CellSpacing=”0″ Width=”93%” BackColor=”#F4F1E6″>

<ItemStyle VerticalAlign=”Top” Height=”100%” HorizontalAlign=”Left”/>
<ItemTemplate>
<table width=”100%”>

<asp:Label ID=”Label1″ runat=”server” Text ='<%# container.dataitem(“fabricname”)%>’></asp:Label>

</table>
</ItemTemplate>
</asp:DataList>

</body>

Step 2 : In Default.aspx.vb

Dim total As Integer

Dim totalpages As Integer


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If IsPostBack = False Then

If Request.QueryString(“p”) = Nothing Then
page1 = 1
ElseIf Request.QueryString(“p”) <= 0 Then
page1 = 1
ElseIf Request.QueryString(“p”) = “” Then
page1 = 1
ElseIf Request.QueryString(“p”) <> “” Then
page1 = Request.QueryString(“p”)
End If

datalist_sortfabric()

End If
End Sub

sub datalist_sortfabric()

Dim dssort As New DataSet
Dim dssort1 As New DataSet

dssort = objfabric.SelectSortFabric()  ‘ Select full Dataset

If dssort.Tables(0).Rows.Count > 0 Then

total = dssort.Tables(0).Rows.Count
size = 15   ‘total recordper page

Dim tp As Double = total / size
Dim arr As Array = tp.ToString.Split(“.”)
totalpages = arr(0)
If total Mod size <> 0 Then
totalpages = totalpages + 1
End If

dssort1 = objfabric.SelectSortFabricPaging((page1 – 1) * size, size)

record.Value = dssort.Tables(0).Rows.Count

dlFabricsort.DataSource = dssort1
dlFabricsort.DataBind()

dynamic_paging()

end if

end sub

sub dynamic_paging()

Dim str As String = “”
phtopsort.Controls.Clear()
Dim i As Integer

‘**********First

If page1 = 1 Then
str += “”
Else
str += “<a href=’Default.aspx?p=1′ class=’link12′>First</a>&nbsp;
End If

‘**********pre

If page1 = 1 Then
str += “”
Else
str += “<a href=’Default.aspx?p=” & page1 – 1 & “‘ class=’link12’>Previous</a> &nbsp; ”
End If

‘**********middle
For i = 0 To totalpages – 1

Dim tmp As String = “”
If i = 0 Then
If i + 1 = page1 Then
tmp = “<a href=’Default.aspx?p=” & i + 1 & “‘ class=’link12′>” & i + 1 & “</a>”
Else
tmp = “<a href=’Default.aspx?p=” & i + 1 & “‘ class=’link12′>” & i + 1 & “</a>”
End If
Else
If i + 1 = page1 Then
tmp = ” | <strong> <a href=’Default.aspx?p=” & i + 1 & “‘ class=’link12′>” & i + 1 & “</a></strong>”
Else
tmp = ” | <a href=’Default.aspx?p=” & i + 1 & “‘ class=’link12’>” & i + 1 & “</a>”
End If

End If

If i >= page1 – 6 And i <= page1 + 4 Then
str += tmp
Else
str += “”
End If
Next
‘**********Next

If page1 = totalpages Then
str += “”
Else
str += “&nbsp;&nbsp;&nbsp;<a href=’Default.aspx?p=” & page1 + 1 & “‘ class=’link12’>Next</a>”
End If

‘**********Last

If page1 = totalpages Then
str += “”
Else
str += “&nbsp;&nbsp;&nbsp;<a href=’Default.aspx?p=” & totalpages & “‘ class=’link12’>Last</a>”
End If

‘**********
Dim tab As Table = New Table
tab.Controls.Clear()
tab.CellSpacing = 0
tab.CellPadding = 0
tab.BorderWidth = Unit.Point(0)

Dim tabrow As TableRow = New TableRow
tab.Controls.Add(tabrow)

Dim tabcell As TableCell = New TableCell
tabcell.VerticalAlign = VerticalAlign.Middle
tabcell.HorizontalAlign = HorizontalAlign.Center

tabcell.Text = str
tabrow.Controls.Add(tabcell)
phtopsort.Controls.Add(tab)

end sub

here SelectSortFabricPaging() function contains  page index and page size means when your dataAdapter fills value

objAdp.Fill(ds, pindex, psize, “fabrics”)

here  ds is dataset, pindex is current page index, psize is size of page, and “fabreics” is name of table from you get the records.

And last more one things to that when we build a string for placeholder, we write a page name (default.aspx) if your page contains some querystring then write it there.

Posted in ASP.NET | Tagged: , , , | Leave a Comment »

ASP.NET Validation Controls

Posted by Viral Sarvaiya on March 13, 2009


With ASP.NET, there are six(6) controls included.

They are:
* The RequiredFieldValidation Control
* The CompareValidator Control
* The RangeValidator Control
* The RegularExpressionValidator Control
* The CustomValidator Control

All of the validation controls inherit from the base class BaseValidator so they
all have a series of properties and methods that are common to all validation controls.
They are:

* ControlToValidate – This value is which control the validator is applied to.
* ErrorMessage – This is the error message that will be displayed in the validation summary.
* IsValid – Boolean value for whether or not the control is valid.
* Validate – Method to validate the input control and update the IsValid property.
* Display – This controls how the error message is shown. Here are the possible options:
o None (The validation message is never displayed.)
o Static (Space for the validation message is allocated in the page layout.)
o Dynamic (Space for the validation message is dynamically added to the page if validation fails.)

The RequiredFieldValidation Control
———————————–

The first control we have is the RequiredFieldValidation Control. As it’s obvious,
it make sure that a user inputs a value. Here is how it’s used:

Required field: <asp:textbox id=”textbox1″ runat=”server”/>
<asp:RequiredFieldValidator id=”valRequired” runat=”server” ControlToValidate=”textbox1″
ErrorMessage=”* You must enter a value into textbox1″ Display=”dynamic”>*
</asp:RequiredFieldValidator>

The CompareValidator Control
—————————–
Next we look at the CompareValidator Control. Usage of this CompareValidator is for
confirming new passwords, checking if a departure date is before the arrival date, etc.
We’ll start of with a sample:

Textbox 1: <asp:textbox id=”textbox1″ runat=”server”/><br />
Textbox 2: <asp:textbox id=”textbox2″ runat=”server”/><br />
<asp:CompareValidator id=”valCompare” runat=”server”
ControlToValidate=”textbox1″ ControlToCompare=”textbox2″
Operator=”Equals”
ErrorMessage=”* You must enter the same values into textbox 1 and textbox 2″
Display=”dynamic”>*
</asp:CompareValidator>

Here we have a sample where the two textboxes must be equal. The tags that are unique to this control is the ControlToCompare attribute which is the control that will be compared. The two controls are compared with the type of comparison specified in the Operator attribute. The Operator attribute can contain Equal, GreterThan, LessThanOrEqual, etc.
Another usage of the ComapareValidator is to have a control compare to a value. For example:

Field: <asp:textbox id=”textbox1″ runat=”server”/>
<asp:CompareValidator id=”valRequired” runat=”server” ControlToValidate=”textbox1″
ValueToCompare=”50″
Type=”Integer”
Operator=”GreaterThan”
ErrorMessage=”* You must enter the a number greater than 50″ Display=”dynamic”>*
</asp:CompareValidator>

The RangeValidator Control
————————–

Range validator control is another validator control which checks to see if a control value is within a valid range. The attributes that are necessary to this control are: MaximumValue, MinimumValue, and Type.
Sample:

Enter a date from 1998:
<asp:textbox id=”textbox1″ runat=”server”/>
<asp:RangeValidator id=”valRange” runat=”server”
ControlToValidate=”textbox1″
MaximumValue=”12/31/1998″
MinimumValue=”1/1/1998″
Type=”Date”
ErrorMessage=”* The date must be between 1/1/1998 and 12/13/1998″ Display=”static”>*</asp:RangeValidator>

The RegularExpressionValidator Control
————————————–

The regular expression validator is one of the more powerful features of ASP.NET.
Everyone loves regular expressions. Especially when you write those really big nasty ones…
and then a few days later, look at it and say to yourself. What does this do?
Again, the simple usage is:

E-mail: <asp:textbox id=”textbox1″ runat=”server”/>
<asp:RegularExpressionValidator id=”valRegEx” runat=”server”
ControlToValidate=”textbox1″
ValidationExpression=”.*@.*\..*”
ErrorMessage=”* Your entry is not a valid e-mail address.”
display=”dynamic”>*
</asp:RegularExpressionValidator>

The CustomValidator Control
—————————–

The final control we have included in ASP.NET is one that adds great flexibility to our
validation abilities. We have a custom validator where we get to write out own functions
and pass the control value to this function.

Field: <asp:textbox id=”textbox1″ runat=”server”>
<asp:CustomValidator id=”valCustom” runat=”server”
ControlToValidate=”textbox1″
ClientValidationFunction=”ClientValidate”
OnServerValidate=”ServerValidate”
ErrorMessage=”*This box is not valid” dispaly=”dynamic”>*
</asp:CustomValidator>

We notice that there are two new attributes ClientValidationFunction and OnServerValidate.
These are the tell the validation control which functions to pass the controltovalidate value to.
ClientValidationFunction is usually a javascript funtion included in the html to the user.
OnServerValidate is the function that is server-side to check for validation if client does
not support client-side validation.

Client Validation function:

<script language=”Javascript”>
<!–
/* … Code goes here … */
–>
</script>

>

Server Validation function:

Sub ServerValidate (objSource As Object, objArgs As ServerValidateEventsArgs)
‘ Code goes here
End Sub

Validation Summary
——————-

ASP.NET has provided an additional control that complements the validator controls.
This is the validation summary control which is used like:

<asp:ValidationSummary id=”valSummary” runat=”server”
HeaderText=”Errors:”
ShowSummary=”true” DisplayMode=”List” />

Posted in ASP.NET | Tagged: , , , , , , | Leave a Comment »

file upload validation with javascript

Posted by Viral Sarvaiya on March 13, 2009


step 1 : Make a default.aspx page

put this script in to head section of html page.

<script>

function valid_file()

{

if(!ChkFile(document.getElementById(“fileupload1”)))
{
alert(‘Please Upload Image File’);
document.getElementById(“fileupload1”).focus();
return false;
}

}

function ChkFile(txtfield)
{
var filename,filenamelen
var fileext,fileextlen
var allowedtypes = [“.jpg”,”.JPG”,”.jpeg”,”.JPEG”,”.BMP”,”.bmp”]
var dotpos
filename = txtfield.value
filenamelen = filename.length
dotpos = filename.lastIndexOf(“.”)
fileextlen = (filenamelen – dotpos – 1)

if (fileextlen >= 3 && fileextlen <= 4) //this allows files with a min of 3 and max of 4 chars in their extension
{
fileext = filename.substring(dotpos,filenamelen)
//alert (fileext)
for(var ctr=0;ctr<allowedtypes.length;ctr++)
{

if(allowedtypes[ctr] == fileext)
{
return true;
}
}
//alert(“Only JPG/JPEG/GIF type picture files are allowed.\n Please select a valid file type.”)

}
else
//alert(“The file you have selected is not a valid picture file.\nPlease select a different file.”)

return false
}

</script>

step 2 :On the  default.aspx.vb page

add this code in to page load function

dont put this file in ispostback block

btnsubmit..Attributes.Add(“onclick”, “javascript:return valid_file();”)

Posted in ASP.NET | Tagged: , , , , , | Leave a Comment »

Text Box Characters Counter

Posted by Viral Sarvaiya on March 13, 2009


Here’s another way around for you

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

function validatelimit(obj, maxchar)
{

if(this.id) obj = this;

var remaningChar = maxchar – obj.value.length;
document.getElementById(’<%= Label1.ClientID %>’).innerHTML = remaningChar;

if( remaningChar <= 0)
{
obj.value = obj.value.substring(maxchar,0);
return false;

}
else
{return true;}

}

</script>

<asp:TextBox ID=”TextBox1″ runat=”server” TextMode=”MultiLine” onkeyup=”return validatelimit(this,500)” Height=”400px”></asp:TextBox>
<asp:Label ID=”Label1″ runat=”server” Text=”500″></asp:Label>

Posted in ASP.NET, Javascript | Tagged: , , , | Leave a Comment »

querystring in to javascript and HTML page Redirect.

Posted by Viral Sarvaiya on March 13, 2009


In the HTML page, we can not use the value of one page to second page without useing server side coding.

to do that we have to use javascript.

Step 1 :  Make a file1.html

<HTML>
<HEAD>
</HEAD>

<BODY>

<Form name=”frmDefault” action=”file2.htm” method=”get”>

<p>Item1: <Input Type=”text” name=”txtItem1″></p>
<p>Item2: <Input Type=”text” name=”txtItem2″></p>
<p><Input type=”submit”></p>
</Form>

</BODY>
</HTML>

step 2 : Make file2.html

<HTML>
<BODY>
<Script Language=”Javascript”>
<!–//
var arrArgs = location.search.substring(1).split(“&”);

for (var i=0; i<arrArgs.length; i++) {
document.write (‘<p>’ + arrArgs[i] + “</p>”);
}
//–>
</Script>
</BODY>
</HTML>

javascript write a value of querystring that comes to another  page.

Posted in Javascript | Tagged: , , , , , | Leave a Comment »

Image Upload and Resize

Posted by Viral Sarvaiya on March 13, 2009


Lets assume we have a FileUpload control (named File1) and a Button control ( imgExtSubmit) and we’re going to upload the image when we click our button. The simplest method of uploading our file might look something like this :

for this you have to add the Namespace :

Imports System.Drawing
Imports System.Drawing.Imaging

Protected Sub imgExtSubmit_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgExtSubmit.Click

If File1.HasFile Then

Dim file As String = System.DateTime.Now.Millisecond

If File1.FileName <> “” Then
imgName = GeneralMethods.GetCallbackPath(File1.FileName, file)
File1.SaveAs(Server.MapPath(“../images/”) & imgName)
GeneralMethods.Thmbimages(Server.MapPath(“../images/”) & imgName, Server.MapPath(“../thumb/”), imgName, 116, 76)
Else
imgName = “noimages.gif”
End If

End If

after this coding make a public class named : GeneralMethods.vb

Public Class GeneralMethods

Public Shared Function GetCallbackPath(ByVal returnValue As String, ByVal Fileno As String) As String
Dim fileName As String = System.IO.Path.GetFileName(returnValue)
Dim strfile() As String = fileName.Split(“.”)
fileName = strfile(0) & Fileno & “.” & strfile(1)
If fileName.Length > 10 Then
Dim i As Integer = CInt(fileName.Length) – 10
fileName = fileName.Substring(i, 10)
i = fileName.Length
End If
Return fileName
End Function

Public Shared Function Thmbimages(ByVal MainPath As String, ByVal ThmbPath As String, ByVal Filename As String, ByVal Passwidth As String, ByVal Passheight As String, Optional ByVal FixFlag As Integer = 0) As String
Dim width, height As Integer
Dim pathname As String
Dim inp As New IntPtr
Dim orginalimg, thumb As System.Drawing.Image

orginalimg = orginalimg.FromFile(MainPath)
width = orginalimg.Width
height = orginalimg.Height

If FixFlag <> 0 Then
width = Passwidth
height = Passheight
Else
If width > Passwidth Or height > Passheight Then
Dim per As Double
If width > height Then
per = (100 * Passwidth) / width
width = (width * per) / 100
height = (height * per) / 100
Else
per = (100 * Passheight) / height
width = (width * per) / 100
height = (height * per) / 100
End If
End If
End If
thumb = orginalimg.GetThumbnailImage(width, height, Nothing, inp)

pathname = ThmbPath & Filename

If File.Exists(pathname) Then
File.Delete(pathname)
End If

thumb.Save(pathname)
thumb.Dispose()
orginalimg.Dispose()
Return Filename
End Function

End Class

this function directly save the actual file to the image folder and make a small image and put it to the THUMB folder.

Posted in ASP.NET | Tagged: , , , , | Leave a Comment »

javascript Ajax Function

Posted by Viral Sarvaiya on March 13, 2009


hear i demonstrate the javascript’s Ajaxfunction.

with this function we can get data from one .aspx page to another .aspx page.

Description :

Hear i have a student information in one table of database. In 1 page(default.aspx) i have a chacke box and some textbox.

when i check the check box it goes to server side without refreshing that page take all the data of student and put all the data in to that text boxes.

step 1: Make a default.aspx page as below.

<head id=”Head1″ runat=”server”>

<script type=”text/JavaScript”>
function ajaxFunction(eid, chkenroll)
{
var xmlHttp;
try
{
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
// Internet Explorer
try
{
xmlHttp=new ActiveXObject(“Msxml2.XMLHTTP”);
}
catch (e)
{
try
{
xmlHttp=new ActiveXObject(“Microsoft.XMLHTTP”);
}
catch (e)
{
alert(“Your browser does not support AJAX!”);
return false;
}
}
}
xmlHttp.onreadystatechange=function()
{

if(xmlHttp.readyState==4)
{
var valstr,ss
valstr = xmlHttp.responseText;
ss = valstr.split(“_”);
document.getElementById(“txtSfname”).value = ss[0];
document.getElementById(“txtSlname”).value = ss[1];
document.getElementById(“txtSemail”).value = ss[2];
document.getElementById(“txtSadd1”).value = ss[3];
document.getElementById(“txtScity”).value = ss[4];
document.getElementById(“txtSstate”).value = ss[5];
document.getElementById(“txtSzip”).value = ss[6];
}
}
if (document.getElementById(chkenroll).checked == true)
{
xmlHttp.open(“GET”,”getenrollment.aspx?eid=” + eid ,true);
}
else
{
document.getElementById(“txtSfname”).value = “”;
document.getElementById(“txtSlname”).value = “”;
document.getElementById(“txtSemail”).value = “”;
document.getElementById(“txtSadd1”).value = “”;
document.getElementById(“txtScity”).value = “”;
document.getElementById(“txtSstate”).value = “”;
document.getElementById(“txtSzip”).value = “”;

document.getElementById(“chkadd”).checked=false;
document.getElementById(“txtbfname”).value = “”;
document.getElementById(“txtblname”).value = “”;
document.getElementById(“txtbemail”).value = “”;
document.getElementById(“txtbaddr1”).value = “”;
document.getElementById(“txtbaddr2”).value = “”;
document.getElementById(“txtbcity”).value = “”;
document.getElementById(“txtbstate”).value = “”;
document.getElementById(“txtbzip”).value = “”;
}
xmlHttp.send(null);
}
</script>

</head>

<body>

<table cellpadding=”0″ cellspacing=”0″>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF; padding-top: 0px; padding-right:150px;”
class=”contant” height=”35″ colspan=”2″>
<asp:CheckBox ID=”chkemrollment” runat=”server” Text=”  Check this box for same as Enrollment” />
</td>
</tr>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF;” class=”contant”
height=”35″  width=”130″>
First Name :
</td>
<td width=”620″ style=”border-bottom: solid 1px #ffffff;border-right: solid 1px #FFFFFF; padding-left: 10px; vertical-align: middle;”>
<asp:TextBox ID=”txtSfname” runat=”server” CssClass=”input2″ autocomplete=”off”></asp:TextBox><span style=”color:Red;”> *</span>
</td>
</tr>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF;” class=”contant”
height=”35″>
Last Name :
</td>
<td style=”border-bottom: solid 1px #ffffff;border-right: solid 1px #FFFFFF; padding-left: 10px; vertical-align: middle;”>
<asp:TextBox ID=”txtSlname” runat=”server” CssClass=”input2″ autocomplete=”off”></asp:TextBox><span style=”color:Red;”> *</span>
</td>
</tr>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF;” class=”contant”
height=”30″>
Email Address :
</td>
<td style=”border-bottom: solid 1px #ffffff;border-right: solid 1px #FFFFFF; padding-left: 10px; vertical-align: middle;”>
<asp:TextBox ID=”txtSemail” runat=”server” CssClass=”input1″ autocomplete=”off”></asp:TextBox><span style=”color:Red;”> *</span></td>
</tr>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF;” class=”contant”
height=”35″>
Address 1:
</td>
<td style=”border-bottom: solid 1px #ffffff;border-right: solid 1px #FFFFFF; padding-left: 10px; vertical-align: middle;”>
<asp:TextBox ID=”txtSadd1″ runat=”server” CssClass=”input2″ autocomplete=”off”></asp:TextBox><span style=”color:Red;”> *</span>
</td>
</tr>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF;” class=”contant”
height=”35″>
Address 2 :
</td>
<td style=”border-bottom: solid 1px #ffffff;border-right: solid 1px #FFFFFF; padding-left: 10px; vertical-align: middle;”>
<asp:TextBox ID=”txtSadd2″ runat=”server” CssClass=”input2″ autocomplete=”off”></asp:TextBox>
</td>
</tr>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF;” class=”contant”
height=”35″>
City :
</td>
<td style=”border-bottom: solid 1px #ffffff;border-right: solid 1px #FFFFFF; padding-left: 10px; vertical-align: middle;”>
<asp:TextBox ID=”txtScity” runat=”server” CssClass=”input2″ autocomplete=”off”></asp:TextBox><span style=”color:Red;”> *</span>
</td>
</tr>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF;” class=”contant”
height=”35″>
State/Province :
</td>
<td style=”border-bottom: solid 1px #ffffff;border-right: solid 1px #FFFFFF; padding-left: 10px; vertical-align: middle;”>
<asp:TextBox ID=”txtSstate” runat=”server” CssClass=”input2″ autocomplete=”off”></asp:TextBox><span style=”color:Red;”> *</span>
</td>
</tr>
<tr>
<td style=”border-bottom: solid 1px #ffffff; border-right: solid 1px #FFFFFF;border-left: solid 1px #FFFFFF;” class=”contant”
height=”35″>
Post/Zip code :
</td>
<td style=”border-bottom: solid 1px #ffffff;border-right: solid 1px #FFFFFF; padding-left: 10px; vertical-align: middle;”>
<asp:TextBox ID=”txtSzip” runat=”server” CssClass=”input2″ autocomplete=”off”></asp:TextBox><span style=”color:Red;”> *</span>
</td>
</tr>
</table>

</body>
</html>

step 2: Make a default.aspx.vb page as below.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

chkemrollment.Attributes.Add(“onclick”, “javascript:return ajaxFunction(” & Request(“eid”) & “,'” & chkemrollment.ClientID & “‘)”)

End Sub

———————————————————————–

On clicking the checkbox, javascript redirect to the getenrollment.aspx page. from this page we can redirect with query string with GET method.

getenrollment.aspx page only include the first line of aspx page, remove all the other coding of that file. show bellow.

step 3: Make a getenrollment.aspx page as below.

<%@ Page Language=”VB” AutoEventWireup=”false” CodeFile=”getenrollment.aspx.vb” Inherits=”getenrollment” %>

step 4: Make a getenrollment.aspx.vb page as below.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ds As New DataSet
If IsPostBack = False Then
If Request(“eid”) <> “” Then
If IsNumeric(Request(“eid”)) Then
ds = objenroll.SelectEnrollmentById(Request(“eid”))
If ds.Tables(0).Rows.Count > 0 Then
Response.Write(ds.Tables(0).Rows(0).Item(“fname”) & “_” & ds.Tables(0).Rows(0).Item(“lname”) & “_”)
Response.Write(ds.Tables(0).Rows(0).Item(“email”) & “_” & ds.Tables(0).Rows(0).Item(“address”) & “_”)
Response.Write(ds.Tables(0).Rows(0).Item(“city”) & “_” & ds.Tables(0).Rows(0).Item(“state”) & “_”)
Response.Write(ds.Tables(0).Rows(0).Item(“zipcode”))
End If
End If
End If
End If
ds.Dispose()
ds.Clear()
End Sub

——————————–

this page return the string to the default.aspx page,

show javascript of the  default.aspx page

valstr = xmlHttp.responseText;

means the valstr variable have that string.

and then that string seperated by “_” (underscore)  and then it put to the textbox.

Posted in Javascript | Tagged: , , , | Leave a Comment »

 
%d bloggers like this: