Tuesday, April 28, 2009

Get Parameter List Of Store Procedure in SQL SERVER

    SELECT * FROM INFORMATION_SCHEMA.PARAMETERS  
    
    --–pass storeprocedure name or function name 
    --–get the relavent columns ParameterName,DataType,Length,ParameterType      
    SELECT Parameter_name as ParameterName,Data_type as DataType,coalesce(Character_Maximum_Length,0) as Length,
    Parameter_Mode as ParameterType 
    FROM INFORMATION_SCHEMA.PARAMETERS 
    WHERE SPECIFIC_NAME='GetProductInfoForAttributes'  
    --GetProductInfoForAttributes is my store procedure name 
    

thnx

Saturday, April 25, 2009

Count wordin textbox

TO find the enter text in text box use the following function in javascript Use onkeydown and onkeyup events to call this function

function fnCheckCharacters()    
{         
    var txtcheck=document.getElementById('<%= txtQ11.ClientID %>'); 
    //here txtQ11 is my textbox id       
    var lblLeftCount = document.getElementById('<%=lblCharCounter.ClientID %>'); 
    //lblCharCounter is a labed on which i printed the remaing word                
    var len=300;       
    var Lchar = 0;           
    if (txtcheck.value.length > 300)       
    {           
        alert ("You can enter atmost 300 characters");           
        txtcheck.value=txtcheck.value.substring(0,300);       
    }
    else       
    {           
        lblLeftCount.innerHTML=txtcheck.value.length;       
    }          
}   

in this function, i fixed the length of textbox to 300. user can enter only 300 char.after inserting each char, reaming one is displayed on label.


thnx

Wednesday, April 22, 2009

Text Box Enter Key in ASP.NET

One of the common requests in ASP.NET is to submit a form when visitor hits an Enter key. That could be a case if, for example you want to make Login Screen. It is expected that user just hit enter when he insert a user name and password instead to of forcing him to use a mouse to click login button. If you want to make search function on your web site, it is frequently required to give a possibility to hit enter after you insert a search terms instead of mouse click on a Search button.

In HTML or classic ASP pages is not hard to submit forms using the enter key on keyboard. Programmer use a to make a default button. If web site visitor click on that button or press enter key, the form will be submited.

Of course, you can have a more than one form on your page and individual submit button for every form.
You don't want to submit a form with Enter key?

Rarely, you will need to disable an Enter key and avoid to submit form. If you want to prevent it completely, you need to use OnKeyDown handler on tag of your page. The javascript code should be:

if (window.event.keyCode == 13)
{
event.returnValue=false;
event.cancel = true;
}

Common ASP.NET problems with Enter key

If you try to use Enter key in ASP.NET, according to your browser's type, you can get really weird results. For example, try to place one ASP.NET textbox and a button to the web form. Write a code on a OnClick event of a button. That could be something simple, like:

Response.Write("The button was clicked!");

Now start debugging and write something to textbox. If you press enter while focus is on textbox, form will submit, but your code for button's click event will not be executed.

Stop the debuging and place one more simple HTML textbox to the form. You will not write anything in this textbox, so you can even make it invisible. Just place it somewhere inside of your form tag.

Start debugging again. You cannot see the second textbox, and everything looks like before. Try again to write something in first textbox. If you press enter now, form will submit, and your code for button's click event will now be executed. This is extremely different behavior, and you did nothing except you placed one invisible textbox on web form. :)

Maybe it is not best practice, but placing invisible textbox could be simple solution for you if you have only one button on your web form. But, what if you have a different situation? What if you have a few buttons with only one textbox, or more than one text box with only one button, or many text boxes and many buttons with different code for each button, and all that on one form?

Different browsers have a different behavior in these cases. In case that you have more buttons, only first button will be "clicked" every time. So, we need some other approach to get an universal solution.

Enter Key in ASP.NET


One of the common requests in ASP.NET is to submit a form when visitor hits an Enter key. That could be a case if, for example you want to make Login Screen. It is expected that user just hit enter when he insert a user name and password instead to of forcing him to use a mouse to click login button. If you want to make search function on your web site, it is frequently required to give a possibility to hit enter after you insert a search terms instead of mouse click on a Search button.


In HTML or classic ASP pages is not hard to submit forms using the enter key on keyboard. Programmer use a to make a default button. If web site visitor click on that button or press enter key, the form will be submited.

Of course, you can have a more than one form on your page and individual submit button for every form.
You don't want to submit a form with Enter key?

Rarely, you will need to disable an Enter key and avoid to submit form. If you want to prevent it completely, you need to use OnKeyDown handler on tag of your page. The javascript code should be:

if (window.event.keyCode == 13)
{
event.returnValue=false;
event.cancel = true;
}
Common ASP.NET problems with Enter key

If you try to use Enter key in ASP.NET, according to your browser's type, you can get really weird results. For example, try to place one ASP.NET textbox and a button to the web form. Write a code on a OnClick event of a button. That could be something simple, like:

Response.Write("The button was clicked!");

Now start debugging and write something to textbox. If you press enter while focus is on textbox, form will submit, but your code for button's click event will not be executed.

Stop the debuging and place one more simple HTML textbox to the form. You will not write anything in this textbox, so you can even make it invisible. Just place it somewhere inside of your form tag.


Start debugging again. You cannot see the second textbox, and everything looks like before. Try again to write something in first textbox. If you press enter now, form will submit, and your code for button's click event will now be executed. This is extremely different behavior, and you did nothing except you placed one invisible textbox on web form. :)

Maybe it is not best practice, but placing invisible textbox could be simple solution for you if you have only one button on your web form. But, what if you have a different situation? What if you have a few buttons with only one textbox, or more than one text box with only one button, or many text boxes and many buttons with different code for each button, and all that on one form?

Different browsers have a different behavior in these cases. In case that you have more buttons, only first button will be "clicked" every time. So, we need some other approach to get an universal solution.

How to make a default button in ASP.NET

We need to specify exactly which button will be "clicked" when visitor press Enter key, according to which textbox currently has a focus. The solution could be to add onkeydown attribute to textbox control with this code:

TextBox1.Attributes.Add("onkeydown", "if(event.which || event.keyCode){if ((event.which == 13) || (event.keyCode == 13)) {document.getElementById('"+Button1.UniqueID+"').click();return false;}} else {return true}; ");

This line of code will cause that button Button1 will be "clicked" when visitors press Enter key and cursor is placed in TextBox1 textbox. On this way you can "connect" as many text boxes and buttons as you want.


Default buttons in ASP.NET 2.0 and ASP.NET 3.5

ASP.NET 2.0 makes this problems easier and introduce a concept of a "default button". New defaultbutton attribute can be used with
or control. What button will be "clicked" depends of where actually cursor is and what button is chosen as a default button for form or a panel.

----

Or We can use the Panel/Form DefaultButton Property to make the button to default press when we press the enter key..

For Panel Control in asp.net


<asp:Panel ID="Panel1" runat="server" DefaultButton="">
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:button ID="btnDefault" runat="server" Text="Default Button Panel" OnClientClick="btnDefault_Click" />
</asp:Panel>



For Form tag in asp.net

<form id="form1" runat="server" defaultbutton="btnDefault">   
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:button ID="btnDefault" runat="server" Text="Default Button Form" OnClientClick="btnDefault_Click" />
</form>






After making the default button, press enter key on textbox you will notice that without pressing Button click events, btnDefault's btnDefault_Click is fired.



---
Enjoy Programming



Get Primary key - Foreign key relations table in sql server 2005

Get Primary key - Foreign key relations table in sql server 2005 Why it is required? The main purpose of this is finding related constraint table from database. Check below Query....


--get table list with constraint(primary and foreign key)  

select * from information_schema.constraint_column_usage  

--get table list with foreign key constraint  
select * from information_schema.referential_constraints  

--get the relation  
select tblAll.table_name as PrimaryTableName, tblAll.column_name as PrimaryTableColumn, 
tblFK.table_name as ForeignKeyTable, tblFK.column_name as ForeignKeyColumn 
from information_schema.constraint_column_usage tblAll 
inner join information_schema.referential_constraints tblAllFK on tblAllFK.unique_constraint_name = tblAll.constraint_name 
inner join information_schema.constraint_column_usage tblFK on tblAllFK.constraint_name=tblFK.constraint_name   

thnx

Thursday, April 16, 2009

Browser detection javascript

heres a javascript for the detection of the web browser used by the end user:


if(navigator.userAgent.indexOf(’Safari’)!=-1)
{ 
    alert(’safari’); 
} 
else if(navigator.userAgent.indexOf(’Gecko’)!=-1) 
{ 
    alert(’mozilla’); 
}  
else 
{ 
    if(navigator.userAgent.indexOf(’MSIE 7.0′)!=-1) 
    { 
        alert(’ie 7′); 
    } 
    else 
    { 
        alert(’ie 6′); 
    } 
}         
       

now i’ll explain you the above script: navigator.userAgent returns the full description of the user agent or web browser used by the client or end user. indexOf() function returns the character position [...]

Regular Expression in Sqlserver 2005


Regular Expression in Sqlserver 2005.

why Regular Expression Required for sqlserver 2005? The basic need Of Regular Expression
is avoiding hard work for searching or pattern maching in database. Example if i
want to search valid email address from users table then what will be senario for
that? i have thousand of newly registered user daily. then how to i find invalid
Email Address from that? let’s start with regular expression in sqlserver 2005.
the basic requirement for that configure OLE Object in to Server. –Configure OLE
Automation object. –EXECUTE permission set for object ’sp_OACreate’

use[master] GO GRANT EXECUTE ON[sys].[sp_OASetProperty] TO [public] 
GO 
use[master] 
GO 
GRANT EXECUTE ON[sys].[sp_OAMethod] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OAGetErrorInfo] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OADestroy] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OAStop] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OACreate] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OAGetProperty] TO[public] 
GO sp_configure’show advanced options’,1 
GO reconfigure go  execsp_configure go execsp_configure‘Ole Automation Procedures’,
        

1 – Configuration option ‘Ole Automation Procedures’ changed from 0 to 1. Run the
RECONFIGURE statement to install. go reconfigure go steps : 1) Create Scalar Value
Function In Sqlserver 2005.


create FUNCTION dbo.RegEx     
(         
 --Expression Target / Source         
 @Target varchar(5000),         
 --Regular Expression Pattern         
 @Pattern varchar(Max),         
 --whether the expression case sensitive         
 @CaseSensitive bit = 0     
) 
RETURNS bit 
AS     
BEGIN         
DECLARE @ReturnOACreate int         
DECLARE @ObjToken int         
DECLARE @objMatches int         
DECLARE @objMatch int         
DECLARE @count int         
DECLARE @results bit          

EXEC @ReturnOACreate = sp_OACreate 'VBScript.RegExp', @ObjToken OUTPUT          
--objecttoken OUTPUT         
--Is the returned object token, and must be a local variable of data type int.         
--This object token identifies the created OLE object and is used in calls to the other         
--OLE Automation stored procedures.         
--Return         
--0 (success) or a nonzero number (failure) that is the integer value of the         
--HRESULT returned by the OLE Automation object          
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END          
EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'Pattern', @Pattern          
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END          
EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'Global', false         
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END          
EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'IgnoreCase', @CaseSensitive         
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END              
EXEC @ReturnOACreate = sp_OAMethod @ObjToken, 'Test', @results OUTPUT, @Target         
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END          
EXEC @ReturnOACreate = sp_OADestroy @ObjToken         
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END     

--return 1 for success     
RETURN @results     
END          

2) Now use this function for Matching the Expression.


  • case 1: simple regular expression match
            select dbo.RegEx( N'122-45-6789', N'^\d{3}-\d{2}-\d{4}$',0 )      
            run this it return 0 it indicate this expression is valid.  
        
            select dbo.RegEx( N'1224-45-6789', N'^\d{3}-\d{2}-\d{4}$',0 )      
            run this it return 1 it indicate this expression is InValid.  
            

  • case 2: Get the data from Table now the senario is i am finding the valid email address from the table (here i used simple regular expression which is made manual for demo purpose you can use any of them.)
        declare @tblTmp as table(id int identity(1,1),emailaddress varchar(50))  
        insert into @tblTmp values ('test@yahoo') 
        insert into  @tblTmp values ('test@gmail.test') 
        insert into @tblTmp values ('t@gmail.com') 
        insert into @tblTmp values ('test@test.com') 
        insert into  @tblTmp values ('test@test.')  
        
        select * from @tblTmp  
        --find valid email address 
        
        select * from @tblTmp where dbo.RegEx(emailaddress,'^[A-Za-z0-9._%-]+@[a-zA-Z0-9]+\.[a-zA-Z]{2,4}$',0)=1
        

Wednesday, April 15, 2009

Split Function in SQL Server

Sql Server does not (on my knowledge) have in-build Split function. Split function
in general on all platforms would have comma-separated string value to be split
into individual strings. In sql server, the main objective or necessary of the Split
function is to convert a comma-separated string value (‘abc,cde,fgh’) into a temp
table with each string as rows.

The below Split function is Table-valued function which would help us splitting
comma-separated (or any other delimiter value) string to individual string.

Create FUNCTION [dbo].[Split]
( 
 @String varchar(8000), @Delimiter char(1)
)
returns @temptable TABLE (items varchar(8000)) 
as 
begin 

declare @idx int 
declare @slice varchar(8000) 

select @idx = 1 

if len(@String)<1 or @String is null 
 return 

while @idx!= 0 
begin 
 
 set @idx = charindex(@Delimiter,@String)  
 if @idx!=0 
  set @slice =left(@String,@idx - 1) 
 else 
  set @slice = @String 

 if(len(@slice)>0) 
  insert into @temptable(Items)values(@slice) 

 set @String = right(@String,len(@String) - @idx) 
 if len(@String)= 0 
 break 
end 
return 
end 

--now fire this query 
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')

--this will return following results. 
-- Items -----------------------
--Chennai 
--Bangalore 
--Mumbai

Tuesday, April 14, 2009

SQL Native Client (SQLNCLI )

SQl server uses a new database provider model. This provider succeeds OLEDB. OLEDB
was used for SQL Server databases. Now even it's possible to access MS Access databases
that way. ODBC is only for backward compatability. The new SQLNCLI provider has
the following new features

  • - Support for the new SLQ 2005 large datatypes (Max String, with no limited length)
  • - XML Datatype (Not just a handle, but a real type)
  • - Custom Datatypes (UDT)
  • - The new snapshot isolation level.This level does not lock dirty reads. It uses
    optimistic (locking). It's the best of both worlds. Enabled to read all actual committed
    data, skipping locks, but reading the data (dirty reads),and persisting data if
    the data is not locked.
  • - Supprot for multiple active recordsets. It was already possible to get multiple
    recordsets. In Ado.Net, with movenext, the next result could be read. But now, it's
    possible to have multiple connections open. So each recordset (multiple retrieval)
    can have it's own connection.
  • - Password can be changed at the client, without the need of the administrator.
  • - Support for asynchoon operations. Lots of complex database actions take a time.
    Now SQL server can create threads to do jobs on its own.
  • - It supports SQLXML 4.0. The new connection string (just another provider) "Provider=SQLNCLI;Server=(local);Database=Northwind;IntegratedSecurity=SSPI;" For DSN connections (who uses them still?) "Driver={SQL Native Client}; Server=(local); Database=Northwind; Trusted_Connection=yes;"

Saturday, April 11, 2009

Export GridView into Excel

below i am describing how to export a grid data..
    /// pass gridveiw into this funtion  
    private void ExportGridView(GridView gv)
    {
        string attachment = "attachment; filename=ArchiveOrderList.xls"; //name of excel filename         
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        // Create a form to contain the grid         
        HtmlForm frm = new HtmlForm();
        //gvOrderInfo.Parent.Controls.Add(frm);         
        //gvOrderInfo.Parent.Controls.Add(frm);         
        this.Controls.Add(frm);
        frm.Attributes["runat"] = "server";
        frm.Controls.Add(gv);
        frm.RenderControl(htw);
        //GridView1.RenderControl(htw);         
        Response.Write(sw.ToString());
        Response.End();
    }

    private void PrepareGridViewForExport(Control gv)
    {
        LinkButton lb = new LinkButton();
        Literal l = new Literal();
        string name = String.Empty;
        for (int i = 0; i < gv.Controls.Count; i++)
        {
            if (gv.Controls[i].GetType() == typeof(LinkButton))
            {
                l.Text = (gv.Controls[i] as LinkButton).Text;
                gv.Controls.Remove(gv.Controls[i]);
                gv.Controls.AddAt(i, l);
            }
            else if (gv.Controls[i].GetType() == typeof(DropDownList))
            {
                l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
                gv.Controls.Remove(gv.Controls[i]);
                gv.Controls.AddAt(i, l);
            }
            else if (gv.Controls[i].GetType() == typeof(CheckBox))
            {
                l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";
                gv.Controls.Remove(gv.Controls[i]);
                gv.Controls.AddAt(i, l);
            }
            else if (gv.Controls[i].GetType() == typeof(ImageButton))
            {
                continue;
            }
            if (gv.Controls[i].HasControls())
            {
                PrepareGridViewForExport(gv.Controls[i]);
            }
        }
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        if (control.ID == GridView1.Id) // here GridView1 is the id of gridview of page
        {
            //Doing nothing             
            return;
        }
        base.VerifyRenderingInServerForm(control);
    }

Thursday, April 2, 2009

Open New pop window

hay guys nothing to new...use java script to open new pop up window on image button
click or any other window... just add attribute like ...
    imgbtn.Attributes.Add("onclick", "javascript:window.open('" + Text.Replace(" ", "_") + "/LargeImage.aspx?data=" + str + "','LargeImage','width=500,height=500,scrollbars=yes'); return false;");   
    //here imgbtn is my image control name

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...