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
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.
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:
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
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
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 [...]
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
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
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;"
