When people who know SQL use Excel

So I had some urgent data import to do, and DTS wasn't playing fair...
I have the following csv format:

contactid, telephone1, telephone2, mobilephone
GUID, number, number, number
GUID, number, number, number
GUID, number, number, number

and I needed it in a database... so sure, I could have got DTS working, or I could have used BCP to put the data in (though the biggest set is only 3000 lines)... or I could have written a dot net program to load the csv and import it to a table using a sqlbulkcopy (which i've done before for significantly bigger files) but..

Well, i'm ashamed to say, log growth and performance issues aside, that I did this:

=CONCATENATE("create table [",MID(CELL("filename"),59,LEN(CELL("filename"))-59+1),"] (id int identity(1,1) primary key, contactid uniqueidentifier, ",$B$1," varchar(1000), ",$C$1," varchar(1000), ",$D$1," varchar(1000))")

=SUBSTITUTE(CONCATENATE("insert into [",MID(CELL("filename"),59,LEN(CELL("filename"))-59+1),"] (contactid,",$B$1,",", $C$1, ",", $D$1,") values ('",A2,"','",SUBSTITUTE(B2,"'","''"),"','",SUBSTITUTE(C2,"'","''"),"','",SUBSTITUTE(D2,"'","''"),"')"),"'NULL'","NULL")

It's like I said... when people who know SQL use Excel.

prototype.js & Date.dateAdd / Date.dateDiff / Date.iso8601string

If you ever need dateAdd and dateDiff in javascript...
also included is a method to get the 2007-11-22T11:42:00.000 string for a date.

This is what I use.
This requires prototype.js

I'm pretty sure I didn't write the dateDiff/dateAdd methods, i'm just going through some of my source code and I was slack and didn't add references.
If you own this code, let me know and i'll add the appropriate references.

iso8601string

Date.prototype.iso8601string = function()
{
var out;
out = this.getYear() + '-' + this.getMonth().toPaddedString(2) + '-' + this.getDate().toPaddedString(2) + 'T' + this.getHours().toPaddedString(2) + ':' + this.getMinutes().toPaddedString(2) + ':00.000'
return out;
}

dateDiff
if you've ever used vbscript, or vb you should be familiar with this method. It gets the difference in units between two dates.

Date.prototype.dateDiff = function (ret,edDate) {
var start = this;
var end = edDate;
ret = ret.toLowerCase();
if(ret=='d') {
return Math.ceil((end.getTime() - start.getTime()) / (24*60*60*1000));
}
else if(ret=='h') {
return Math.ceil((end.getTime() - start.getTime()) / (60*60*1000));
}
else if(ret=='n') {
return Math.ceil((end.getTime() - start.getTime()) / (60*1000));
} else if(ret=='s') {
return Math.ceil((end.getTime() - start.getTime()) / 1000);
} else {
return Math.ceil(end.getTime() - start.getTime());
}
}

dateDiff
if you've ever used vbscript, or vb you should be familiar with this method. It adds a unit to a date, eg: the current date, plus a month.

Date.prototype.dateAdd = function(p_Interval, p_Number){
var thing = new String();
p_Interval = p_Interval.toLowerCase();
if(isNaN(p_Number)){
throw "The second parameter must be a number. \n You passed: " + p_Number;
return false;
}
p_Number = new Number(p_Number);
switch(p_Interval.toLowerCase()){
case "yyyy": {
this.setFullYear(this.getFullYear() + p_Number);
break;
}
case "q": {
this.setMonth(this.getMonth() + (p_Number*3));
break;
}
case "m": {
this.setMonth(this.getMonth() + p_Number);
break;
}
case "y":
case "d":
case "w": {
this.setDate(this.getDate() + p_Number);
break;
}
case "ww": {
this.setDate(this.getDate() + (p_Number*7));
break;
}
case "h": {
this.setHours(this.getHours() + p_Number);
break;
}
case "n": {
this.setMinutes(this.getMinutes() + p_Number);
break;
}
case "s": {
this.setSeconds(this.getSeconds() + p_Number);
break;
}
case "ms": {
this.setMilliseconds(this.getMilliseconds() + p_Number);
break;
}
default: {
throw "The first parameter must be a string from this list: \n" +
"yyyy, q, m, y, d, w, ww, h, n, s, or ms. You passed: " + p_Interval;
return false;
}
}
return this;
}

prototype.js & text caret/selection methods

I have an addiction, it's to the syntactic sugar of prototype.js, the excellent ruby-syntax style (i've never even looked at ruby) javascript library that's now up to version 1.6.

In a recent project (where I wrote some keypress input filter code, to filter to numeric inputs etc) I found that I needed to get the selected text in an input, so that I knew I didn't have to Event.stop the keypress as it would overwrite the selection anyway.
In another area in the same control library, I needed to get/set the text caret position, to allow me to detect when the caret was getting close to a maximum input length and 'jump' to the next box.
I found it took quite a bit of searching to locate the correct cross-browser methods of doing these tasks.

I've implemented these methods as standard javascript functions instead of element methods via prototype, though it's on my todo list to do this. They'd be better suited to be on the element themselves.

Without further ado, the code.
Get the selection in a text field
You must pass the element to it, in some browsers, it is not input-dependant, ie: will get the document selection as well.

function getSel(oField)
{
var txt = '';
var foundIn = '';
if (oField.selectionStart)
{
txt = oField.value.substring(oField.selectionStart,oField.selectionEnd);
}
else if (window.getSelection)
{
txt = window.getSelection();
foundIn = 'window.getSelection()';
}
else if (document.getSelection)
{
txt = document.getSelection();
foundIn = 'document.getSelection()';
}
else if (document.selection)
{
txt = document.selection.createRange().text;
foundIn = 'document.selection.createRange()';
}
else
{
return;
}
return txt;
}

Get Caret Pos (for a field)
Gets the caret position within a text input.

function getCaretPos(oField)
{
var iCaretPos = 0;
if (Prototype.Browser.IE)
{
var oSel = document.selection.createRange ();
oSel.moveStart ('character', -oField.value.length);
iCaretPos = oSel.text.length;
}else{
iCaretPos = oField.selectionEnd;
}
return iCaretPos;
}

Set Caret Pos (for a field)
Sets the caret position within a text input.

function setCaretPos(oField, iCaretPos)
{
if (Prototype.Browser.IE)
{
var oSel = document.selection.createRange ();
oSel.moveStart ('character', -oField.value.length);
oSel.moveStart ('character', iCaretPos);
oSel.moveEnd ('character', 0);
oSel.select ();
}else{
oField.selectionStart = iCaretPos;
oField.selectionEnd = iCaretPos;
oField.focus();
}
}

Webbrowser Focus Woes

Okay so for a client i'm working on a compact framework application.
Early on, I realised that trying to render user-created content in datagrids just wasn't going to work.
The main reason for this was that long text wouldn't wrap vertically and expand the rows, like you'd expect it to (at least from a web background).
Further to this, when you had a layout such as

Label Value
Label Value

If the value was long, you could set AutoGrow and it would grow horizontally but not vertically.
I tried to get around this with a horrible horrible iterative function which looped over the controls on the form and tried to resize them to hold their content and move other controls down the form like it should.

None of this worked.
In a moment of desperation, I dropped a webbrowser control on the form and found I could set documentText of it to HTML. voila! Layout problems solved..

This had a couple of challenges though, which is the reason for this post.

1) Navigation. In order to capture input, ie links, submit buttons etc.. you typically need a server side page. Plain html will not form-submit to itself, well not that I could find and you certainly can't href unless you use # anchor tags.
I used a blank html document (just html and body tags) to facilitate linking. Links navigate to this blank html document ?id=xx and then I parse the querystring.

2) Focus.
This is the real reason for this post.
Focusing web browsers is poorly implemented at best.

Because the webbrowser control grabs focus to allow document navigation, things dont work too well. I discovered that when the webbrowser takes up the whole form, this isn't an issue but if you have a smaller webbrowser and say, a search box, then its very difficult to control the focus.
If you are using the activated event on a form to add data etc, on the second visit to the form, the focus is still on the webbrowser.
Textbox1.focus() does nothing.
WebBrowser1.focused = false

The solution to this problem is WebBrowser1.Enabled = false.
Disable the web browser. Disabled controls cant have focus.
Just remember to renable it when you want the user to be able to swich back to it.
WebBrowser1.Enabled is not in intellisense though, but it does work, at least on the emulator. I have to try the actual devices yet.
If you combine this with Form1.Keypreview = true and a Form1_keydown handler, you can also use a button on the phones keypad to jump the focus back out of the webbrowser by disabling it.

I hope this helps someone else, I searched for days to find a solution to this focusing stuff.

Wow, so my last post had a glaring logic error.
It seems I forgot that the dateadd needed the conversion figure, r, not the modified version, c + r.
I've cleaned it up and added another function here so that I can still get the modified component with a function call.

Private Shared Function GetClosest15(ByVal t As Date)
Dim c As Int32 =
RoundTo15GetPlusMinusComponent(t.Minute)
Return DateAdd(DateInterval.Second,
-t.Second, DateAdd(DateInterval.Minute, c, t))
End Function

Private Shared
Function RoundTo15GetPlusMinusComponent(ByVal c As Int32)
Dim r As Int32 = c
Mod 15
If r <= 7 Then r *= -1 Else r = 15 - r
Return r
End
Function

Private Shared Function RoundTo15(ByVal c As Int32)
Dim r As
Int32 = RoundTo15GetPlusMinusComponent(c)
Return c + r
End Function

Rounding Time

One of the things that bothered me recently was trying to find the closest 15 minute time interval of a datetime.

I went through a few different scenarios before actually writing any code, but this one really did my head in.
For such as 'simple' operation, I found it difficult to identify the logic behind what I was trying to calculate.

Why would you want to do this I hear you ask?
Because sometimes, such as in this particular case (a timesheet entry) it is appropriate to round user input to the nearest component, to keep figures nice and tidy.

This code is in vb.net but it should be a fairly easy conversion.
If anyone can improve on it without using string manipulation, feel free to comment.

It's broken into two functions because another textbox on the same form accepts only minutes and needs to perform the same rounding.

Private Function GetClosest15(ByVal t As
Date)
Dim c As Int32 = RoundTo15(t.Minute)
Return
DateAdd(DateInterval.Second, -t.Second,
DateAdd(DateInterval.Minute, c, t))
End Function

Private Function RoundTo15(ByVal c As
Int32)
Dim r As Int32 = c Mod 15
If r <= 7 Then r *= -1
Else r = 15 - r
Return c + r
End Function

regex

Well, i'm slowly learning more regex- usually when I need to accomplish a specific task, in this case, renaming a bunch of files in a folder to match a single naming scheme.

I used the following

([a-zA-Z3.]{1,})[.]([s]{1}[0-9]{1,2}[e]{1}[0-9]{1,2}[0-9]{3,4})

which matches a name, separated by dots, (the 3 is for a specific case) until a . followed by xxx or SxxExx (where x is a number)
etc.

The groups are extracted using

foreach (Match m in regex.Matches(inputString)
{
string Group1 = Match.Groups[1].value;
string Group2 = Match.Groups[2].value;
}

Only the group2 is required for the rename operation, but the group1 is used elsewhere in the program

Note the gotcha with the indexing on the groups, it's 0 based but 0 appears to be the entire string vs the actual group-match.

Welcome

Welcome to this new blog... it's planned to be a kind-of technical blog, where i'll post all sorts of bits and pieces as I encounter them - issues and the like.
Just like all the others out there..