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 Directory Listing

Ever wanted to list the contents of a directory? I found this very useful when developing a site for one of our clients. You can use it to allow them to select files to use or really anything...

Here was my reference for this: http://www.brainjar.com/asp/dirlist/

I modified what they had to exclude files so that the user would only get the files I wanted them to see. Putting this information in a dropdown list can be very useful, but you could also list it out if you wanted.

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 Functions List

I found a nice list of functions in ASP.

Here it is: http://www.haneng.com/FunctionSearch.asp?s=a

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/

Removing Line Breaks with Dreamweaver

Well my last post took me forever to post. It seems blogger is dumb even in HTML code and takes line breaks literally... ? Meaning every line break was treated like one, even in HTML mode, which doesn't make any sense.

So I searched online and found out how to remove them with Dreamweaver. Its pretty cool. This is the process:

1. Open a document,
2. Click inside Code View,
3. Create an empty line,
4. Left-click in the margin on the left hand-side of that empty line (it should turn black on Windows, blue on Mac),
5. Select Menu > Edit > Find and Replace (keyboard shortcut: Ctrl + F),
6. Select an option in the "Find in:" dropdown box,
7. Select "Replace All" and you're done.

The source of this was here: http://www.tjkdesign.com/articles/whitespace.asp

Really really helpful! I'm sure I'll use it again.

Javascript Event Handlers

I wanted a quick list of event handlers so here they are. This is courtesy of our friends at the w3schools.

FF: Firefox, N: Netscape, IE: Internet Explorer

Attribute	The event occurs when...	FF	N	IE
onabort	Loading of an image is interrupted	1	3	4
onblur	An element loses focus	1	2	3
onchange	The user changes the content of a field	1	2	3
onclick	Mouse clicks an object	1	2	3
ondblclick	Mouse double-clicks an object	1	4	4
onerror	An error occurs when loading a document or an image	1	3	4
onfocus	An element gets focus	1	2	3
onkeydown	A keyboard key is pressed	1	4	3
onkeypress	A keyboard key is pressed or held down	1	4	3
onkeyup	A keyboard key is released	1	4	3
onload	A page or an image is finished loading	1	2	3
onmousedown	A mouse button is pressed	1	4	4
onmousemove	The mouse is moved	1	6	3
onmouseout	The mouse is moved off an element	1	4	4
onmouseover	The mouse is moved over an element	1	2	3
onmouseup	A mouse button is released	1	4	4
onreset	The reset button is clicked	1	3	4
onresize	A window or frame is resized	1	4	4
onselect	Text is selected	1	2	3
onsubmit	The submit button is clicked	1	2	3
onunload	The user exits the page	1	2	3

Listing can be found here: http://www.w3schools.com/jsref/jsref_events.asp

CSS Lists

Formatting list items is always tricky with css. There are so many combinations of what you can do and then there's browser differences with padding and margins.

This is a good reference if you'd like to Tame CSS Lists: http://www.alistapart.com/articles/taminglists/

Today you can do a lot with lists because you can make them look like whatever you want. So they are an ideal choice for navigation menus, lists of information, or whatever creative ideas you might have.

MYSQL Left Join & More

This may seem simple to others but I found a good reference for MYSQL query syntax. Sometimes queries can get crazy and confusing, so its good to read up and make sure you're doing everything right.

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

Cookies with ASP

I found some references for cookies with ASP that I wanted to save. These sites were good at helping me with the syntax because it is slightly different than what I'm used to in PHP.

http://www.w3schools.com/asp/asp_cookies.asp

http://riki-lb1.vet.ohio-state.edu/mqlin/computec/tutorials/aspcookie.htm

Download File Script for ASP

Well after tons of frustration with ASP (which sucks in my opinion), I was finally able to find a way to download files properly. So, if you every wanted to make it so your links aren't straight to the files, but to an intermediate page first, here you go. In PHP this is much simpler, FYI...

<%
Response.Buffer = True
Dim strAbsFile
Dim strFileExtension
Dim objFSO
Dim objFile
Dim objStream
'Set this to the variable in your GET
Set filename = request.QueryString("wp")
'-- set absolute file location
strAbsFile = Server.MapPath(filename)
'-- create FSO object to check if file exists and get properties
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
'-- check to see if the file exists
If objFSO.FileExists(strAbsFile) Then
Set objFile = objFSO.GetFile(strAbsFile)
'-- first clear the response, and then set the appropriate headers
Response.Clear
'-- the filename you give it will be the one that is shown
' to the users by default when they save
Response.AddHeader "Content-Disposition", "attachment; filename=" & objFile.Name
Response.AddHeader "Content-Length", objFile.Size
Response.ContentType = "application/octet-stream"

Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
'-- set as binary
objStream.Type = 1
Response.CharSet = "UTF-8"
'-- load into the stream the file
objStream.LoadFromFile(strAbsFile)
'-- send the stream in the response
Response.BinaryWrite(objStream.Read)
objStream.Close
Set objStream = Nothing
Set objFile = Nothing
Else 'objFSO.FileExists(strAbsFile)
Response.Clear
Response.Write("Le fichier est inexistant.")
End If
Set objFSO = Nothing
%>

P.S. I hacked this out from some download manager but don't remember it to give proper credit (sorry).

Pause Flash

// sec = number of seconds

function paused(sec) {
stop();
var i = sec - 1;
var t = setInterval(function () {
if (i == 0) {
clearInterval(t);
play();
}
i--;
}, 1000);
}

Then in a frame add:
paused (sec); // change sec to the number of seconds for the pause

PHP to ASP Quick Reference

I'm a PHP developer but the demands of clients always make you learn things you really don't want to... so I had to learn ASP (VBScript). I think the hardest part about learning a new language is the syntax, but I guess that's the only real difference, right?

Anyway, ASP is much different than PHP, in more ways than one. I won't get into them but if you're struggling to learn it, or if you know ASP but are struggling with PHP this is a very useful reference: http://www.design215.com/toolbox/asp.php

It saved me a lot of time because if you've tried to search for ASP code, like I have, you'll know it is much harder than looking for PHP references.

Quick AJAX Reference

I started working with AJAX this past summer and had one reference that really helped me the most. The site is: http://www.captain.at/howto-ajax-form-post-request.php

AJAX seems intimidating because it's new and cool but it really isn't. Simply put, it is a combination of JavaScript and a server-side language such as PHP. It's just about sending and receiving data through JavaScript to a server-side encoded page (PHP page) and displaying the output information to a user without refreshing their browser.

I'm finding that each new site that I do, I end up adding a little bit of AJAX flavor, so I'm sure we'll be finding more tips and tricks with it.

Blog Begins

Well, we've been talking about making a blog of useful stuff so here we go. This is just miscellaneous cool things we've found online (or from ourselves) related to web development/design.

If you find anything useful, link to it and let others know. Thanks!