Returning random rows in a result set

Using the NEWID() function to return random rows

The NEWID function will return a GUID value, that is statistically guaranteed to be unique e.g.

If you repeatedly call this function it will return a unique GUID. You can utilise this feature to give each row a unique random value. I’ve encountered a number of occaisions where it is desiranle to return from a resultset, a random number of rows. In the past I would achieve this by returning the result set to some application code that would randomise the results and select at most N items.

The example below shows how we can return 5 random rows from the Cutsomers table (Northwind database).

Selecting 5 Random rows

Each time the SQL snippet is run, 5 random customers are returned from the table.

This is a very neat and efficient.

Object reference not set to an instance of an object. (SQLEditors)

SQL Server 2005, SQL Server Management Studio (SSMS)

When attempting to modify a table in the SSMS (right-click on the table, click “Modify table” option) I receved the error, “Object reference not set to an instance of an object. (SQLEditors)” .

This error appears to be a problem with the registry installation for the WorkStation/Client Components. I managed to repair this by removing and then reinstalling the ‘WorkStation Components’ via the Control Pannel | Add, Remove Programs.

I suspect, but cannot be sure, that this problem may have arisen due to a install of SQL Server Express after I had already installed SQL Server 2005.

The steps I followed are as follows;

You will need the SQL Server 2005 install disk to remove/install components. Also, ensure you close Visual Studio and SQL Server Management Studio if they are open.

1. Control Panel | Add, Remove Programs.
2. Select SQL Server 2005, Click the Change button.
3. On the Component Selection dialog, select ‘Workstation Components’.
4. Click the next button on the Wizard, until you come to the Change or Remove Instance dialog.
5. Select ‘Changed Installed Components’.
6. On the Client Components option, right-click and select ‘Entire feature will be unavailble’.
7. Click the Next button and then click the Install Button.
8. The components will now be removed, this will take some time.
9. After the components have been removed, Repeat Steps 1 to 3 and click the next button on the Server Installation wizard to start the System Configuration Check dialog.
10. You may have to reboot your machine, inspect the Actions for a Pending Reboot Required Message. Reboot if required.
11. Click the next button on the Wizard, until you come to the Change or Remove Instance dialog.
12. Select ‘Changed Installed Components’.
13. On the Client Components option, select ‘Entire feature will be installed on local hard drive’.
14. Click the Next button and then click the Install Button.
15. The Workstation Components will now be installed. This may take some time.
16. Installation should now be complete.

How To Set Default Focus To a Server Control

ASP.NET 2.0

You can easily set the focus to a specific control when your web form is loaded, by adding the line

Page.SetFocus(<ControlID>);

to your Page_Load() event.

Must declare the scalar variable “@ID”

GridView  – Auto Generated SQL Insert, Update And Delete Statements

ASP.NET 2.0 with SQL Server 2005

I recently ran into the error ‘Must declare the scalar variable “@ID” ‘ when attempting to delete rows from a GridView.  I had created the GridView via the smart tag and wizard options, so for a while I was stumped as to what the was the cause of the problem. I felt I knew what the error message was indicating, but could not figure out why it was occurring. A little Googling confirmed my suspicion that the parameter @ID was not being set in the SQL generated by the SQLDataSource but none of the posts I read revealed how I might fix it.

Because I had built the page using the various wizards I thought there was nothing further for me to do, but after a little further digging I discovered I had not set the DataKeyNames property for the GridView. Without this property set, the GridView cannot automatically wire up the parameter values and thus the generated SQL queries fail.

Double check the DataKeyNames property should you be experiencing this problem.

Depending how you have created the GridView and SQLDataSource, you may have to manually set the parameters in response to an SQLDataSource Inserting, Updating or Deleting event.