Portable Database Functions - Tool #1: Quick Execution

(Page 2 of 5 )

 

Already you’re thinking that it is very simple to execute a SQL statement. It’s really only 6 lines of code, providing you have a connection string and SQL statement:

 

dim objConn

set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open( your connection string )

objConn.Execute( SQL statement )

objConn.Close()

set objConn = Nothing

 

Could it really be any simpler? Well, of course, or else I wouldn’t be writing this article! When you think about it, each time you need to write 6 more lines of code. This little block of code also gives you approximately 150 more opportunities to mistype a character, resulting in time lost due to debugging. And trust me, by the 50^th time you write objConn.Close() in your application, you will inexplicably begin to despise each o, b, j, etc. that you have to type to perform the same simple procedure as the other 49 times.

 

So, here’s the tool to turn those 6 lines of monotony into 1 line of excitement. Well, maybe you won’t be that excited by it, but nonetheless, here it is:

 

'================================

Sub commitToDB( strSQL2, strDB )

'================================

 

       If strSQL2 = "" Then Call errorMessage( "Cannot commit empty string!" )

             

       If strDB = "" Then strDB = Application("MySQL_inventory")

       ‘=== in case you call several DBs from your application

‘===Application("MySQL_inventory") is the default

 

       Dim objConn

       Set objConn = Server.CreateObject("ADODB.Connection")

      

objConn.Open( strDB )

       objConn.Execute( strSQL2 )

       objConn.Close()

 

       Set objConn = Nothing

      

End Sub

 

 

Explanation

 

This subprocedure takes a SQL statement, and executes it on the default connection unless another is specified. The call errorMessage() is explained in the article “Easy Error Management”, basically just a way to explain in plain English that there’s a problem. Other than that, nothing miraculous is accomplished here, except for the fact that now when you wish to execute a statement, it only requires that you type the following:

 

Call commitToDB( strSQL, “” )

 

Wasn’t that easy? Honestly, it brings me a strange sense of satisfaction to type in this one line of code and move on with my life!

Next: Tool #2: How Fast Can You Count? >>
 

More Database Articles
More By Justin Cook

Comments On: Portable Database Functions

>>> Be the FIRST to comment on this article!