.NET Keychar and Keys

For keypress event sample code

private void txtEdit_KeyPress(object sender, KeyPressEventArgs e)
       {
           if(e.KeyChar== (char)Keys.Enter)
               if (txtEdit.Text != "")
                   lstUrls.Items.Add(txtEdit.Text);

       }

 

this does work.

 

But for Delete key it doesn’t work

private void lstUrls_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Delete)
                MessageBox.Show("Del");
        }

 

Focused in lstUrls listbox control pressing delete doesn’t evaluate e.KeyChar == (char)Keys.Delete condition to true.

Don’t know why.

SQL query for finding cumulative figures of a field/column

One thing everytime in our reporting comes the cumulative figure which we do best in excel by putting the values in a column and next to the column put a formula and drag.

 

But what if data resides in Database Server and we need to programmatically plot the cumulative figure to a graph and/or the from a huge number of rows you’ll need to aggregate then cumulate.

 

 

Say for example we have a table which returns when queried

 

Select * from dailysales

 

.

Date	No_Of_Sales
2008-04-16 15:03:21.080	3
2008-04-17 15:03:21.080	2
2008-04-18 10:07:19.507	5
...	...

 

Now we need another column to show the cumulative figure like.

 

For that we can use the following query:

 

select Date,  No_Of_Sales,

(select sum(No_Of_Sales)

from dailysales where Date<=d.Date) as Running_Total

from dailysales d

order by date

 

 

Which gives

 

 

Date	No_Of_Sales	Running_Total
2008-04-16 15:03:21.080	3	3
2008-04-17 15:03:21.080	2	5
2008-04-18 10:07:19.507	5	10
...	...	...

 

 

 

 

Date Parsing(formatted) from string in .NET

There are many functions in DateTime class in .NET to parse a string and get a date.

Usually DateTime.TryParse() tries to parse the date from string for a set of formats like 'mm-dd-yyyy', 'mm-dd-yy', or like 'dd-monthname-yyyy' but cannot parse from 'dd-mm-yyyy' (e.g. '15-10-2008').

To parse a string to a date in your own defined format use

DateTime.ParseExact();

For example to parse a date from a string like '15-10-2008' which is '15-Oct-2008'
where month and day can be double or single digit you can use-

string dateStr='15-10-2008';
DateTime parsedDate=DateTime.ParseExact(dateStr, "d-M-yyyy",

System.Globalization.CultureInfo.InvariantCulture);

SQL Server DateTime to and fro Varchar Conversion

I figured out solutions of another iritating problems anyone may fall behind and hence should know by reference:

General Syntax for convert function

convert(target_type, expression, style_code)

Now for convert(varchar, exp, code). As code is a number one may find himself dificult to remember the scode to real style mapping.

Here code mapping with meaning is:

The list of styles that can be used are:

Style ID	Style Type
0 or 100	mon dd yyyy hh:miAM (or PM)
101	mm/dd/yy
102 d	yy.mm.d
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
13 or 113	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
20 or 120	yyyy-mm-dd hh:mi:ss(24h)

Convert string(character data) to Datetime

These styles are the format of input to be used when converting character data into datetime and format of output while converting datetime data into characters:

for convert(datetime, exp, code):

0 or 100	Default. Equivalent to not specifying a style code.	mon dd yyyy hh:mmAM	Sep 8 2007 9:00PM
1	USA date.	mm/dd/yy	09/08/07
2	ANSI date.	yy.mm.dd	07/09/08
3	UK / French date.	dd/mm/yy	08/09/07
4	German date.	dd.mm.yy	08.09.07
5	Italian date.	dd-mm-yy	08-09-07
6	Abbreviated month.	dd mmm yy	08 Sep 07
7	Abbreviated month.	mmm dd, yy	Sep 08, 07
8 or 108	24 hour time.	HH:mm:ss	21:00:00
9 or 109	Default formatting with seconds and milliseconds appended.	mon dd yyyy hh:mm:ss:fffAM	Sep 8 2007 9:00:00:000PM
10	USA date with hyphen separators.	mm-dd-yy	09-08-07
11	Japanese date.	yy/mm/dd	07/09/08
12	ISO date.	yymmdd	070908
13 or 113	European default with seconds and milliseconds.	dd mon yyyy HH:mm:ss:fff	08 Sep 2007 21:00:00:000
14 or 114	24 hour time with milliseconds.	HH:mm:ss:fff	21:00:00:000
20 or 120	ODBC canonical date and time.	yyyy-mm-dd HH:mm:ss	2007-09-08 21:00:00
21 or 121	ODBC canonical date and time with milliseconds.	yyyy-mm-dd HH:mm:ss.fff	2007-09-08 21:00:00.000
101	USA date with century.	mm/dd/yyyy	09/08/2007
102	ANSI date with century.	yyyy.mm.dd	2007/09/08
103	UK / French date with century.	dd/mm/yyyy	08/09/2007
104	German date with century.	dd.mm.yyyy	08.09.2007
105	Italian date with century.	dd-mm-yyyy	08-09-2007
106	Abbreviated month with century.	dd mmm yyyy	08 Sep 2007
107	Abbreviated month with century.	mmm dd, yyyy	Sep 08, 2007
110	USA date with hyphen separators and century.	mm-dd-yyyy	09-08-2007
111	Japanese date with century.	yyyy/mm/dd	2007/09/08
112	ISO date with century.	yymmdd	20070908
126	ISO8601, for use in XML.	yyy-mm-ddThh:mm:ss	2007-09-08T21:00:00

ADO.NET: Copy Rows from DataTable to a new DataTable

Found one interesting thing in ADO.NET programming.

Wanted to copy some error free records from an existing table to a new table.

C# Code is:

(Assumes we have a table named tblExisting with some rows hasErrors is true)

DataTable tblErrorFree=new DataTabel();

foreach(DataRow r in tblExisting)
if(r.HasErrors)
tblErrorFree.ImportRow(r);

// Now the tblErrorFree datatable contains all the error free records of tblExisting datatable