Jan 26, 2007

Page records in SQL server

http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html - How do I page through a recordset?

http://databases.aspfaq.com/database/how-do-i-handle-alphabetic-paging.html ---- How do I handle alphabetic paging?

http://msdn2.microsoft.com/en-us/library/ms979197.aspx --- Microsoft Solution

http://www.sqlteam.com/item.asp?ItemID=26455 - Server Side Paging using SQL Server 2005
http://www.aspfaq.com/show.asp?id=2120. --- Good site on SQL and this link explains SQL paging

SQL 2005 books online :: http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

SQL 2000 books online:::: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

kick it on DotNetKicks.com

Jan 21, 2007

Error When Installing Visual Studio 2005 SP1: "The installation source for this product is not available.

Some Googling turned up a fix for a very common error message when installing SP1 - “Error 1718. File FileName was rejected by digital signature policy” - but it took a bit more searching before I found this bug report in which user rlasker provided the following helpful comment:

I had this issue. When I looked in the event log I found this:

Event Type: Error
Event Source: MsiInstaller
Event Category: None
Event ID: 1008
Date: 12/18/2006
Time: 12:18:31 PM

The installation of C:\DOCUME~1\...\LOCALS~1\Temp\ZNWA0\VS80sp1-KB926601-X86-ENU.msp is not permitted due to an error in software restriction policy processing. The object cannot be trusted.

I followed the instructions at this link:

And it no longer gave me the error descibed above.

Looking in my Event Log I found the same details and dutifully applied the workaround described at the KB (http://support.microsoft.com/default.aspx/kb/925336). These steps remedied the problem, and after rebooting I was able to successfully install SP1. Hopefully this post makes it to the first page of Google's results so future developers running into the same problem who are Googling the error message can more quickly find a fix.

For more VS 2005 SP1 info, see:

kick it on DotNetKicks.com

Handling Errors in an ASP.NET Web Application

  • Using custom, human-friendly error pages (avoid the Yellow Screen of Death!)
  • Techniques for automatically logging unhandled exceptions
  • Ways to automatically notify developers in the face of an unhandled exception
  • How to handle exceptions that arise from the depths of ASP.NET 2.0's data source controls. That is, we'll see how to handle exceptions that occur when you are working with data in an ASP.NET 2.0 page using the SqlDataSource control and a GridView and an exception is raised from the database layer.

If you can't make the talk, you can download the PowerPoint slides and code demos here. Hope to see you tomorrow!

kick it on DotNetKicks.com

Jan 19, 2007

Data Driven Query Task in DTS for updating data from Excel into SQL Server table

This is a very common issue we face.

There is a Excel sheet with data that needs to be imported into SQL Server table. The SQL Server table has unique key/primary key constraints on a key column (e.g. InspectorID). However the Excel spreadsheet can contain duplicates since this either being generated manually or coming from a different data source (typically a data warehouse). To be able to create a DTS script that uploads data from the Excel spreadsheet into Inspector table :

I divided the Inspector table upload process into 2 steps. In the first step I inserted all the distinct InspectorID’s from Excel spreadsheet into the Inspector Table using the TransformData Task step in DTS, and for other columns that did not accept nulls, I used default values for these fields in the insert statement.

After this I used a Data driven query Task to update the remaining fields of the Inspector table from the spreadsheet. Working with this task was quite tricky, since at first glance it would appear as if you are updating the Inspector table with the fields from the Inspector table itself. However, once you create a one-one mapping between the fields in the Excel spreadsheet and the Inspector table using the ActiveX Script in Transformations tab and set the query type to DTSTransformstat_UpdateQuery instead of DTSTransformstat_InsertQuery, it automatically behind the scenes maps the source (Excel) fields to the destination (Inspector table) fields.

FYI, the various options available in the Transformations tab include: ActiveX Script, Copy Column, DateTime String, Lowercase String etc..

First in the Source you need to select the Connection1 (Excel) and then choose the Excel Spreadsheet from where the data is retrieved.

Second in the Bindings tab select the Inspector table from Connection2 ( SQL Server connection)

Now if the column in the Excel Spreadsheet does not Map exactly with the column in Inspector table, then you can make use of the Lookups tab in either the Transform Data Task or the Data Driven Query Task.

Thus you can create a lookup query such as : select InspectorTypeID from InspectorType where Type = ?. If this Lookup was named TypeLookup.

Then you will need to modify the ActiveX Script in Transformations tab as follows:

Function Main()
DTSDestination("InspectorID") = DTSSource("ID")
DTSDestination("InspectorName") = DTSSource("Name")
DTSDestination("InspectorTypeID") = DTSLookups("TypeLookup").Execute(DTSSource("InspectorType"))
Main = DTSTransformstat_UpdateQuery
End Function

If it is a TransformData Task then the Function Main should be set to the value :

Then in the Queries Tab, select Update query and use the following query to update Inspector table:
update Inspector
set InspectorName = ?, InspectorType = ?
where InspectorID = ?

Then click on Parse/Show Parameters then Map Parameter1 to InspectorName, Parameter2 to InspectorType and Parameter3 to InspectorID.

Note that the dropdown for Parameters displays the columns from the Inspector table and not from the Excel spreadsheet. Thus you may wonder how would we get the values from the Excel spreadsheet to update the Inspector table. The trick is that since we have one-one mapping between the columns in the Excel spreadsheet and the Inspector table as we defined in the ActiveX Script in the Transformations tab, thus we get the values from the excel spreadsheet behind the scenes.

Thus this task updates the InspectorName and InspectorType number fields from the Excel spread sheet to Inspector table for all the unique/distinct InspectorID’s that were inserted in the previous “Transform Data Task” step.

kick it on DotNetKicks.com