Posts

Showing posts from August, 2010

select only those records which are having only numeric values in col

select only those records which are having only numeric values in col1 , where col1 is defined as varchar  select * from tab where col1 not like '%[^0123456789.]%'
Post a Comment
Read more

Return the all column values of table in comma separated in SQLServer

Declare @Var Varchar(8000)   Select  @Var = COALESCE(@Var+',' , ' ') + qty FROM mytab   SELECT  @Var result is:- a,b,c,d,e,f
Post a Comment
Read more

call a function of code-behind (.vb or .cs) file in JavaScript

Following code shows how you can use a function of a code-behind file in JavaScript (.js) file. In this example, you will see that when a loged-in user closes his browser Window (instead of loging-out), how you can log him out programatically by accessing the FunctionToLogOut() function of .vb file in a JavaScript file.   contents of “MyScript.js” (JavaScript) file_ function myunload() {  if (window.event.clientX < 0 && window.event.clientY < 0)  {    alert("You are about to log out...");    PageMethods.FunctionToLogOut();  } } *************************************************************************************** In the <body> tag of your web page_ <body onunload="myunload()"> *************************************************************************************** In the code behind_ <WebMethod()>_ Public Shared Function FunctionToLogOut() [ Yor code for Loging-Out user will go here... ] End Function **********...
Post a Comment
Read more

Avoid duplicate insertion of record on page refresh by F5

With the help of TimeStamp , we can easily avoid duplicate insertion of record when user refreshes page by pressing F5. Let's see how easy it is to check whether page is refreshed by pressing F5: 1. First, add a class 'BasePage' to your project and inherit it from System.Web.UI.Page Here is the detailed code for BasePage class: public class BasePage : System.Web.UI.Page //Inherit class from System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Session["RefreshTimeStamp"] = HttpContext.Current.Server.UrlDecode(System.DateTime.Now.ToString()); } } protected void Page_PreRender(object sender, EventArgs e) { ViewState["RefreshTimeStamp"] = Session["RefreshTimeStamp"]; } public bool IsRefreshed { get { if (Convert.ToString(Session["RefreshTimeSt...
Post a Comment
Read more

Finding Nth min/max salary using SQL

To find Nth minimum salary: 1.In SQL Server 2005 SELECT * FROM (SELECT salary,Dense_rank() OVER (ORDER BY Salary) AS Rank FROM employees) t1 WHERE Rank=N 2.In SQL Server 2000/2005 SELECT salary FROM employees e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.salary)) FROM employees e2 WHERE e2.salary To find Nth maximum salary: 1.In SQL Server 2005 SELECT * FROM (SELECT salary,Dense_rank() OVER (ORDER BY Salary desc) AS Rank FROM employees) t1 WHERE Rank=N 2.In SQL Server 2000/2005 SELECT salary FROM employees e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.salary)) FROM employees e2 WHERE e2.salary >= e1.salary))
Post a Comment
Read more

Checking for duplicate record with FormView

When we are using FormView control often we need to check for existence of record in database at the time of insert or update. Suppose we are having table 'CategoryMst' and we want that 'CategoryName' column should be unique. 1 . Add Unique key to the column 'CategoryName' in the 'CategoryMst' table. 2 . Then just catch the exception and keep the FormView in insert/ edit mode in ItemInserted/ ItemUpdated event of FormView control. protected void FormView1_ItemInserted(object sender, FormViewInsertedEventArgs e) { if (e.Exception != null) { if (((SqlException)e.Exception).Number == 2627) { e.ExceptionHandled = true; e.KeepInInsertMode = true; // Display error message. } } }
Post a Comment
Read more

Currency Convertor API

ASP.NET Exchange Rate API http://exchangerate-api.com/asp-net Before you start using this API you need a (free) API key, which you can get here Once you've done that, using the Exchange Rate API in ASP.NET is easy. Here are some examples to convert currencies: To convert currencies from 12.50 USD (US Dollar) to GBP (British Pound) Dim answer As String Dim url As String url = "www.exchangerate-api.com/usd/gbp/12.50?k=API_KEY" Dim inStream As StreamReader Dim webRequest As WebRequest Dim webresponse As WebResponse webRequest = webRequest.Create(url) webresponse = webRequest.GetResponse() inStream = New StreamReader(webresponse.GetResponseStream()) answer = inStream.ReadToEnd() Or to get how many Japanese Yen (JPY) there are to the South African Rand (ZAR): Dim answer As String...
Post a Comment
Read more