Thursday, February 26, 2009

500 Internal Server Error - The received certificate has expired.

PROBLEM
The page cannot be displayed

There is a problem with the page you are trying to reach and it cannot be displayed.Please try the following:
Click the Refresh button, or try again later.
Open the www.mylogo.com.tr home page, and then look for links to the information you want.
If you believe you should be able to view this directory or page, please contact the Web site administrator by using the e-mail address or phone number listed on the www.mylogo.com.tr home page.
500 Internal Server Error - The received certificate has expired. (-2146893016) Internet Security and Acceleration Server
CAUSE
This issue occurs if both of the following conditions are true:
You publish a Secure Sockets Layer (SSL) Web site by bridging the SSL connection to the internal Web server as SSL. In other words, Internet Security and Acceleration (ISA) Server receives an SSL request on the external interface and then requests the Web content from the Web server by using an SSL request. -and-
The certificate on the internal Web server has expired.The error message that is described in the "Symptoms" section describes the error as an internal ISA Server error. However, this error message actually indicates a problem with the published server's certificate.

RESOLUTION
To resolve this issue, obtain a new certificate for the published Web server. This error no longer occurs when the published server has a valid certificate.

Tuesday, February 24, 2009

Enable Ole automation for MS SQL


The following steps for "Enable Ole automation",


start ->


programs ->


MS sql 2005 ->


configuration tools ->


surface area configuration ->


Surface area conf for features ->



Ole automation ->


select the Enable check box and saved


Monday, February 23, 2009

FUNCTION: isNaN (Java Script)

The isNaN function is used to determine if the argument, testvalue, is a NaN.

A NaN, which means "Not-a-Number", is classified as a primitive value by the ECMA-262 standard and indicates that the specified value is not a legal number.

The function returns true if the argument is not a number and false if the argument is a number.

The classic example of a NaN is zero divided by zero, 0/0.

Code:

document.write(isNaN("Ima String")) // true

document.write(isNaN(0/0)) // true

document.write(isNaN("348")) // false

document.write(isNaN(348)) // false

Thursday, February 19, 2009

Round to a certain number of places in Java Script

For rounding decimals you can use the built-in JavaScript methods toFixed or toPrecision.

Round to a certain number of places
For rounding decimals you can use the built-in JavaScript methods toFixed or toPrecision.


var num = 10;
var result = num.toFixed(2); // result will equal 10.00

num = 930.9805;
result = num.toFixed(3); // result will equal 930.981

num = 500.2349;
result = num.toPrecision(4); // result will equal 500.2

num = 5000.2349;
result = num.toPrecision(4); // result will equal 5000

num = 555.55;
result = num.toPrecision(2); // result will equal 5.6e+2

Adding TextBox Values using Javascript in C#

The following code sample shows how to add the textbox values using javascript.

The keyup event of the textbox is used. Three textboxes is use , the functionality is to add the first and second textboxes and total in third textbox.

Step 1-The Javascript Function follows :

function KeyUpEvent()
{
var txt1 = document.getElementById("txt1");
var txt2 = document.getElementById("txt2");
var txt3 = document.getElementById("txt3");
if ((txt1.value != "") && (txt2.value != ""))
{
txt3.value = parseInt(txt1.value) + parseInt(txt2.value);
}
}

Step 2-Design





Step 3-To add attributes to the textbox at code behind use this example:

txt1.Attributes.Add("onKeyUp", "javascript:KeyUpEvent();");
txt2.Attributes.Add("onKeyUp", "javascript:KeyUpEvent();");


How to convert a float, interger value to string?

Integer (int) or float value can be converted to string by using the function or method toString().


var a = 3.22;
a.toString(); // "3.22"

var a = 5;
a.toString(); // 5

Converting a string data type to float in javascript?

To convert a value from string to float, we have to use the function or method "parseFloat() to convert a string value to float.

Example:

parseFloat("4.333"); // 4.333
parseFloat("5aaa"); // 5
parseFloat("6e2"); // 600
parseFloat("aaa"); // NaN (means "Not a Number")

Tuesday, February 17, 2009

Arrays in C#

Arrays
Arrays are a special type, built into C#, that holds a group of other types. Arrays are a useful construct for organizing data. They provide matrix support and, even further, multidimensional support. As a collection, an array allows going beyond simple storage of data and provides fundamental discovery and manipulation of that data. Array methods are discussed in the next chapter. This chapter shows declaration and instantiation of single-dimension, multidimension, and jagged arrays.

Single-Dimension Arrays
Single-dimension arrays provide the capability to store and manipulate a list of items. Every element is of the same type. This is how such an array should be declared:
Type[] Array-Identifier [initializer] ;
The array identifier is any valid identifier. It should be meaningful for the purpose of the array. The optional initializer allocates memory for the array.
TIP
Once a C# array has been initialized, it can't change its size. If dynamic resizing of an array is needed, use one of the collection classes.
Here are some examples of single-dimensional array declarations:
// uninitialized declaration
MyClass[] myArray;
byte[] inputBuffer = new byte[2048];

// creates an array of 3 strings
string[] countStrings = { "one", "two", "three" };
Arrays may be declared with no initialization. Remember, an array must be initialized before it's used. It may be initialized with an integer type value inside the brackets of the initializer, as the following example shows:
// creates an array of 3 strings
string[] countStrings = new string[3] { "one ", " two", " three" };

Another way to initialize an array is by leaving the space in the brackets of the initializer blank and then following the initializer brackets with an initializer list in braces. The array initializer list is a comma-separated list of values of the array type. The size of the array becomes the number of elements in the initializer list. If an integer value is added to the initializer brackets and there is an initializer list in the same array initializer, make sure that the integer value in the initializer brackets is greater than or equal to the number of elements in the initializer list. Take a look at this code sample:
// error
string[] countStrings = new string[3] { "one", "two", "three", "four" };
The initializer in this code fails with an error because the allocated size of the countStrings array is only 3, but the number of strings in the list is four. The number of strings in the list can't exceed the allocated size.

TIP
Don't specify a size when using an initializer list on an array. Later, it will be easier to add an item to the list and avoid the mistake of not incrementing the number.


Dimensional Arrays
Multidimensional arrays are similar in declaration and initialization to single-dimensional arrays, with a few exceptions. For every new dimension included, add a comma to brackets of the array declaration. During initialization, add an integer value to each of the dimensions specified in the declaration. Here are a couple examples:
long [ , ] determinant = new long[4, 4];
int [ , , ] stateSpace = new int[2, 5, 4];
bool [ , ] exclusiveOr
= new bool[2, 2] { {false, true}, {true, false} };
Remember, the integer values in the initializer brackets are optional when including an initializer list.

Jagged Arrays
Jagged arrays allow creation of multidimensional arrays without the requirement of making every element of every dimension the same size. If an application has data that doesn't cover the entire range of possible values, this may be an option. It may also open the opportunity to save memory space. Here are some examples:
decimal[][] monthlyVariations = new decimal[12][];

monthlyVariations[(int)Months.Jan] = new decimal[31];
monthlyVariations[(int)Months.Feb] = new decimal[28];
.
.
.
monthlyVariations[(int)Months.Dec] = new decimal[31];

Friday, February 13, 2009

SQL SERVER - Fix : Error : Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table

Server: Msg 544, Level 16, State 1, Line 1Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

This error message appears when you try to insert a value into a column for which the IDENTITY property was declared, but without having set the IDENTITY_INSERT setting for the table to ON.

Fix/WorkAround/Solution:
--Turn Identity Insert ON so records can be inserted in the Identity Column
SET IDENTITY_INSERT [dbo].[TableName] ON
GO
INSERT INTO [dbo].[TableName] ( [ID], [Name] )VALUES ( 2, 'InsertName')
GO
--Turn Identity Insert OFF
SET IDENTITY_INSERT [dbo].[TableName] OFF
GO


Setting the IDENTITY_INSERT to ON allows explicit values to be inserted into the identity column of a table. Execute permissions for the SET IDENTITY_INSERT default to the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles, and the object owner.

Find Weekdays Between Two Dates (SQL Function-UDF)

Following user defined function returns number of weekdays between two dates specified. This function excludes the dates which are passed as input params. It excludes Saturday and Sunday as they are weekends.
CREATE FUNCTION dbo.spDBA_GetWeekDays( @StartDate datetime,@EndDate datetime )RETURNS INT AS
BEGIN
DECLARE @WorkDays INT,
@FirstPart INT
DECLARE @FirstNum INT, @TotalDays INT
DECLARE @LastNum INT, @LastPart INT
IF (DATEDIFF(DAY, @StartDate, @EndDate) 0)
THEN @LastPart - 1ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
RETURN ( @WorkDays )END
GO

This function can be used as
SELECT dbo.spDBA_GetWeekDays (‘10/10/2008′, ‘11/22/2008′)
GO

Find day of the Current Week

Very Simple Script which find Day of the Current Week;


SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)
Result:
2009-02-09 00:00:00.000 --Day(0) --Monday


SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 4)
Result:
2009-02-13 00:00:00.000 --Day (4) --Friday

Get Number of Days in Month (SQL Function-UDF)

Following User Defined Function (UDF) returns the numbers of days in month. It is very simple yet very powerful and full proof UDF.

CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth]
( @myDateTime DATETIME )RETURNS INT AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0 AND YEAR(@myDateTime) % 100 != 0) OR (YEAR (@myDateTime) % 400 = 0) THEN 29
ELSE 28 END
END
RETURN @rtDate
END
GO




Run following script in Query Editor:

SELECT dbo.udf_GetNumDaysInMonth(GETDATE()) -- Today = 13.02.2009

Result :

28

Few Useful DateTime Functions to Find Specific Dates

Few Useful DateTime Functions to Find Specific Dates functions.
—-Today
SELECT GETDATE() ‘Today’
—-Yesterday
SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’
—-First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’
—-Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’
—-First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’
—-Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’
—-First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’
—-Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’
—-First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’
—-Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) ‘Last Day of Last Month’
—-First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’
—-Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) ‘Last Day of Current Year’
—-First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’
—-Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) ‘Last Day of Last Year’

How to create and use a sample sql cursor and cursor code

Here is a sample sql cursor code created for looping through a list of records as a result of a select query, which enables the sql developer to execute a stored procedure for each row in the cursor which use the values fetched by the cursor as the input arguments. The sample cursor is developed on a SQL Server and is a sample for sql server cursor. The sql codes may use t-sql codes so the sample may have differences than a typical pl sql cursor or an oracle cursor.

The sample sql cursor codes below illustrates a process of merging dublicate customer records kept in an application database. Assume that the dublicate customers list and relation among the duclicate customer records are inserted into and kept in a table named DublicateCustomers which is simply formed of columns MasterCustomerId, DublicateCustomerId and some other columns like MergeDate, IsMerged, MergedByUserId, InsertedDate, InsertedByUserId, etc which are used during processing some details and useful in the reporting of the dublicate record merge process results.



The list of the original customer records and the dublicate customer records can be selected by the sql select query below:

SELECT MasterCustomerId, DublicateCustomerIdFROM DublicateCustomers WHERE IsMerged = 0

You can either create a temporary table to keep the result set in order to use your initial set of records in the next steps of your process or you can just use the above sql query to supply your records set to feed the cursor we will create.
Here with the variable declarations we will set column values we fetch with the cursor to the variables.


DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DublicateId Int

Then the sql cursor definition or the cursor declaration code takes place.

DECLARE merge_cursor CURSOR FAST_FORWARD FOR
SELECT MasterCustomerId, DublicateCustomerId
FROM DublicateCustomers WHERE IsMerged = 0

During the cursor declararion you can set the cursor properties or the attributes of the cursor. Note that the sample cursor declaration uses the FAST_FORWARD key attribute in order to create a cursor with a high performance. Since FAST_FORWARD states that the cursor is FORWARD_ONLY and READ_ONLY the performance of the cursor is optimized.
The t-sql syntax of cursor declaration command DECLARE CURSOR is stated as below :

DECLARE cursor_name CURSOR [ LOCAL GLOBAL ] [ FORWARD_ONLY SCROLL ] [ STATIC KEYSET DYNAMIC FAST_FORWARD ] [ READ_ONLY SCROLL_LOCKS OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

You can find more on how to declare a cursor and cursor attributes in Books Online
With the call of the OPEN command the t-sql server cursor is opened and the cursor is populated with the data by the execution of the select query of the DECLARE CURSOR command.


OPEN merge_cursor

So the OPEN command runs or executes the "SELECT MasterCustomerId, DublicateCustomerId FROM DublicateCustomers WHERE IsMerged = 0"
select query defined in the DECLARE CURSOR definition command which is set after FOR key.

With the execution of this select query the cursor is populated with the rows or the data returned as a result set of the query.

The next step in using a cursor is fetching the rows from the populated cursor one by one.

FETCH NEXT FROM merge_cursor INTO @MasterId, @DublicateId
The syntax of the FETCH command is as follows
FETCH [ [ NEXT PRIOR FIRST LAST ABSOLUTE { n @nvar } RELATIVE { n @nvar } ] FROM ] { { [ GLOBAL ] cursor_name } @cursor_variable_name } [ INTO @variable_name [ ,...n ] ]

With the use of the NEXT, the FETCH NEXT command returns the row following the current row. If FETCH NEXT is called for the first time for a cursor, or we can say if it is called after the OPEN CURSOR command, then the first row in the returned result set is fetched or returned. The column values in the returned row can be set into variables with the INTO key and by giving the names of the variables as a comma seperated list after the INTO key.
So for our sample the first row in the return result set of the cursor is set into two variables named @MasterId and @DublicateId. Here one important point is the first column of the result set (column named MasterCustomerId) is set to first variable defined in the list which is the @MasterId variable. And the secod column named DublicateCustomerId is set to the second variable @DublicateId.
So the variable types must be carefully declared according to the column types of the selected rows.
After the FETCH command, you should always control the value of the @@FETCH_STATUS. This variable returns the status of the last cursor FETCH command in the current connection.
The possible return values of @@FETCH_STATUS are;
0
FETCH statement was successful
-1
FETCH statement failed or the row was beyond the result set
-2
Row fetched is missing
By always checking the @@FETCH_STATUS and controlling that it is value is equal to "0" we will have a new row fetched. When the fetched status is different than the "0" we can say that we have no more records are fetched. In short, the value of @@FETCH_STATUS variable is the controlling parameter of the loop we will use during processing all records or rows in the cursor.
In the body part of the WHILE statement the codes to process each row returned by the cursor takes place. This code block changes according to your reason to create and use a cursor. I placed an EXEC call for a sql stored procedure and an UPDATE sql statement here in order to show as a sample.
The most important thing to care for the inside codes of the WHILE code block is the last code statement FETCH NEXT command is recalled to get the next row from the return cursor data set.
After all the records are processed the @@FETCH_STATUS parameter returns -1, so the cursor can be now closed with the CLOSE CURSOR command. CLOSE CURSOR releases the current result set. And the DEALLOCATE CURSOR command releases the last cursor reference.
Here you can find the full sample sql cursor code used in this article for explaining the cursors in SQL Server.


DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DublicateId Int
SELECT @MergeDate = GetDate()
DECLARE merge_cursor CURSOR FAST_FORWARD FOR
SELECT MasterCustomerId, DublicateCustomerId FROM DublicateCustomers WHERE IsMerged = 0
OPEN merge_cursor FETCH NEXT FROM merge_cursor
INTO @MasterId, @DublicateId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC MergeDuplicateCustomers @MasterId, @DublicateId
UPDATE DublicateCustomersSETIsMerged = 1,MergeDate = @MergeDate
WHERE
MasterCustomerId = @MasterId ANDDublicateCustomerId = @DublicateId
FETCH NEXT FROM merge_cursor
INTO @MasterId, @DublicateId
END
CLOSE merge_cursor
DEALLOCATE merge_cursor

Query with transaction isolation level set to READ UNCOMMITTED fails with error 601

When a query runs with its transaction isolation level set to READ UNCOMMITTED, SQL Server 2000 aborts the query and returns the following error message:
Server:
Msg 601, Level 12, State 3, Line 14 Could not continue scan with NOLOCK due to data movement.
COUSE:
This behavior may occur when a row in a table is deleted between the time SQL Server reads the location of the row from an index and the time SQL Server fetches the row.

RESOLUTION:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.

You can run the following query in Query Analyzer to dynamically turn on the trace flag:

DBCC TRACEON (9134, -1)

Number To Text Function on MS SQL (English Version)

If you need Converts a numeric value to string on the MS SQL , use the "fNumberToText" function.


Script Code:



-- Name: Convert Number to Text
-- Description:Converts a numeric value to string.
--converts numeric value to string
--handles up to 2 decimal places, with rounding
--if @isMoney = true then it will output dollars and cents
CREATE function dbo.fNumberToText (@num AS numeric(18,2), @isMoney as bit)
returns varchar(255)
AS

BEGIN
--declare @num numeric(18,2)
--declare @isMoney bit
--set @num = 8000000000.17
--set @isMoney = 1
DECLARE @Split tinyint
DECLARE @iSplit tinyint
DECLARE @sNum varchar(20)
DECLARE @NumSet varchar(3)
DECLARE @Char char(1)
DECLARE @NumText varchar(255)
DECLARE @SetText varchar(100)
DECLARE @HunText varchar(100)
DECLARE @CharText varchar(50)
DECLARE @Match bit
DECLARE @Point tinyint
DECLARE @Cents varchar(2)
DECLARE @CentText varchar(100)
DECLARE @NumStrings TABLE (Num int, NumStr varchar(20))
INSERT INTO @NumStrings
SELECT 1, ' One'
UNION SELECT 2, ' Two'
UNION SELECT 3, ' Three'
UNION SELECT 4, ' Four'
UNION SELECT 5, ' Five'
UNION SELECT 6, ' Six'
UNION SELECT 7, ' Seven'
UNION SELECT 8, ' Eight'
UNION SELECT 9, ' Nine'
UNION SELECT 10, ' Ten'
UNION SELECT 11, ' Eleven'
UNION SELECT 12, ' Twelve'
UNION SELECT 13, ' Thirteen'
UNION SELECT 14, ' Fourteen'
UNION SELECT 15, ' Fifteen'
UNION SELECT 16, ' Sixteen'
UNION SELECT 17, ' Seventeen'
UNION SELECT 18, ' Eighteen'
UNION SELECT 19, ' Nineteen'
UNION SELECT 20, ' Twenty'
UNION SELECT 30, ' Thirty'
UNION SELECT 40, ' Fourty'
UNION SELECT 50, ' Fifty'
UNION SELECT 60, ' Sixty'
UNION SELECT 70, ' Seventy'
UNION SELECT 80, ' Eighty'
UNION SELECT 90, ' Ninety'
SET @sNum = cast(@num as varchar(20))
--convert any cent text first, then the
-- whole number
SET @Point = charindex('.', @sNum)
IF @Point > 0
BEGIN
SET @Cents = substring(@sNum, @Point + 1, 2)
SET @sNum = left(@sNum, @Point-1)
--if isMoney THEN combine the two digits (eg 11 = eleven)
IF @isMoney = 1
BEGIN --look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = @Cents)
IF @Match <> 0
BEGIN
SET @CentText = (select NumStr FROM @NumStrings WHERE Num = @Cents)
END
ELSE
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@Cents, 1))
END
END
ELSE --if NOT isMoney THEN treat each digit seperately (eg 11 = one one)
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1)),'') + isnull((select NumStr from @NumStrings where Num = right(@Cents, 1)),'')
END
END
IF @CentText IS NULL
SET @CentText = ''
--break the number into blocks of 3 char
-- acters
SET @Split = ((len(@sNum)-1) / 3) + 1
SET @iSplit = 0
SET @NumText = ''
WHILE @iSplit < @Split
BEGIN
SET @CharText = ''
SET @HunText = ''
SET @SetText = ''
SET @NumSet = right(left(@sNum, len(@sNum) - @iSplit * 3), 3)
IF len(@Numset) = 3 --Calculate ANY hundreds
BEGIN
SET @Char = left(@NumSet, 1)
SET @HunText = isnull((select NumStr FROM @NumStrings WHERE Num = @Char) + ' Hundred', '')
SET @SetText = @HunText
END
--look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = right(@NumSet, 2))
IF @Match <> 0
BEGIN
SET @CharText = (select NumStr FROM @NumStrings WHERE Num = right(@NumSet, 2))
END
ELSE
BEGIN
SET @CharText = isnull((select NumStr FROM @NumStrings WHERE Num = left(right(@NumSet, 2), 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@NumSet, 1))
END
--make sure there IS something in @CharText AND @SetText (ie IF @NumSet = x00, x000)
IF @CharText IS NULL SET @CharText = ''
IF @SetText IS NULL set @SetText = ''
--seperate ANY hundreds FROM tens/units WITH an 'and'
IF @HunText <> '' AND @CharText <> ''
SET @SetText = @SetText + ' and'
--if there are no hundreds, it's the smallest SET AND there are other sets, ADD an 'and' before
IF @HunText = '' AND @CharText <> '' and @iSplit = 0 and @Split > 1 and @CentText = ''
SET @SetText = ' and' + @SetText
SET @SetText = @SetText + @CharText
--append the SET suffix text. ADD a ',' AS long as there IS something in the lowest set
IF @SetText <> ''
SET @SetText = @SetText + CASE @iSplit
WHEN 0 THEN ''
WHEN 1 THEN ' Thousand'
WHEN 2 THEN ' Million'
WHEN 3 THEN ' Billion'
WHEN 4 THEN ' Trillion'
WHEN 5 THEN ' Quadrillion'
END + CASE WHEN len(@NumText) > 0 THEN ',' ELSE '' end
SET @NumText = @SetText + @NumText
SET @iSplit = @iSplit + 1
END
--add any dollars and cent text
IF @isMoney = 1 AND @NumText <> '' and rtrim(ltrim(@NumText)) <> 'One'
SET @NumText = @NumText + ' Dollars'
IF @isMoney = 1 AND rtrim(ltrim(@NumText)) = 'One'
SET @NumText = @NumText + ' Dollar'
IF @isMoney = 1 AND @CentText <> '' and rtrim(ltrim(@CentText)) <> 'One'
SET @CentText = @CentText + ' Cents'
IF @isMoney = 1 AND rtrim(ltrim(@CentText)) = 'One'
SET @CentText = @CentText + ' Cent'
IF @isMoney = 0 AND @CentText <> ''
SET @CentText = ' Point' + @CentText
IF @isMoney = 1 AND @NumText <> '' and @CentText <> ''
SET @CentText = ' and' + @CentText
--combine dollars and cents
SET @NumText = @NumText + @CentText
--add 'Minus' for negative numbers
IF left(@sNum, 1) = '-'
SET @NumText = 'Minus' + @NumText
RETURN @NumText
END

How Do I: Use the ASP.NET AJAX FilteredTextbox Extender?


The FilteredTextbox Extender control enables the Textbox server control of ASP.Net to filter the input given by user that allows only valid input characters into the textbox. You can use the FilteredTextboxExtender for the field of username to validate and filter the invalid characters e.g.: allows the input characters as alphabets + special characters (- or _) + numbers.

There is a limitation of using FilteredTextboxExtender that user can disable the javascript feature of web browser. This allows the user to input any invalid characters. So, you cannot rely totally on this extender control. You must also verify the input before saving it into the data store otherwise it could cause exceptions later on.
FilteredTextboxExtender Properties

TargetControlID: ID of Textbox control to bind the FilteredTextboxExtender with it.

FilterType: There are four types of filters Custom, LowercaseLetters, Numbers, UppercaseLetters. You can specify one of these filters or comma-separated combinations.If FilterType is set as Custom then you must specify the character ValidChars also.

FilterMode: There are two types of filter mode ValidChars, InvalidChars.ValidChars FilterMode works with FilterType having combination of filters and must contain Custom. You must also specify the valid characters in ValidChars property. InvalidChars FilterMode works only with Custom FilterType.

ValidChars: List of valid characters. ValidChars property works only if FilterType contains Custom otherwise it is ignored.

InvalidChars: List of invalid characters. Invalid property works with Custom FilterType. Also you must select the InvalidChars FilterMode.

FilterInterval: Time in milliseconds to filter the invalid characters within the specified time interval. Default value is 250 milliseconds.


Sample Code:












Saturday, February 7, 2009

Tables to search on the SQL Server 2005 with query

If you want to list table with query , run the following query.

select * from INFORMATION_SCHEMA.TABLES where
TABLE_NAME LIKE '%USER%';

Result:

ASP.NET compile Error : Reply from ::1: time<1ms

Problem:
Whenever a project or a file is run, the address http://localhost:// fails to load on all browsers. I disabled/enabled proxy servers with exclusions and what not to see if it was the proxy that was causing this, but had no luck at all. Funny enough, if I manually change the address to http://127.0.0.1://, the website which I am debugging/running loads.


Command:> ping localhost
Pinging hong001 [::1] with 32 bytes of data:
Reply from ::1: time<1ms sent =" 4," received =" 4," lost =" 0" minimum =" 0ms," maximum =" 0ms," average =" 0ms">ping 127.0.0.1
Pinging 127.0.0.1 with 32 bytes of data:
Reply from 127.0.0.1: bytes=32 time<1ms ttl="128" bytes="32" ttl="128" bytes="32" ttl="128" bytes="32" ttl="128" sent =" 4," received =" 4," lost =" 0" minimum =" 0ms," maximum =" 0ms," average =" 0ms">Solution:
You must be disable IPv6 on your Computer.

HOW to Disable IPv6:

  • Windows XP: Start->Run->cmd /k ipv6 uninstall
  • uninstallUnlike Windows XP, IPv6 in Windows Vista cannot be uninstalled.

Friday, February 6, 2009

Unity Rapor Üretici (Dinamik Sorgu)

Unity'deki rapor üreticiyi kullanarak kendi sorgularınızı programa ilave edebilirsiniz. Ancak programın tablo yapısından dolayı her yıl kullanılan tablo isimleri değişmektedir.

Örneğin , 2008 deki Firma kodu 208 ve dönemi 01 olan cari kart tablosu LG_208_01_CLCARD iken 2009 yılında firma kodu 209 olarak değiştirilirse LG_209_01_CLCARD olacaktır.



  • STATİK SORGU
    select II.CODE, II.NAME, SUM(O.AMOUNT) AS 'Miktar',S3.CODE AS LOT, O.ADRESS
    FROM OLYMPOS_ADRES O WITH (NOLOCK) INNER JOIN LG_108_ITEMS II WITH (NOLOCK) on O.ITEMREF =II.LOGICALREF
    INNER JOIN LG_108_01_SERILOTN S3 WITH (NOLOCK) ON S3.ITEMREF =II.LOGICALREF AND S3.LOGICALREF=O.LOTREF
    WHERE SHIPMENT = 0
    GROUP BY II.CODE, II.NAME,S3.CODE, O.ADRESS

  • DİNAMİK SORGU
    select II.CODE, II.NAME, SUM(O.AMOUNT) AS 'Miktar',S3.CODE AS LOT, O.ADRESS
    FROM OLYMPOS_ADRES O WITH (NOLOCK) INNER JOIN {ITEMS} II WITH (NOLOCK) on O.ITEMREF =II.LOGICALREF
    INNER JOIN {SERILOTN} S3 WITH (NOLOCK) ON S3.ITEMREF =II.LOGICALREF AND S3.LOGICALREF=O.LOTREF
    WHERE SHIPMENT = 0
    GROUP BY II.CODE, II.NAME,S3.CODE, O.ADRESS


Convert Polish Złoty (PLN) to text function

A number of Polish Złoty (PLN) to convert to text function.

Using:
SELECT [dbo].[fNumberToTextPol] (3213,22)

Result:



SQL Script:
create function [dbo].[fNumberToTextPol] (@num AS numeric(18,2), @isMoney as bit)
returns varchar(255)
AS

BEGIN
--converts numeric value to string
--handles up to 2 decimal places, with r
-- ounding
--if @isMoney = true then it will output
-- Zloty
--created by: serkan sonmez
--created on: 28/12/2008
-- declare @num numeric(18,2)
-- declare @isMoney bit
-- set @num = 1288.32
-- set @isMoney = 1

DECLARE @Split tinyint
DECLARE @iSplit tinyint
DECLARE @sNum varchar(20)
DECLARE @NumSet varchar(3)
DECLARE @Char char(3)
DECLARE @NumText varchar(255)
DECLARE @SetText varchar(100)
DECLARE @HunText varchar(100)
DECLARE @CharText varchar(50)
DECLARE @Match bit
DECLARE @Point tinyint
DECLARE @LEN_ tinyint
DECLARE @Cents varchar(2)
DECLARE @CentText varchar(100)
DECLARE @NumStrings TABLE (Num int, NumStr varchar(20))
INSERT INTO @NumStrings
SELECT 1, ' jeden'
UNION SELECT 2, ' dwa'
UNION SELECT 3, ' trzy'
UNION SELECT 4, ' cztery'
UNION SELECT 5, ' pięć'
UNION SELECT 6, ' sześć'
UNION SELECT 7, ' siedem'
UNION SELECT 8, ' osiem'
UNION SELECT 9, ' dziewięć'
UNION SELECT 10, ' dziesięć'
UNION SELECT 11, ' jedenaście'
UNION SELECT 12, ' dwanaście'
UNION SELECT 13, ' trzynaście'
UNION SELECT 14, ' czternaście'
UNION SELECT 15, ' piętnaście'
UNION SELECT 16, ' szesnaście'
UNION SELECT 17, ' siedemnaście'
UNION SELECT 18, ' osiemnaście'
UNION SELECT 19, ' dziewiętnaście'
UNION SELECT 20, ' dwadzieścia'
UNION SELECT 30, ' trzydzieści'
UNION SELECT 40, ' czterdzieści'
UNION SELECT 50, ' pięćdziesiąt'
UNION SELECT 60, ' sześćdziesiąt'
UNION SELECT 70, ' siedemdziesiąt'
UNION SELECT 80, ' osiemdziesiąt'
UNION SELECT 90, ' dziewięćdziesiąt'
UNION SELECT 100, ' sto'
UNION SELECT 200, ' dwieście'
UNION SELECT 300, ' Trzysta'
UNION SELECT 400, ' czterysta'
UNION SELECT 500, ' pięćset'
UNION SELECT 600, ' sześćset'
UNION SELECT 700, ' siedemset'
UNION SELECT 800, ' osiemset'
UNION SELECT 900, ' dziewięćset'

SET @sNum = cast(@num as varchar(20))
--convert any cent text first, then the
-- whole number
SET @Point = charindex('.', @sNum)
IF @Point > 0
BEGIN
SET @Cents = substring(@sNum, @Point + 1, 2)
SET @sNum = left(@sNum, @Point-1)
--if isMoney THEN combine the two digits (eg 11 = eleven)
IF @isMoney = 1
BEGIN --look FOR matches WITH the RIGHT two characters
SET @Match = (select count(*) FROM @NumStrings WHERE Num = @Cents)
IF @Match <> 0
BEGIN
SET @CentText = (select NumStr FROM @NumStrings WHERE Num = @Cents)
END
ELSE
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@Cents, 1))
END
END
ELSE --if NOT isMoney THEN treat each digit seperately (eg 11 = one one)
BEGIN
SET @CentText = isnull((select NumStr FROM @NumStrings WHERE Num = left(@Cents, 1)),'') + isnull((select NumStr from @NumStrings where Num = right(@Cents, 1)),'')
END
END
SET @CentText = CASE LEN(@Cents) WHEN 1 THEN '/10' WHEN 2 THEN '/100' END
SET @CentText = @Cents + @CentText
--@CentText
IF @CentText IS NULL
SET @CentText = ''
--break the number into blocks of 3 char
-- acters
SET @Split = ((len(@sNum)-1) / 3) + 1
SET @iSplit = 0
SET @NumText = ''
WHILE @iSplit < @Split BEGIN SET @CharText = '' SET @HunText = '' SET @SetText = '' SET @NumSet = right(left(@sNum, len(@sNum) - @iSplit * 3), 3) IF len(@Numset) = 3 --Calculate ANY hundreds BEGIN SET @Char = left(@NumSet, 1) + '00' SET @HunText = isnull((select NumStr FROM @NumStrings WHERE Num = @Char) + '', '') SET @SetText = @HunText END --look FOR matches WITH the RIGHT two characters SET @Match = (select count(*) FROM @NumStrings WHERE Num = right(@NumSet, 2)) IF @Match <> 0
BEGIN
SET @CharText = (select NumStr FROM @NumStrings WHERE Num = right(@NumSet, 2))
END
ELSE
BEGIN
SET @CharText = isnull((select NumStr FROM @NumStrings WHERE Num = left(right(@NumSet, 2), 1) + '0'),'') + (select NumStr from @NumStrings where Num = right(@NumSet, 1))
END
--make sure there IS something in @CharText AND @SetText (ie IF @NumSet = x00, x000)
IF @CharText IS NULL SET @CharText = ''
IF @SetText IS NULL set @SetText = ''
--seperate ANY hundreds FROM tens/units WITH an 'and'
IF @HunText <> '' AND @CharText <> ''
SET @SetText = @SetText + ' '
--if there are no hundreds, it's the smallest SET AND there are other sets, ADD an 'and' before
IF @HunText = '' AND @CharText <> '' and @iSplit = 0 and @Split > 1 and @CentText = ''
--SET @SetText = ' and' + @SetText
SET @SetText = '' + @SetText
SET @SetText = @SetText + @CharText
--append the SET suffix text. ADD a ',' AS long as there IS something in the lowest set
IF @SetText <> ''
SET @SetText = @SetText + CASE @iSplit
WHEN 0 THEN ''
WHEN 1 THEN ' tysiąc'
WHEN 2 THEN ' milion'
WHEN 3 THEN ' milionów'
WHEN 4 THEN ' trillion'
WHEN 5 THEN ' quadrillion'
END + CASE WHEN len(@NumText) > 0 THEN ',' ELSE '' end
SET @NumText = @SetText + @NumText
SET @iSplit = @iSplit + 1
END
--add any dollars and cent text
IF @isMoney = 1 AND @NumText <> '' and rtrim(ltrim(@NumText)) <> 'jeden'
--SET @NumText = @NumText + ' Dollars'
SET @NumText = @NumText + ' PLN'
IF @isMoney = 1 AND rtrim(ltrim(@NumText)) = 'jeden'
--SET @NumText = @NumText + ' Dollar'
SET @NumText = @NumText + ' PLN'
IF @isMoney = 1 AND @CentText <> '' and rtrim(ltrim(@CentText)) <> 'jeden'
SET @CentText = @CentText + ''
IF @isMoney = 1 AND rtrim(ltrim(@CentText)) = 'jeden'
SET @CentText = @CentText + ''
IF @isMoney = 0 AND @CentText <> ''
SET @CentText = ' Point' + @CentText
IF @isMoney = 1 AND @NumText <> '' and @CentText <> ''
--SET @CentText = ' and' + @CentText
SET @CentText = ' ' + @CentText
--combine dollars and cents
SET @NumText = @NumText + @CentText
--add 'Minus' for negative numbers
IF left(@sNum, 1) = '-'
SET @NumText = 'Minus' + @NumText
RETURN @NumText
END

How can I start using ajax calendar?

Calendar is an ASP.NET AJAX extender that can be attached to any ASP.NET TextBox control. It provides client-side date-picking functionality with customizable date format and UI in a popup control. You can interact with the calendar by clicking on a day to set the date, or the "Today" link to set the current date.
In addition, the left and right arrows can be used to move forward or back a month. By clicking on the title of the calendar you can change the view from Days in the current month, to Months in the current year. Another click will switch to Years in the current Decade. This action allows you to easily jump to dates in the past or the future from within the calendar control.
The page uses the culture setting Türkçe (Türkiye) which was specified by the browser. The page properties have Culture="auto" and UICulture="auto" set to enable the same. See this MSDN article for more information. The ScriptManager on this Calendar demo page has EnableScriptGlobalization="true" and EnableScriptLocalization="true".



  • Calendar Properties
    The calendar associated with a button has been initialized with this code. The properties in italic are optional:
    TargetControlID - The ID of the TextBox to extend with the calendar.
    CssClass - Name of the CSS class used to style the calendar. See the Calendar Theming section for more information.
    Format - Format string used to display the selected date.
    PopupButtonID - The ID of a control to show the calendar popup when clicked. If this value is not set, the calendar will pop up when the textbox receives focus.
    PopupPosition - Indicates where the calendar popup should appear at the BottomLeft(default), BottomRight, TopLeft, TopRight, Left or Right of the TextBox.
    SelectedDate - Indicates the date the Calendar extender is initialized with.

Sample Code:










ForeColor="#666666">İlk Tarih
ID="ImgBntCalc2" runat="server" CausesValidation="False" ImageUrl="~/images/Calendar_scheduleHS.png" />ID="MaskedEditExtender51" runat="server" CultureAMPMPlaceholder="" CultureCurrencySymbolPlaceholder=""
CultureDateFormat="" CultureDatePlaceholder="" CultureDecimalPlaceholder="" CultureThousandsPlaceholder=""
CultureTimePlaceholder="" Enabled="True" ErrorTooltipEnabled="True" Mask="99/99/9999"
MaskType="Date" TargetControlID="frm_DateAra">

ControlToValidate="frm_DateAra" Display="Dynamic" EmptyValueBlurredText="*" EmptyValueMessage="Date is required"
ErrorMessage="MaskedEditValidator51" Font-Size="11px" InvalidValueBlurredMessage="*"
InvalidValueMessage="Geçersiz Tarih" TooltipMessage="Tarih Arama" ValidationGroup="MKE3">
ID="CalendarExtender51" runat="server" Enabled="True" PopupButtonID="ImgBntCalc2"
TargetControlID="frm_DateAra">






Google translate now türkçe


Than a service from Google. Previously, in many languages on the Turkish translations that translate added. Language structure in the middle due to the excellent translation do not see your business.

http://translate.google.com/?hl=tr&sl=tr&tl=en

Wednesday, February 4, 2009

Unity'de Kullanıcı Hareketlerini Sorgulamak

Unity'de Kullanıcı hareketlerini sistem işletmeni dışında sorguyla da alabilirsiniz.

Örnek Sorgu:
SELECT USERNAME,FIRMNO,DATE_,MSGS1,MSGS2 FROM LG_000_SYSLOG WHERE USERNAME = 'Serkan SONMEZ'

Sonuç :

Kullanıcı Limiti Hatası

Unity'e giriş yaparken "Kullanıcı Limiti aşılmıştır" uyarısı geliyorsa aşağıdaki sorgu çalıştırılarak programa giriş yapılabilir.

TRUNCATE TABLE L_GOUSERS

UDF function: AT

AT :
MS-SQL'de string alanda bir karakter veya karakter grubunun kaçıncı sırada olduğunu bulmak için kullanılan UDF (User Definite Function) dir.

Kullanımı:
declare @gcString nvarchar(4000), @gcFindString nvarchar(4000)
select @gcString = 'Tüm insanlar kardeştir', @gcFindString = 'in'
select dbo.AT(@gcFindString, @gcString, default) -- dönen değer=5

Create Scripti:

CREATE function AT (@cSearchExpression nvarchar(4000), @cExpressionSearched nvarchar(4000), @nOccurrence smallint = 1 )
returns smallint
as
begin
if @nOccurrence > 0
begin
declare @i smallint, @StartingPosition smallint
select @i = 0, @StartingPosition = -1
while @StartingPosition <> 0 and @nOccurrence > @i
select @i = @i + 1, @StartingPosition = charindex(@cSearchExpression COLLATE Latin1_General_BIN, @cExpressionSearched COLLATE Latin1_General_BIN, @StartingPosition+1 )
end
else
set @StartingPosition = NULL

return @StartingPosition
end
GO

Cari Risklerini Update eden Query

Unity'de herhangi bir sebepten dolayı risk bilgilerinin bozulması durumunda aşağıdaki cursor ile Cari riskleri update edilir.

DECLARE @CLREF INT
DECLARE @DEFINITION_ VARCHAR(150)
DECLARE @TOTAL1 FLOAT
DECLARE @TOTAL2 FLOAT
DECLARE YETKIKODU CURSOR FOR
SELECT LOGICALREF,DEFINITION_ FROM LG_208_CLCARD (NOLOCK) WHERE
CODE LIKE 'M.%' AND LOGICALREF<>1010
ORDER BY DEFINITION_
OPEN YETKIKODU
FETCH NEXT FROM YETKIKODU
INTO @CLREF,@DEFINITION_
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TOTAL1=SUM((AMOUNT-SHIPPEDAMOUNT)*PRICE)
+SUM(((AMOUNT-SHIPPEDAMOUNT)*PRICE*VAT)/100)
-SUM((((AMOUNT-SHIPPEDAMOUNT)*PRICE)
+(((AMOUNT-SHIPPEDAMOUNT)*PRICE*VAT)/100))*(DISTDISC/TOTAL))
FROM LG_208_01_ORFLINE ORFL (NOLOCK)
INNER JOIN LG_208_CLCARD (NOLOCK) CL ON ORFL.CLIENTREF=CL.LOGICALREF
WHERE LINETYPE =0 AND CLOSED =0 AND
(AMOUNT-SHIPPEDAMOUNT)>0 AND CL.LOGICALREF=@CLREF
SELECT @TOTAL2=ORDRISKTOTAL FROM LG_208_01_CLRNUMS (NOLOCK)
WHERE CLCARDREF= @CLREF
SET @TOTAL1=FLOOR(@TOTAL1)
SET @TOTAL2=FLOOR(@TOTAL2)
IF @TOTAL2<>@TOTAL1
BEGIN
UPDATE LG_208_01_CLRNUMS SET ORDRISKTOTAL=@TOTAL1 WHERE CLCARDREF= @CLREF
END
FETCH NEXT FROM YETKIKODU
INTO @CLREF,@DEFINITION_
END
CLOSE YETKIKODU
DEALLOCATE YETKIKODU
GO