This post is part of a project to move my old reference material to my blog. Before 2012, when I accessed the same pieces of code or general information multiple times, I would write a quick HTML page for my own reference and put it on a personal site. Later, I published these pages online. Some of the pages still get used and now I want to make them available on my blog.
Photo by Steinar La Engeland
Below you will find four samples of ASP code: connecting to a database, inserting data, retrieving data, and closing the connection. These four pieces are the framework of a database-driven site.
Connecting to a Database
Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "Provider=SQLOLEDB; Data Source=(local); Database=[DATABASE]; User ID=[USER ID]; Password=[PASSWORD]" objConn.Open
The above lines create a ADO object, set the connection string (which includes the provider, data source, database, user ID, and password), and opens the connection.
Inserting Data into a Table
Dim SQL SQL = "INSERT INTO [TABLE NAME] ([COLUMNS]) VALUES ('[VALUE]')" objConn.Execute SQL
Probably the simplest example here. These lines simply define a SQL query and then execute it. This SQL query happens to be an insert statement.
Dim SQL, objRec SQL = "SELECT [COLUMNS] FROM [TABLE NAME]" Set objRec = objConn.Execute(SQL) While Not objRec.EOF Response.Write(objRec("Column_Name")) objRec.MoveNext Wend objRec.Close Set objRec = Nothing
The first part of this block of code is the same as inserting data. You define a query and execute it. However, this time we’re also storing the data in an object called objRec. From there, we loop through objRec, performing actions on each record (in this case, writing the data).
Closing the Connection
It’s important to close the connection when you’re done interacting with the database. This frees up resources on your database server, allowing for faster performance.