Category: SQL Server 2000

.Net, ADO.NET, ASP.NET, C#, Database, Programming, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 7, VB.NET, Web Development, WebForms

ASP.NET – Handling Null Return Values from the Database using IsDBNull

Justin Cooney
ASP.NET
ASP.NET

If you are retrieving or adding values from and to your database then you will know the difficulties that can be caused by NULL values. The notorious InvalidCastException is one example of receiving a NULL value from your database into an ASP.NET object that doesn’t know what to do with database NULL values.

Here’s typical example of an InvalidCastException error caused by setting a String variable to a value that is returned as a NULL from your database query:

System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToString(Object Value) at ASP.myPageName_aspx.Page_Load(Object sender, EventArgs e) in D:\Inetpub\wwwroot\MySite\myPageName.aspx:line 1xx

If you find this article useful, have a look at some of the other ASP.NET tips and tricks articles I’ve published. Continue reading “ASP.NET – Handling Null Return Values from the Database using IsDBNull” →

Advertisement
Database, Programming, SQL, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Tips, Transact-SQL, tSQL

T-SQL – Replace Multiple Extra Whitespaces in a String with One Whitespace

Justin Cooney
MS SQL Server
MS SQL Server

In this article I will review three easy methods that can be used to replace extra spaces between words in a string. Note that if you want to remove extra spaces from the start and end of your string you will still need to encapsulate the output of the routines within the standard LTrim / RTrim functions.

Each of these methods works quite nicely and can either be written directly into your code, or can be added as a user-defined function. I have tested these functions in SQL Server 2000 and SQL Server 2008 and they work very well in either of these versions of SQL Server.

Please have a look at some of the other aricles I have written covering SQL Server. Continue reading “T-SQL – Replace Multiple Extra Whitespaces in a String with One Whitespace” →

Database, Programming, SQL, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Tips, System Objects, Transact-SQL, tSQL

T-SQL – Search a Database for all Table Columns with a Text Value

Justin Cooney

There are times when it comes in handy to be able to query all columns of all of the tables in a database for a particular text match. For example: if you have just taken over a project with a large database and are trying to find where values from the UI are coming from, then it helps to be able to match text from the UI with tables and columns.

So you can see how being able to use a T-SQL query to locate the table and column responsible for holding specific data saves a lot of time. Rather than searching through the database diagram and guessing at tables and columns, you can simply run a blanket search to find out where the columns you need are.

In this article I’m providing a fully working example of a query that will search your database for any specific text that you may be interested in. I have added two restrictions to the example since it can take a longer time to run depending on the size of your database and the frequency that your keyword appears in:

  1. The first restriction is that I limit the search field to only VARCHAR columns.
  2. The second restriction is that the query will only return ten results per column searched.

Continue reading “T-SQL – Search a Database for all Table Columns with a Text Value” →