Debugging ADO code

It's a simple table with an ID column, a text value and a numeric value. We'll write some typical code to insert a row into it and make some mistakes along the way.

sName = "John" iAge = 20 set objCon = CreateObject("ADODB.Connection") sSQL = "insert into TestTable (txtName, intAge) values (" & sName & ", " & iAge & ")" objCon.Open sConnect objCon.Execute sSQL objCon.Close set objCon = nothing

When running this code we get the following error

Microsoft JET Database Engine error '80040e10' No value given for one or more required parameters /example/debug.asp, line 13

This is a typical error that people ask about. There are a few things I want to point out. First of all is the source of the error. It might not be the most obvious thing to see but the error is coming from the database engine itself. The relevance of this is that the error is not coming from the VBScript so the code is syntactically correct. The reason it doesn't work is due to the SQL your script is creating. This is backed up by the fact that line 13 is objCon.Execute sSQL. Syntacticly the code is fine and is being executed OK, but the database engine is rejecting the SQL you are passing it.

If you want to diagnose these kinds of problems the first thing to do is include this line;

sName = "John" iAge = 20 set objCon = CreateObject("ADODB.Connection") sSQL = "insert into TestTable (txtName, intAge) values (" & sName & ", " & iAge & ")" objCon.Open sConnect Response.Write "<p>" & sSQL & "</p>" objCon.Execute sSQL objCon.Close set objCon = nothing

The extra Response.Write prints out the SQL that we are executing.

insert into TestTable (txtName, intAge) values (John, 20)

Our problem here is that the SQL isn't valid. Text parameters need quotes around the values like this;

insert into TestTable (txtName, intAge) values ('John', 20)

So we update our code so that we put quotes around the text parameter.

sName = "O'Neil" iAge = 20 set objCon = CreateObject("ADODB.Connection") sSQL = "insert into TestTable (txtName, intAge) values ('" & sName & "', " & iAge & ")" objCon.Open sConnect objCon.Execute sSQL objCon.Close set objCon = nothing

Another issue is that even though the code itself is sound, it might be an issue with the exact data you're using. Let's say that our sName variable is being taken from the a FORM submission and contains an apostrophe. Using the same code as above only with data that has an apostrophe we get this;

Microsoft JET Database Engine error '80040e14' Syntax error (missing operator) in query expression ''O'Neil', 20)'. /example/debug.asp, line 13

Even though it is the same code we are using that worked before, it is not working now. Let's put our debug line back in to find out the SQL we are executing;

insert into TestTable (txtName, intAge) values ('O'Neil', 20)

The database engine assumes your parameter is encased in quotes so takes "O" as being your parameter, however it doesn't know what the "Neil'" is. The way to handle this is to double up the single quotes so the SQL looks like this;

insert into TestTable (txtName, intAge) values ('O''Neil', 20)

This is a special agreement with SQL that if there are two apostrophes together inside data it assumes it represents an actual quote character and is not indicating the end of the parameter.

There is a simple way of dealing this, we use the Replace function to replace all single quotes with two single quotes;

sName = "O'Neil" iAge = 20 set objCon = CreateObject("ADODB.Connection") sSQL = "insert into TestTable (txtName, intAge) values ('" & Replace(sName, "'", "''") & "', " & iAge & ")" objCon.Open sConnect objCon.Execute sSQL objCon.Close set objCon = nothing

The thing to note is to recognose when the error is coming from the database engine. If you need help with the code (by posting to a newsgroup for example) include the relevant code, the content of any variables you are using (such as sName in our example), the results of your Response.Write "<p>" & sSQL & "</p>" statement and the schema of the table. Quite often all of them are required to fully diagnose a problem.

Conversley we can have an error arising from the code itself;

set objCon = CreateObject("ADODB.Connection") sSQL = "insert into TestTable (txtName, intAge) values ('" & Replace(sName, "'", "''") "', " & iAge & ")" objCon.Open sConnect objCon.Execute sSQL objCon.Close set objCon = nothing

If we execute this code this is the result;

Microsoft VBScript compilation error '800a0401' Expected end of statement /example/debug.asp, line 11 sSQL = "insert into TestTable (txtName, intAge) values ('" & Replace(sName, "'", "''") "', " & iAge & ")" ---------------------------------------------------------------------------------------^

Now it says Microsoft VBScript compilation error which tells us that the error is coming from the VBScript engine so the problem is with the actual code. ASP is quite good because it tells us where about in the code the problem is. This is indicated by the ^ at end end of the "-----" line. In thise case we've missed out an ampersand. The code should be;

sSQL = "insert into TestTable (txtName, intAge) values ('" & Replace(sName, "'", "''") & "', " & iAge & ")"

By now you may realise that all this SQL building is quite a tricky business and gets more complicated the more parameters you have. Seeing as executing SQL is what ADO is all about surely there is a better way than this...?

Yep, there sure is.

Hard-coded SQL statements vs the Parameters collection

When calling a stored procedure or a piece of SQL text with parameters there are two methods of doing it. You can either construct the SQL yourself using string concatenation, or you can get the Parameters collection of the Command object to do all of the hard work for you. Below I'll show you both methods and will be making common mistakes along the way to show you how using the Parameters collection can greatly aid debugging.

This is the structure of the table that we'll be inserting into. It has 7 text fields, 2 int fields and a date field;

The SQL I am building is an INSERT statement to insert the values of some variables. You'll notice that each string variable has the Replace function used on it. This is to "double up" any single quotes in the variables that would otherwise cause problems.

For example if I try and insert the value of a variable into a table and that variable contains the name O'Neil, the single quote causes a problem;

sSQL = "INSERT INTO NameTable(strName) VALUES('" & sName & "')"

If we do Response.Write sSQL we get this;

INSERT INTO NameTable(strName) VALUES('O'Neil')

sSQL = "INSERT INTO NameTable(strName) VALUES('" & Replace(sName, "'", "''") & "')"

This gives us SQL of;

INSERT INTO NameTable(strName) VALUES('O''Neil')

When confronted with the double single quotes the SQL engine knows you want to insert just the one single quote in the string and that the quote mark is not indicating the termination of the string.

ERROR 1

I run this and I get;

Line 26 is objCon.Execute sSQL

What's the problem? Look back at the table design, TextValue3 is 3 chars long, my variable sTV3 is "Three" which is longer than 3 chars so I get that error. Full marks to anyone who spotted that little problem.

ERROR 2

I run this code and get

Anyone spot the error? Somewhere deep inside there is a missing single quote.

ERROR 3

I get;

The reason being I have mis-coded and am trying to put a text value in an integer field. We have made iIV1 equal "a" wheras the table expects a number. For Access this will give you "Too few parameters".

ERROR 4

This is the same as ERROR 3 only missing quotes around a field name

ERROR 5

I get;

Not too hard to spot this as there is only one date field and it is invalid.

Now let's make the same mistakes using the Parameters collection.

ERROR 1

I get;

Line 27 is

So right away we know which parameter is causing the problem.

ERROR 2

This is impossible to get as there is no fiddly parameter generation, no single quotes and no Replace functions as ADO does the hard work for us

ERROR 3

This gives;

Line 30 is

Again we know right away what is giving us the problem.

ERROR 4

Again this is impossible to get for the same reasons as error 2

ERROR 5

Again there is no guess work, we are told the line of the param that has the problem.

Note that is does not use SPs or Access Queries, just ADO and a hard-coded SQL statement. The same code applies to SPs and Queries though. Note also that ADO constants (adInteger etc) need defining.

For queries that involve passing simply integer fields I tend to hard code them, but if your query involves text, dates or many params of these types then it is worth doing it this way. Mainly for debugging purposes. You are told which param has the problem and there is no messy code. It is all clean, clear and crisp. Also if you are using SPs then you can write an app that actually writes the ADO code for you.

The Command object and stored procedures/queries

Up until now we've been using the command object to populate the parameters of hard-coded SQL statements. Now let's look at something more useful such as executing stored procedures (SQL Server) or queries (Access).

Our TestTable has three columns; ID, txtName, intAge. Here is a simple query to extract the ID of a record given a name and age.

CREATE PROCEDURE GetID @strName varchar(50), @intAge int AS SELECT ID FROM TestTable WHERE txtName = @strName AND intAge = @intAge

Using hard-coded SQL we would try something like this

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("test.mdb") & ";" sName = "John" iAge = 20 set objRS = CreateObject("ADODB.Recordset") sSQL = "GetID '" & Replace(sName, "'", "''") & "', " & iAge objRS.Open sSQL, sConnect while not objRS.EOF     Response.Write objRS("ID") & "<br>"     objRS.MoveNext wend objRS.Close set objRS = nothing

The above code will execute the following SQL;

GetID 'John', 20

Now let's convert our code to use the command object

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath ("test.mdb") & ";" sName = "John" iAge = 20 Set objCommand = CreateObject("ADODB.Command") With objCommand .CommandText = "GetID" .CommandType = adCmdStoredProc .ActiveConnection = sConnect .Parameters.Append .CreateParameter("@strName", adVarChar, adParamInput, 50, sName) .Parameters.Append .CreateParameter("@intAge", adInteger, adParamInput, , iAge) set objRS = .Execute End With while not objRS.EOF     Response.Write objRS("ID") & "<br>"     objRS.MoveNext wend objRS.Close Set objCommand.ActiveConnection = Nothing Set objCommand = Nothing

You should recognise this code, however there are two differences. When we used SQL text previously we had to indicate where the parameters were with ? marks. However the concept of parameters is built into stored procedures (from now on I shall refer to stored procedures however I am talking about Access Queries as well. I will point out at the time if there is anything that queries cannot do) so we don't need to indicate that there are 2 parameters for the GetID stored procedure, that information is contained withinin the stored prcedure itself. To this end the CommandText is simply "GetID". The CommandType has also been set to adCmdStoredProc to indicate that we are executing a stored procedure.

Other than that the code is identical.

With each parameter we create we have to supply its name, its type, its direction, its size and its value. You don't have to supply all of these items however. For example you don't need to supply the value right away, that can be supplied afterwards like this;

objCommand.Parameters.Append .CreateParameter("@strName", adVarChar, adParamInput, 50) .... objCommand.Parameters("@strName") = sName

You'll see in our example that strName was given a size (50) but intAge wasn't. The reason for this is that a varchar can be various lengths, however an integer is an integer. If we want to be precise we could supply the value of 4 for integer values, or we can just leave it blank and ADO will use its default value.

Given the numerous types of parameters it can be a minefield. Take the DateTime parameter. You'd be forgiven for thinking that the correct ADO constant was adDate, or maybe adDBDate. Well you'd be wrong as the correct value is adDBTimeStamp. How can you navigate this parameter minefield? Well ADO doesn't let us down as the parameters collection is very clever and has a trick up its sleave that we've yet to see. Note that this technique Try this code;

sName = "John" iAge = 20 Set objCommand = CreateObject("ADODB.Command") With objCommand     .CommandText = "GetID"     .CommandType = adCmdStoredProc     .ActiveConnection = sConnect     .Parameters("@strName") = sName     .Parameters("@intAge") = iAge     set objRS = .Execute End With while not objRS.EOF     Response.Write objRS(0) & "<br>"     objRS.MoveNext wend objRS.Close objCommand.ActiveConnection.Close set objCommand.ActiveConnection = nothing set objCommand = nothing

You'll see that this code still works even though we didn't add parameters to the Parameters collection. The reason is that the Parameters collection has the ability to create itself. It will do this if you execute the Refresh method of the Parameters collection, but it will also do it automatically if you reference the collection is any way if it is empty. For example if you do something like

With objCommand     .CommandText = "GetID"     .CommandType = adCmdStoredProc     .ActiveConnection = sConnect     Response.Write .Parameters.Count End With

You'll get a valid value back. When we queried .Count the Parameters collection saw it was empty so issued a Refresh call to populate itself. The problem with the Parameters collection populating itself is that it requires a round-trip to the database where it queries the system tables. For actual code I would recommend you do not do this. However where it comes in useful is where you can't work out the correct parameter type, size or anything else that you need. So if I had a DateTime field that I couldn't figure out the type of I'd write code like;

Set objCommand = CreateObject("ADODB.Command") With objCommand     .CommandText = "DummySP"     .CommandType = adCmdStoredProc     .ActiveConnection = sConnect     .Parameters.Refresh     Response.Write .Parameters("@dtDateTimeField").Type End With

The result is 135 and a quick check of adovbs.inc reveals

Const adDBTimeStamp = 135

A final note on parameters is how to deal witth TEXT (SQL Server) or MEMO (Access) fields where the data can be any length. For such fields you simply specify the size of the parameter as being the size of the actual data you are sending. For example;

sText = "This is my text to be placed in a TEXT or MEMO field" objCommand.Parameters.Append .CreateParameter("@strText", adVarChar, adParamInput, len(sText), sText)

Return values and Output parameters

How do we get return values and output params using ADO? Again we have to turn to the Command object to do this for us. You may have noticed that when the Parameters collection populated itself it also created the Return parameter for you (called RETURN_VALUE).

Let's consdier the following stored procedure (return values and output parameters are SQL Server stored procedures only).

CREATE PROCEDURE DummySP @intValue1 int, @intValue2 int, @intResult int output AS select @intResult = @intValue1 + @intValue2 if ceiling(@intResult/2)*2 = @intResult     RETURN 2 else     RETURN 1

It has two input parameters, one output parameter and a return value. The procedure adds the two input values together, places the result in the output parameter, and the return value is set to 1 if the result is odd and 2 if it is even.

We would call this procedure like so

iValue1 = 10 iValue2 = 15 iResult = 0 iReturn = 0 Set objCommand = CreateObject("ADODB.Command") With objCommand     .CommandText = "DummySP"     .CommandType = adCmdStoredProc     .ActiveConnection = sConnect     .Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)     .Parameters.Append .CreateParameter ("@intValue1", adInteger, adParamInput, , iValue1)     .Parameters.Append .CreateParameter ("@intValue2", adInteger, adParamInput, , iValue2)     .Parameters.Append .CreateParameter ("@intResult", adInteger, adParamOutput)     .Execute     iResult = .Parameters("@intResult")     iReturn = .Parameters("RETURN_VALUE") End With objCommand.ActiveConnection.Close set objCommand.ActiveConnection = nothing set objCommand = nothing Response.Write "<p>Result was " & iResult & " and the return value was " & iReturn & "</p>"

Note the value we have given to the direction values; adParamReturnValue for the return value, adParamInput for the input parameters and adParamOutput for the output parameters.