TSQL date

declare @StartDate datetime
,@EndDate datetime
set @StartDate = dateadd(d,-1, convert(datetime,convert(varchar(11),getdate(),109),109))
set @EndDate = @StartDate+1
select @StartDate,@EndDate


select * from table
where timevalue >= @StartDate and TimeValue < @EndDate

jquery

$("select[ID^='Note_']").click(function() {
var currID = $(this).attr("id");
var $options = $('option', $(this));
$options.each(function() {
$(this).text($(this).text() + $(this).attr("title"));
});
});
$("select[ID^='Note_']").bind("blur", function(event) {
//alert($(this).val());
var currID = $(this).attr("id");
var $options = $('option', $(this));
$options.each(function() {
$(this).text($(this).value());
});
});

SELECT DISTINCT

Interesting alternative to using SELECT DISTINCT if you’re loading results into a dataset. The following line creates a DataTable with only distinct rows:

DataTable dtDistinctData = dsData.Tables[0].DefaultView.ToTable(true);

How to stop printing blank pages

How to stop printing blank pages
If you are getting blank pages when you are printing a worksheet, then there are a number of possibilities:

1. You have some ‘unnoticed’ text in one of the cells, e.g. ‘.’ or ‘,’.

2. You have cells that contain text, but the text color is the same as the cell background color.

3. You have a cell with a formula that returns a blank value.

4. You have empty cells that are formatted with borders or shading.

5. You have set a print area that spans into more than one page.

Possible Resolutions:

1. For unnoticed text, press CTRL+END to find the last non-blank character, and delete it if it is not required. Repeat the process if necessary.

2. For formulas, display formulas by pressing CTRL+’~’ then try to find the formula that returns a blank value.

3. Set the print area to the required cells only. Read more.

How to Add, Remove ListItems from one ListBox to Another in JQuery?

$(document).ready(function() {
$("#btnAdd").click(function() {
$("#ListBox1 > option[@selected]").appendTo("#ListBox2");
});
$("#btnAddAll").click(function() {
$("#ListBox1 > option").appendTo("#ListBox2");
});
$("#btnRemove").click(function() {
$("#ListBox2 > option[@selected]").appendTo("#ListBox1");
});
$("#btnRemoveAll").click(function() {
$("#ListBox2 > option").appendTo("#ListBox1");
});
});



1
2
3

DataRow drBlank = dsList.Tables[0].NewRow();
drBlank["IsLabelPrinted"] = 0;
drBlank["GroupID"] = -1;
dsList.Tables[0].Rows.Add(drBlank);
dsList.AcceptChanges();

IP address regex

///

/// Validate that String is an IP address.
/// Between 0.0.0.0 and 255.255.255.255
///

protected Regex regIP = new Regex(
@"(?2[0-4]\d|25[0-5]|[01]?\d\d?)\.(?2[0-4]\d|25"
+ @"[0-5]|[01]?\d\d?)\.(?2[0-4]\d|25[0-5]|[01]?\d\d?)\.(?"
+ @"2[0-4]\d|25[0-5]|[01]?\d\d?)",
RegexOptions.IgnoreCase
| RegexOptions.CultureInvariant
| RegexOptions.IgnorePatternWhitespace
| RegexOptions.Compiled
);

Data source name not found and no default driver specified

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I had this error and want to let you know how it was resolved.

First, this was an ASP web application using a vb 6.0 dll to get data from a sql server 2005 database on a 64 bit windows server 2008 enterprise (vista like) server. I could only get the dll to work in component services as opposed to simply registering it.

It all worked fine upon setup, but after four windows updates one night, the error above was posted in the event viewer, and the web app crashed.

Here is the resolution:

In a 64 bit windows server operating system, there are TWO odbc managers. When you pull up the usual menu for the odbc / dsn system, it is for the 64 bit odbc manager, and 32 bit applications (vb 6.0) will not work using these dsn's.

This is where the 32 bit odbc manager is:

C:\Windows\SysWOW64\odbcad32.exe

I hope you do not have to go through what I and three Microsoft Support engineers had to to figure this out.

Jonathan

drop all constraints on a table

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'dotnetnuke'
set @table = 'tabs'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where constraint_catalog = @database and
table_name = @table
exec sp_executesql @sql
END

DB Copy of table

SELECT *
INTO copy_of_testlist
FROM testlist

PageMethods

PageMethods.Echo('Hello World!', onComplete, onTimeout, onError, onAbort, context, timeout, priority);

First of all, we must pass all the arguments that the page method is accepting, separated by a comma; in this case, the Echo method accepts one argument, the string to echo. After the list of the server method's arguments, we can pass the references to three methods to call when the page method returns:

•onComplete, is a function that will be called if the page method returns with no errors.
•onTimeout, will be called if the page method doesn't return a value within the specified timeout interval.
•onError, will be invoked if an error occurs during the process of the asynchronous request (for example, an Exception being raised on server-side).
•onAbort, is invoked when the asynchronous request is aborted. This can be done by calling the abort() method on the WebRequest object returned with the call to PageMethods.Echo().
•context, is the state object that will be passed to the callbacks when processing results.
•timeout, specifies the timeout before an asynchronous request is dropped. A response must arrive before the timeout occurs.
•priority, is a number that specifies the priority of the asynchronous request.

DataKeys of gridview

Convert.ToInt32(this.gridview1.DataKeys[row.RowIndex].Values[0])

Change UPPERCASE to lowercase on the fly

HI MOM, HOW ARE YOU TODAY?

(Shift-F3)

hi mom, how are you today?

(Shift-F3)

Hi mom, how are you today?

CSV to Array

// separate individual items between commas
string[] array = commaDelimited.Split(new char[] {','});

object[] myArray = new object[] { 1,2,3};
ArrayList = new ArrayList(myArray);

COALESCE SQL

COALESCE(expression1,...n) is equivalent to this CASE expression:

SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
(10.00, NULL, NULL, NULL),
(20.00, NULL, NULL, NULL),
(30.00, NULL, NULL, NULL),
(40.00, NULL, NULL, NULL),
(NULL, 10000.00, NULL, NULL),
(NULL, 20000.00, NULL, NULL),
(NULL, 30000.00, NULL, NULL),
(NULL, 40000.00, NULL, NULL),
(NULL, NULL, 15000, 3),
(NULL, NULL, 25000, 2),
(NULL, NULL, 20000, 6),
(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
GOHere is the result set.

Total Salary

------------

20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

56000.0000

SQL right, left Modulo

declare @a int, @b int, @c int, @d int
select @a = 568788
select @b = left(@a,3)
select @c = right(left(@a,3),2)
select @d = right(left(@a,3),2)%10
SELECT @b, @c, @d

--Output
568 68 8

SQL GROUP BY techniques

SELECT
C.CustomerID, C.CustomerName,
C.CustomerType, C.Address1, C.City,
C.State, S.TotalSales
FROM
Customers C
INNER JOIN
(SELECT
CustomerID, SUM(Sales) as TotalSales
FROM
Sales
GROUP BY
CustomerID) S
ON
C.CustomerID = S.CustomerID

MS SQL Check for empty String

MS SQL one cannot use the <> '' on text, ntext and varchar data types:

SQL:

SELECT * FROM myTable WHERE myField LIKE '_%'

OR If you want to get all the field with empty string use

SQL:

SELECT * FROM myTable WHERE myField NOT LIKE '_%'

What’s the maximum length for VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) data types?

The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

The maximum storage size for NVARCHAR(MAX) is also 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. Since each Unicode character in an NVARCHAR data type uses two bytes, the maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.

The maximum storage size for VARBINARY(MAX) is the same as the maximum storage size for VARCHAR(MAX) and NVARCHAR(MAX), which is 2^31-1 (2,147,483,647 bytes or 2GB - 1 bytes). The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length.