Why ASP/SQL error occurs only occasionally: Command text not set

[skurpyun]

Hey folks,
I am having a very annoying problem with my ASP/SQL code and I am not sure if this is best suited for the SQL forum or the ASP forum, so I will post them in both places and ask for forgiveness later.

Anyway I am getting this error from my code:
"Command text was not set for the command object"

A user fills out a form with their email address. Hitting the submit button directs them to a "process.asp" file that validates the info and updates a database accordingly.

In the code I am selecting a users email adress from the database. If the email address is not found then I update the database with the users information. If the email is found, then the user is directed to a page that says "no duplicate emails allowed".

If an error occurs, I am emailed the sql statement along with the err number and error description. In the email the line that says "sql statement:" would be following by the sql statement.

but every now and then, and it is weird because this DOES NOT happen all the time, i get an error saying basically the following:

sql statement:''.

Description: Command text was not set for the command object.
Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147217908


which basically tells me that there was no sql statement to process!!!! even though i explicitely declare a variable called "sql_insert", initialize it, and set it equal to an sql insert statement like

sql_insert = "insert into" & tableName & "(fname,lname,street,city,state,country,zip,email,datecreated) values ('"& fname &"','"& lname &"','"& street &"','"& city &"','"& state &"','"& country &"','"& zip &"','"& email &"',getDate())"


Here is the entire code of the process.asp file:


<%
dim conn, sql

sub connecttoDB()
set conn = server.createobject(&quot;ADODB.Connection&quot
conn.Open = &quot;DSN=mydsn;UID=me;password=funkymamajamma&quot;
end sub

sub disconnectfromDB()
set sql=nothing
conn.close
set conn = nothing
end sub

sub emailError(email,message, errdesc, errsource,errnumber)
message = message & VBCRLF & VBCRLF & &quot;Description: &quot; & errdesc & VBCrlf & &quot;Source: &quot; & errsource & VBCrlf & &quot;Number: &quot; & errnumber

dim objEmail
set objEmail = server.createobject(&quot;CDONTS.NewMail&quot
with objEmail
.To = email
.From = &quot;webuser@mysite.com&quot;
.Subject = &quot;Error occured in process.asp&quot;
.Body = message
.Send
end with

set objMsg = nothing
end sub
%>

'HERE DECLARE AND INITIALIZE MY VARIABLES AND GET THE FORM DATA TO BE PROCESSED

<%
'CONNECT TO THE DATABASE
connecttoDB

sql = &quot;select email from userTable where email ='&quot;& email & &quot;' and lname='&quot;& userlastname &&quot;'&quot;
set rs = conn.execute(sql)

if rs.eof then
'user does not already have an entry in the database
rs.close
set rs = nothing
disconnectfromDB


sql_insert = &quot;insert into&quot; & tableName & &quot;(fname,lname,street,city,state,country,zip,email,datecreated) values ('&quot;& userfirstname &&quot;','&quot;& userlastname &&quot;','&quot;& street &&quot;','&quot;& city &&quot;','&quot;& state &&quot;','&quot;& country &&quot;','&quot;& zip &&quot;','&quot;& email &&quot;',getDate())&quot;

connecttoDB
conn.BeginTrans()
conn.execute(sql_insert)


if err.number then
'if an error has occured, undo sql insert and email error.
conn.rollbacktrans
disconnectfromDB
emailError strEmailTo, &quot;Database error. DB update failed.&quot;& vbcrlf &&quot;Sql statement: ' &quot;& sql_insert &&quot; '.&quot;,err.description,err.source,err.number

response.redirect &quot;error.asp?message=dbnotupdated&quot;
else
conn.commitTrans()
disconnectfromDB
response.redirect &quot;thanks.asp&quot;
end if

else
'user already has an entry/email in the database.

disconnectfromDB
response.redirect &quot;error.asp?message=duplicateemail&quot;
end if
%>


I know it's a bit lengthy. But bear (sp?) with me.

The code works most of the time. But every now and then i get the error mentioned above (i.e. &quot;Command text was not set for the command object.&quot and an empty string for the sql statment.

Any constructive criticism or thoughts on why this error occures would be greatly appreciated. Or if you have seen this happen before, then please enlighten me. Please!!!
I have been looking over this code for almost a week now and yet i cannot figure out why i am getting the weird error.
Thanks for your help.

-skurpyun

 

[cathyw]

Hi Skurpyun,

I suspect it may have something to do with the contents of the variables that are being inserted. Do the variables always contain a value? Are you checking for special characters, like apostrophes or quotation marks, in your form variables (those can wreak havoc on the string you're trying to build). Are the failures consistent? - you say that it fails occasionally - but when it fails for a certain set of form data, does it always fail for that data? - that might indicate a pattern)

Failing that, while testing, it might help to Response.Write the insert statement to see what it actually looks like - that might also provide clues.

Hope that helps,
Cathy

 

[skurpyun]

Hey Cathy,
thanks for taking the &quot;challenge&quot;.
Well, i do validate the variables by removing any dangerous strings (eg. apostrophes), and I make sure that all the variables always have some data in them (either some default string or number).

I have tried testing this for what seems to be a million times and used response.write to see the sql statement, but I can never replicate the errors my users are seeing. And what boggles me more is that for a majority of my users this works without a problem. But there are the few that get errors (and they later try again and have no problems, which leads me to question whether its a user data issue or not). I also tried to look for commonalities in all the error emails that i have and have not been able to find any patterns. It just seems to be random.

I am truley lost on this one.

Maybe i have to rewrite the script to work differently?? arghhh!

 

[cathyw]

Hi Skurpyun,

It sounds like you have a bit of a mystery on your hands.

I notice you're using OLE DB for ODBC for your connection. Is your database in SQL Server? (I'm assuming that it is based on your posting in that forum?). If it is, using the OLE DB for SQL Server provider might work better (I remember reading somewhere that the SQL Server provider was better, although I don't remember why).

The fact that it fails for a user and then works later seems to eliminate the browser as a possible culprit.

Again, assuming your database is in SQL Server, have you considered trying to do your insert with a stored procedure? That would eliminate the building of the insert string on your ASP page, and is likely more efficient as well.

Hope that helps,
Cathy

 

[skurpyun]

Cathy,
i will try your idea. thanks for your thoughts on this.

It is very much a mystery and for the life of me i cannot figure it out and none of my colleagues can figure it out. The strange thing is that the last &quot;error email&quot; i got was sunday morning, and there hasn't been an error since. So it remains a mystery.

Ok, back to testing. and thanks.

 

[robi2]

Try to email yourself each time even if you don't get an error and see if you recieve an sql statement.

And im assuming the email field is a primary key in your database, so there is a much better way to check if the email is already in the database. You tell asp to ingore errors, then you add the email to the db, and then you check the connection object for any errors, if the email wa in the db then you will recieve a specific error, and then you redirect the user to the no duplicates page, and if not you just continue normally.

I don't think my explenation is really good so here is an article about this:

http://www.4guysfromrolla.com/aspfaqs/ShowFAQ.asp?FAQID=106

there are a few examples on that article, i suggest u use the last one.

 

[skurpyun]

Well, I unfortunately was not able to figure out why the script was not working. And I have tested it many times on different computers, different browsers, different operating systems, different user inputs, even different internet connections!! Still i cannot get the error to occur.

So, not having the time to continue modifying the old script and trying to figure out the how and why, i decided to rewrite some scripts based on some examples from the link posted by robi2 (

http://www.4guysfromrolla.com/aspfaqs/ShowFAQ.asp?FAQID=106

)
and so far the errors don't seem to be occuring (well...it's only been 3 days since i employed it). So thanks robi2....star to you.
thanks to all for your input.