ASP's Fast Way To Pull Data from Database

This is a great way to pull in all records from a (MSSQL) database in ASP.

use the GetString() function for your record set!  It is pretty simple and really cool.  Here is my example:

set rs = Server.CreateObject("ADODB.Recordset")

myvariable = rs.GetString (2, , vbTab, vbCrLf, "Null")

Here is the info on the function GetString:

string = recordsetobject.GetString (StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

For more details go here: DevGuru ADO Recordset::GetString Method

MSSQL Searching for Datetime value

This is a great resource for trying to make a query in SQL Server for the datetime data type.  Use this if you're trying to find all entries from a date like yesterday, last month, or anything else.

Working with SQL Server Date/Time Variables: Part Three - Searching for Paritcular Date Values and Ranges

What I found even more useful is this little code:

query = "SELECT * FROM your_table WHERE DATEDIFF(dd, your_date_field, GETDATE()) = 0"

What that does, is get you all entries from your_table where your_date_field and the current date (today's date) are not different.  The 'dd' means day, so it is comparing the day.

This will get all entries from yesterday:

query = "SELECT * FROM users WHERE DATEDIFF(dd, datecreated, GETDATE()) = 1"

And this will get from last week:

query = "SELECT * FROM users WHERE DATEDIFF(ww, datecreated, GETDATE()) = 1"

For a full list of what DATEDIFF does take a look at this: DATEDIFF (Transact-SQL)

Have fun!

Escape Character in MSSQL

I had a frustrating time trying to escape single quotes in MSSQL.  I had enclosed my content in single quotes so that double quotes would be okay, but then I had to deal with if a user put a single quote in the field.  In standard MySQL you could just use the backslash (\) but MSSQL requires a single quote (') in order to escape a character!

Here is a little function I made in PHP to look for single quotes and doubling them so that they would be escaped (thus allowing the content to be inserted into MSSQL).

//********************************

// fix quotes for mssql

//********************************

function mssql_quote_fix($text)

{

$text = str_replace("'", "''", $text);

return $text;

}

Just call this function on the text you're trying to insert.

I'll give a little credit to this article for helping me: Escape Character In Microsoft SQL Server 2000

SQL Random Rows

Select a random row with MySQL:

SELECT column FROM table ORDER BY RAND() LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table ORDER BY RANDOM() LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table ORDER BY NEWID()

Thanks to: http://www.petefreitag.com/item/466.cfm

SQL JOIN References

I had a previous post about LEFT JOINS but I believe w3schools does a better job at explaining them.

• General Join Info
• Inner Join
• Left Join
• Right Join
• Full Join
• Union
• Select Into

MSSQL Count Query

The count query is very useful in SQL.  Below is the syntax:

SELECT COUNT(thefield) AS outputvariable FROM tablename GROUP BY thefield HAVING anyfield = yourvalue

• thefield is what you want to count
  
• outputvariable is what you want the count to be saved in
  
• tablename is the name of the table to look into
  
• anyfield is any field you want to use as a criteria
  
• yourvalue is the value for your criteria
  

This may also work the same for MYSQL.

MSSQL Data Types

Sometimes it gets confusing trying to figure out what all the SQL Server data types really are for. It seems, to a novice, that they have too many... but in reality they all serve a purpose.

Below is a table that gives a brief description of each of these data types.

Data Types	Description
bigint	Integer data from -2^63 through 2^63-1
int	Integer data from -2^31 through 2^31 - 1
smallint	Integer data from -2^15 through 2^15 - 1
tinyint	Integer data from 0 through 255
bit	Integer data with either a 1 or 0 value
decimal	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
numeric	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1
money	Monetary data values from -2^63 through 2^63 - 1
smallmoney	Monetary data values from -214,748.3648 through +214,748.3647
float	Floating precision number data from -1.79E + 308 through 1.79E + 308
real	Floating precision number data from -3.40E + 38 through 3.40E + 38
datetime	Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds
smalldatetime	Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute
char	Fixed-length character data with a maximum length of 8,000 characters
varchar	Variable-length data with a maximum of 8,000 characters
text	Variable-length data with a maximum length of 2^31 - 1 characters
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters
ntext	Variable-length Unicode data with a maximum length of 2^30 - 1 characters
binary	Fixed-length binary data with a maximum length of 8,000 bytes
varbinary	Variable-length binary data with a maximum length of 8,000 bytes
image	Variable-length binary data with a maximum length of 2^31 - 1 bytes
cursor	A reference to a cursor
sql_variant	A data type that stores values of various data types, except text, ntext, timestamp, and sql_variant
table	A special data type used to store a result set for later processing
timestamp	A database-wide unique number that gets updated every time a row gets updated
uniqueidentifier	A globally unique identifier

The table was taken from: http://www.databasejournal.com/features/mssql/article.php/2212141

ASP RecordSet Reference

When dealing with SQL queries its important that you understand RecordSets in ASP. It's pretty straight forward but since there are a lot of methods association with this object, its good to have a reference.

Here's a reference for you all: http://www.itechies.net/tutorials/asp/index.phpindex-pid-rec.htm

Date & Time Formats in ASP and MSSQL

ASP is terrible when it comes to formatting of dates. When putting your date into MSSQL (SQL Server), you probably will encounter issues with the date not being when you entered. A major note is to make sure you include single quotes around your date when making your SQL query. If you don't, you probably won't get the date you intended.

Here is a reference for formats supported in MSSQL for the datetime field. The article also gives an explanation of the difference between DATETIME and SMALLDATETIME.

http://www.databasejournal.com/features/mssql/article.php/2191631

Displaying Large Text with PHP/ASP from SQL

This is an issue I faced with both PHP and ASP. When I set a field type in SQL to varchar(max) or some large amount, when I attempt to display this information via the server-side language, nothing displays.

I found online an explanation for ASP:

After some testing I found there are no SQL Server 2005 data types
that can be read by an ASP page that hold more than 8000 characters. I
speculate that the 2-byte field length indicator is not read by ASP.
Maybe the field length of the old Access memo data type was encoded
differently. - kirkatsfw-ga

I also found something similar related to PHP. My solution in both cases was as stated above, to reduce the limit for the field to something that the server-side languages could deal with. This was the case with both PHP and ASP along with both MySQL and MSSQL.

ASP & MSSQL Query Examples

Here are some code examples for doing things in ASP with MSSQL (SQL Express):


Setup the Connection

'Setup the connection
Dim aConnectionString

aConnectionString = "Driver={SQL Native Client};Server=sitename\sqlexpress;Database=databasename;Uid=username;Pwd=password;"

'Connect to DB
Dim conn, R, SQL, RecsAffected

Set conn = Server.CreateObject("ADODB.Connection")
conn.Mode = adModeReadWrite
conn.ConnectionString = aConnectionString
conn.Open

Setup RecordSet & List all Entries

'select a record set
set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Select * from table", conn

do until rs.EOF
for each x in rs.Fields
response.write(x.name)
response.write(" = ")
response.write(x.value)
next
rs.MoveNext
loop

rs.close

Select an Entry & See if it Exists

set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "Select * FROM tablename WHERE fieldname = 'fieldvalue'", conn

'check if login worked
dim found

if rs.BOF and rs.EOF then
'no entry found
found = false
else
'entry found
found = true
end if

Add an Entry

'add an entry
'conn.Execute "INSERT INTO tablename (fieldnames) VALUES (fieldvalues)"

Delete an Entry

'delete an entry
'conn.Execute "DELETE FROM tablename WHERE fieldname = 'fieldvalue'"

Update an entry

'update an entry
'conn.Execute "UPDATE tablename SET fieldname = 'somevalue' WHERE fieldname = 'fieldvalue'"

There's a lot more but these are some for reference purposes.

Resource for Connection Strings

Every get stuck trying to figure out how to connect to a database? I had this trouble when trying to get into a MS SQLExpress database. It just wouldn't connect... but I knew it was my problem.

Here is a great site for connection strings: http://www.connectionstrings.com/