Excel ODBC Driver for Windows Server 2008 (64 Bit)

By Tony - Last updated: Tuesday, June 10, 2008

So I’m running Windows Server 2008 as a development workstation. I ran into some trouble with a custom .NET application that imports data from Excel files using the ODBC driver.

Error Message in .NET

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

We are using this ODBC connection string (found on connectionsstrings.com):

Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;

After much research on the issue, it turns out the Excel ODBC drivers are 32 bit. So you need to change your Visual Studio platform to be x86 (to force 32bit) … mine was set to “Any CPU”

From what I can gather about the issue here, is that if your application is running in 64 bit mode, then when it creates an odbcConnection, that will also be running under 64 bit (makes sense so far) … so for any drivers that you have that are 32 bit they will not be accessible. Thus you get an error stating “data source name not found”

see this forum post about running sql server 2005 64 bit … it doesn’t say anything about Excel, but I think the principle applies

That’s it! Hope this helps.

For some light ;) reading on Windows Server 2008

Filed in .NET Programming

Small Simple Plug-In to Burn ISO’s in Windows Vista

By Tony - Last updated: Monday, June 2, 2008

For a very small simple and easy to use ISO burner for Windows Vista (and XP), check out ISO Recorder by Alex Feinman … It works like a Microsoft PowerToy. I love it!

Filed in Audio / Video, Showcase

HowTo Filter System.IO.Directory.GetFiles with Linq

By Tony - Last updated: Friday, May 30, 2008

Here we are going to see an easy way to enumerate files in a directory, filter the files with Linq, then bind the results to a ASP.NET repeater control. We will show you the old .NET 1x way of doing this, then the new and improved Linq way of doing this.

I had a method that would return a DataTable as results, then bind it to a ASP.NET Repeater control to display the file list of Exception Reports.

Old .NET 1.1 way of filtering a GetFiles list and returning as a bindable object

DataTable dtFiles = new DataTable();

dtFiles.Columns.Add("FullName", typeof(string));

dtFiles.Columns.Add("FileName", typeof(string));

 

foreach (string file in Directory.GetFiles(Server.MapPath("~/App_Data")))

{

    if (!file.Contains("ErrorReport"))

        continue;

    DataRow row = dtFiles.NewRow();

    row["FullName"] = file;

    row["FileName"] = System.IO.Path.GetFileName(file);

    dtFiles.Rows.Add(row);

}

 

this.repeaterFiles.DataSource = dtFiles;

this.repeaterFiles.DataBind();

 

Here is how to filter your GetFiles results with Linq

var fileList = from file in Directory.GetFiles(Server.MapPath("~/App_Data"))

               where file.Contains("ErrorReport")

               select new

               {

                   FullName = file,

                   FileName = System.IO.Path.GetFileName(file)

               };

this.repeaterFiles.DataSource = dtFiles;

this.repeaterFiles.DataBind();

You can see this is much cleaner, no need to create a DataTable with the results. By using the "select new" you are basically creating a collection with two properties (FullName, FileName) on the fly.

I found Pro LINQ to be an excellent read on the Linq subject.

Filed in .NET Programming, ASP.NET, Web • Tags:

Querying Selected Items From ListItemCollection Using Lambda Expressions

By Tony - Last updated: Tuesday, May 27, 2008

You can use Lambda expressions and the new extensions provided in the .NET framework 3.5 to filter your specialized collections like the ListItemCollection (used in ASP.NET ListBox controls).

I found this great usage for lambda this weekend when posed with a problem using a ListBox control with a SelectionMode of Multiple. If you’ve ever worked with the ListBox before (and I think you have), getting the single SelectedValue or SelectedIndex is a piece of cake. When you need to get all the selected items, well now you’re probably thinking about using a foreach loop to find the selected items, list this:

Two ‘old school’ solutions to get Selected items from a ListItemCollection

Simple foreach loop for finding Selected items in a ListItemCollection

List<ListItem> selectedItems = new List<ListItem>();
foreach (ListItem itm in lboxItems.Items)
{
    if (itm.Selected)
        selectedItems.Add(itm);
}

Or you’re thinking about using an Iterator pattern here, made easy with .NET framework 2.0 and the lovely generics it brought us:

More advanced Iterator patten for returning only selected items

private IEnumerable<ListItem> selectedItems(ListBox lbox)
{
    foreach (ListItem itm in lbox.Items)
    {
        if (itm.Selected)
            yield return itm;
    }            
}

Welcome to Lambda and .NET framework 3.5 !!!

Can you tell that I am overly excited about this?

So there is two more ways to do this:

How to get only the selected items from a ListBox control using Linq

var selectedItems = from li in lboxItems.Items.Cast<ListItem>()
    where li.Selected == true
    select li;

You see we have to first Cast the specialized ListItemCollection to a generic List<ListItem> collection before it can be used in a Linq query (for more info on linq this book really helped me on the subject). You may even want to finally encapsulate the (from …. select li) with parenthesis and then apply a .ToList(), that way it will be typed as a Generic List<ListItem> collection.

How to get only the selected items from a ListBox control using Lambda Expressions

List<ListItem> selectedItems =
    lboxItems.Items.Cast<ListItem>()
    .Where(itm => itm.Selected == true).ToList();

You see we also had to first Cast the specialized ListItemCollection to a generic List<ListItem> collection first to get the extensions (Where and ToList) … now in the Where method is the place that we finally see the lambda expression come to light, this basically is an easier way to express an anonymous delagate function, if you’ve never used one before, then feel lucky that your here now with the availablility of lambda!!!

 

You can download a working Lamda demo against ListItemCollection here

 

I highly recommend ASP.NET 3.5 Unleashed which talks a lot about Linq and Lamda expressions.

Filed in ASP.NET, Web • Tags:

SQLTRACE_BUFFER_FLUSH, Performance Metrics on SQL Server 2005

By Tony - Last updated: Monday, December 10, 2007

SQL Server 2005 gathers metrics data for you automatically out of the box! I discovered this bit of information today, as I was looking into why my DotNetNuke database was starting to drag down. (remember my previous post about How to reset a DNN password at the Database)

I started by looking at some of the hidden metrics data that SQL Server 2005 collects out of the box for you. The first thing to look at was to run a query about the causes of the wait times, to do this execute the following query.

select top 10
wait_type "Wait Type",
wait_time_ms / 1000 "Wait time (s)",
Convert(Decimal(12,2), wait_time_ms * 100.0
/ Sum(wait_time_ms) over ()) "% Waiting"
from sys.dm_os_wait_stats
where wait_type not like ‘%SLEEP%’
order by wait_time_ms desc

This query did not tell me a very much, except that SQLTRACE_BUFFER_FLUSH was at the top of the scale with 99.92% of the wait time.

So I moved on to a more complex query to show a list of missing indexes. This is an interesting query, and it proved to be the one that fixed the slowness problems on my DotNetNuke database.

Here is the query I used to show the cost of missing Indexes

select top 10
round(avg_total_user_cost * avg_user_impact
* (user_seeks + user_scans), 0) "Total Cost",
avg_user_impact, statement "Table Name",
equality_columns "Equality Columns",
inequality_columns "Inequality Columns",
included_columns "Include Columns"
from sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats s
on s.group_handle = g.index_group_handle
join sys.dm_db_missing_index_details d
on d.index_handle = g.index_handle
order by "Total Cost" desc;

Read this for an excellent book on SQL Server 2005 Performance Tuning For more information on Performance Tuning Another great article on the impact of SQLTRACE_BUFFER_FLUSH is found Here

Filed in Databases, SQL

.NET 2.0 Generic Collections to 1.0 and 1.1 Collections

By Tony - Last updated: Monday, October 22, 2007

I am always looking around for the Generic equivalents for the non-generic collections. Especially when converting 1.1 code over to 2.0

Table of Generic Collections to Non-Generic Collections
Non-Generic (1.x) Generic Replacements (2.0+)
ArrayList List<T>
CollectionBase Collection<T>
Comparer Comparer<T>
CompatibleComparer Comparer<T>
DictionaryBase KeyedCollection<TKey,TItem>
Hashtable Dictionary<TKey,TValue>
ICollection ICollection<T>
IDictionary IDictionary<TKey,TValue>
IList IList<T>
Queue Queue<T>
ReadOnlyCollectionBase ReadOnlyCollection<T>
SortedList SortedList<TKey, TValue>
Stack Stack<T>
   

 

Performance

List<T> : a List<T> uses an underlying array that maintains order, thus inserting and removing from this object can take a variable amount of time.

Dictionary<TKey,TValue> : a Dictionary<TKey,TValue> is generally average with add, remove, and contains, even when it contains a large set of data.

SortedDictionary<Tkey,TValue> : SortedDictionary<Tkey,TValue> uses a balanced tree as the underlying implementation. This can provide you with fast lookups, but inserting will more than likely be slower.

SortedList<TKey,TValue> : SortedList<TKey,TValue> uses two separate arrays to store the data, both in order.

Filed in .NET Programming

Streaming Video From Home to Work

By Tony - Last updated: Tuesday, September 18, 2007

Have you ever wanted to stream some video from your house to a remote location. Like to your cell phone, or to your computer at work? Maybe you are scheduled to work late during the World Cup, or NBA Finals. Well here is a simple way to stream that video/audio using VLC (VideoLAN media player).

Requirements:

The source PC:

VLC Source Settings

You will want to monkey with the Transcoding options if your client is not VLC, like if its Media Player, or QuickTime (if its QuickTime you may need to change the Encapsulation method to something else, like MP4 (maybe) )

Firewall Settings

The client PC

Tweaking the Settings

If your video is getting jumpy

Its likely that your stream is too large for your upstream to handle.

It can be useful to watch the Stream and Media Info window (on the client VLC) and the Statistics tab, to monitor the Input bitrate … if you see that bitrate stay above your available upstream too often, then adjusting the source bitrate should fix this problem.

Extra Information

Listen to your tunes and movies at work!

Filed in Audio / Video

Easy way to kill MS SQL server transaction logs

By Tony - Last updated: Sunday, August 19, 2007

I manage many SQL 2000 databases. And many times while performing maintenance I have run into the ‘transaction log full’ error.

like this:
Error: 9002, Severity: 17, State: 2
The log file for database ‘%.*ls’ is full.

So like many people I resort to using the commands
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE

The hassle with this, is that you need to first find out the logical name from sysfiles, or from enterprise manager to be able to use the DBCC SHRINKFILE command

So I wrote a stored procedure that you can install into each of your databases, it will enumerate through the sysfiles table. And perform the above steps against all of the Log files found for the current database. The nice thing is that this works with databases with more than one log file defined.

create procedure proc_killLog
(
 @size int = 2
)
as
begin
 declare @logFile sysname, @db sysname
 select @db = db_name()
 
 declare curFiles cursor for
 select rtrim([name]) from sysfiles where groupid = 0
 open curFiles
 fetch next from curFiles
 into @logFile
 while @@FETCH_STATUS = 0
 begin
 BACKUP LOG @db WITH TRUNCATE_ONLY
 DBCC SHRINKFILE(@logFile, @size)
 fetch next from curFiles
 into @logFile
 end
 
 close curFiles
 deallocate curFiles
end

This procedure should work with SQL 7, as well as SQL 2005 databases

Filed in Databases, SQL

ASP.NET What Server Side controls require a Server Form

By Tony - Last updated: Thursday, June 7, 2007

This is a list that I have compiled of ASP server side controls that require or do not require to be nested inside of a server form (form runat=server)

Requires Does not Require
  asp:Label
  asp:HyperLink
asp:TextBox  
asp:Button  
asp:LinkButton  
asp:ImageButton  
asp:DropDownList  
asp:ListBox  
asp:CheckBox  
asp:CheckBoxList  
  asp:Image
  asp:ImageMap
  asp:Table
asp:BulletedList  
asp:HiddenField  
  asp:Literal
asp:Calendar  
  asp:AdRotator
asp:FileUpload  
asp:Wizard  
  asp:Xml
  asp:MultiView
  asp:View
  asp:Panel
  asp:PlaceHolder
  asp:Substitution
  asp:Localize
asp:GridView  
  asp:DataList
asp:DetailsView  
asp:FormView  
  asp:Repeater
  asp:XmlDataSource
asp:Menu  
asp:TreeView  
Filed in ASP.NET, Web

HOWTO obtain the ClickOnce Version at runtime

By Tony - Last updated: Monday, May 7, 2007

This article will show you how to get the ClickOnce Version at runtime of you .NET application. My examples are in VB.NET and C# but the same technique would work for any other .NET Assembly.

First you need to add a project reference to System.Deployment

Next import the namespace into your class

VB.NET
Imports System.Deployment
C#
using System.Deployment;

You can obtain the Current Version from the ApplicationDeployment.CurrentDeployment.CurrentVersion property. This returns a System.Version object.

Note (from MSDN): CurrentVersion will differ from UpdatedVersion if a new update has been installed but you have not yet called Restart. If the deployment manifest is configured to perform automatic updates, you can compare these two values to determine if you should restart the application.

Retrieve the ClickOnce Version from the CurrentVersion property

NOTE: The CurrentDeployment static property is only valid when the application has been deployed with ClickOnce. Therefore before you access this property, you should check the ApplicationDeployment.IsNetworkDeployed property first, it will always return a false in the debug environment.

VB.NET
Dim myVersion as Version
if (ApplicationDeployment.IsNetworkDeployed) then
   myVersion = ApplicationDeployment.CurrentDeployment.CurrentVersion
end if
C#
Version myVersion;
if (ApplicationDeployment.IsNetworkDeployed)
   myVersion = ApplicationDeployment.CurrentDeployment.CurrentVersion;

Using the Version object

From here you can use the version information in a label, say on an about form in this way.

VB.NET
label1.Text = string.Format("ClickOnce published Version: v{0}.{1}.{2}.{3}", myVersion.Major, myVersion.Minor, myVersion.Build, myVersion.Revision)
C#
label1.Text = string.Format("ClickOnce published Version: v{0}.{1}.{2}.{3}", myVersion.Major, myVersion.Minor, myVersion.Build, myVersion.Revision);
Filed in .NET Programming