ASP AND SQL TUTORIALS : ASP SQL DATABASE TUTORIALS

How do I deal with an apostrophe (') in a SQL statement? - The apostrophe is an illegal character in T-SQL because it is interpreted as a string delimiter. To allow a ' mark to be inserted into a database, simply double-up all occurences of the ' mark:

SQL Test - For every web database project I work on, I pop this code somewhere onto the server. This page allows me to test out SQL queries before I put them into my ASP code.

Generate SQL Statements using easy interface - Make a simple interface to generate SQL Statements which can be copied and pasted into your ASP code. This is not ment to support crazy queries, or advanced SQL, but more to serve as a way to speed up mundane coding.

ASP 101 - Using Quotes in Your SQL Commands - SQL uses a single quote (') character to mark the beginning and end of a string. As such, using a string that contains a quote can cause errors because your database thinks the string is finished and expects SQL commands to come immediately after it.

What is wrong with 'SELECT *'? - SELECT * is inefficient, particularly when you are only using a few of the columns in the table. This is because it actually makes TWO queries to the database: before it runs your query, it has to query the system tables to determine the name and datatypes of the columns. It is much more efficient to NAME your columns in the SQL query, and this will also help in having your column names right there... so you don't have to keep flipping back and forth between ASP page and database. In addition, this will prevent ambiguous column names in your resultset (in the case where both or all tables in the JOIN statement have columns with the same name). Further still, your code can break if it relies on ordinal position, and then someone inserts a column at the top of the table (which you can do in Enterprise Manager, or by dropping / re-creating the table). This is also a strong case for always defining columns in the INSERT list.

Which is better, rs(0) or rs("column_name")? - It is marginally faster to perform recordset element lookups by ordinal position (e.g. rs(0) or rs(0).value) vs name (e.g. rs("column_name")).

How to search and replace SQL Server data in all columns of all tables, in a given database? - This is one of the common requests I see in the newsgroups. People often wonder how they could search and replace data, that is stored in their tables. While this doesn't sound like a very good thing to do, there are some genuine situations, where 'find and replace' functionality is needed. For example, spelling mistakes in the column values, or moving a database from one location to another, and wanting to replace all location specific content.

Codefix - The Apostrophe Bug - In an SQL statement text is enclosed by an apostrophe at the start and an apostrophe at the end. Consider the SQL Insert statement below.

Removing single quotes for SQL statements - One of the most common problems in submitting web forms to a database using SQL is the dreaded single quote. The database reads single quotes as part of the syntax of the SQL query and invariable produces an error.