When I got my $300 hosting bill today – it was a last straw. $120 a year for 100Mb in a single MySQL database, nessessity of a dedicated IP address simple so I could use subdomains (another $60), a gig of space and 80 gig monthly transfer for another $120 – thank you, but no, thank you.
Goodby DiscountASP, hello BlueHost.
I was burning a 4 gig DVD-R and stepped away for a while. When I came back I found that Vista on my laptop nonchalantly went into a sleep mode, not really caring that the burning was only half-done. I think this is the end of my Vista experiments. Next stop – Windows 7 (hopefully it won’t bring the house down).
Often there is a need to perform group by operations on in-memory .NET data tables. You can’t run a standard SQL statement and LINQ is not available prior version 3.0 of .NET. To compensate for this shortcoming I put together a small function that works in .NET 2.0. As input parameters it accepts source DataTable, column name to group by and a column name to perform aggregate operation on. It returns a grouped data as data table:
Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable
Dim dv As New DataView(i_dSourceTable)
'getting distinct values for group column
Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn})
'adding column for the row count
dtGroup.Columns.Add("Count", GetType(Integer))
'looping thru distinct values for the group, counting
For Each dr As DataRow In dtGroup.Rows
dr("Count") = i_dSourceTable.Compute("Count(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")
Next
'returning grouped/counted result
Return dtGroup
End Function
The function first gets distinct values for group-by column, by creating a data view from source data table and using DataView’s “ToTable” method. It then loops thru these distinct values performing aggregate function on the source table using DataTable’s “Compute” method – Count in this case, but it can easily be replaced with other aggregates.
IsNumeric(data) is a widely used function in SQL Server programming. If passed in data is a number – it returns 1, otherwise 0. Supposedly. There’re several cases when it returns 1 even though the data is not numeric. Some of those cases are: dollar sign “$”, minus sign “-“, space, tab. If you try to call Cast or Convert function on the data after that to convert data to number – they will throw an error
An excellent article at SQL Hacks suggest adding “e0” to the data when passing it to IsNumeric function. What it does essentualy is making the number to be in scientific format, e.g 12.34 becomes 12.34e0 (or 12.34 * 1). Which still remains a number. So IsNumeric(12.34e0) returns 1 while IsNumeric($e0) returns 0.
Which works great until actual number in scientific format is passed. If you try to test for example 1.234e5, IsNumeric correctly returns 1, but by adding “e0” as article suggest IsNumeric(1.2345e5e0) returns 0.
To work around this limitation I simple test for an “e” within passed data. If it exist, I am calling IsNumeric as usual, if it does not – I am adding “e0” to the tested data:
CREATE FUNCTION IsRealNumber (@i_sValueToTest nvarchar(250))
/* This function tests if passed in value a real number */
RETURNS int
AS
BEGIN
DECLARE @iResult int
— if there is ‘E’ in the value – do a standard IsNumeric test
IF CHARINDEX(‘E’, UPPER(@i_sValueToTest))<> 0
SET @iResult = IsNumeric(@i_sValueToTest)
— Otherwise do a modified IsNumeric test by adding ‘E0’ at the end
ELSE
SET @iResult = IsNumeric(@i_sValueToTest + ‘E0’)
RETURN @iResult
END
If you use ASP.NET FileUpload control or <INPUT TYPE=”FILE” HTML> control in Internet Explorer on Windows XP SP2 or later, trying to enter file path manually may cause “htmlfile: Access is denied error” error if entered string is not well formed local or UNC path.
The reason is that the control in Internet Explorer after version 6 in Windows XP SP2 does not allow relative path and if the form is submitted via Javascript form.submit method (like for example a __doPostback() call in ASP.NET) you will get untrappable error from above, if the form is submitted by an <INPUT TYPE=”SUBMIT> button you won’t get any notice at all.
The way to work around this issue is to catch invalid file name prior form being sumbitted. Add onclick event to your submitting button (OnClientClick if this is ASP.NET server side button) and add following code to it:
onclick=”if (!FilePathIsValid()) return false;”
then add following JavaScript function to your code:
function FilePathIsValid() {
var reg = /^(([a-zA-Z]:)|(\))(\{1}|((\{1})[^\]([^/:<>”|]*))+)$/g
if (!reg.test(document.getElementById(‘xfuFile’).value)) {
alert(‘Please enter valid file path’);
return false
} else
return true
}
where ‘xfuFile’ is the name of your FileUpload control. The function uses Jens K. Suessmeyer’s RegEx Expression to test if the file path is well formed. If it’s not – user gets error message and the form is not submitted.