DevLog

Several times, when I attach an existing database to SQL Server Express 2005, I get my database attached as read-only. So no way to add any object to it or update it!

The solution is to run this line:

Alter database <dbname> set read_write

As example would be: Alter database Northwind set read_write

This will make sure your database is now enabled for read/write!

 

Hope this helps,
Regards

Tags: Databases &amp; SQL

A new Event in ADO.NET 2.0 has been addeed to the SqlCommand object called the *StatementCompleted* event.
Usually, if you are executing a set of UPDATE statements on your database as a batch using ExecuteNoneQuery() method, you get the number of affected rows in the whole batch action. What if you wanted to know what each UPDATE method has returned from *Rows Affected*?

The answer is this new Event. Check it on  MSDN to see how powerful it is! SqlCommand.StatementCompleted Event

Hope this helps,
Regards

Tags: Databases &amp; SQL

Another tip from the great book by David Sceppa is how to handle null column values:

Suppose you have a column in your table in the Database, and that column can have NULL values, the column is named ColA. Now you wanted to updated a record by checking if ColA is NULL in the WHERE clause to allow updating:

UPDATE ..... WHERE ColA = ?

The above won't work if the value of ColA was NULL and the value of the parameter is NULL, instead you should use:

 "ColA IS NULL"

However, to write a SQL Stored Procedure that allows columns with allowed NULL values to accept values either NULL or something else, you should change the above statement to something as:

UPDATE ... WHERE (ColA = ? OR ((ColA IS NULL) AND (? IS NULL)))

This means, we want the above statement to be true when ColA equals the value of "?" or when ColA is  NULL and the ? is also NULL. Why is that?

Suppose you want to update a record where ColA == 5, therefore, the above statement will be true, the query will search for the record whose ColA value is 5. This is fine!

Now suppose, you want to update a record where ColA is null, so you provide NULL for the value of ?, then the query will search for the record whose ColA is null, the first part of the WHERE will fail, but the second part will succeed, since we are ORing, then the conditions will pass!

Suppose, you want to update a record whose ColA == 6, and it happens that a record in the table has the value of ColA as NULL, and then you execute the query, it will also succeed in not updating the record whose ColA is null, because we have added the second ANDing condition, (? IS NULL). So, if you wanted to update a record whose ColA value is 6, and while running the query you passed through a record whose ColA is NULL, it won't be updated, because we have to check too, if the ? is NULL, if not NULL, then the update will not work, and hence the query works great, because in the second condition, only the first left side wins, but the second right side it fails, because the value im searching for which is 6 is not NULL.

Hope this helps!

Regards

Tags: ASP.NET 2.0 - General, Databases &amp; SQL

Have you ever seen this error before? It happened with me today while I opened a table on SQL Server 2000 and tried to update a cell.