Cannot figure out why ASP/SQL error occurs: Command text was 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 I DECLARE AND INITIALIZE MY VARIABLES AND GET THE FORM DATA TO BE PROCESSED. (i left this out as this message is already pretty lengthy).

<%
'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

 

[philhege]

skurpyun,

Wrap your form variables in CStr(), and check to see if any values are empty. Print out the SQL string. Try it with a combination of empty values and see what you get.

 

[skurpyun]

Philhege,

tried it without much luck. I keep testing with different ways of building the sql string, and printing it to the screen, but i can never replicate the error that a few of my users are getting.

And becuase it happens to infrequently, i'm beginning to think it's not the code, but the server that is the issue. so i am currently looking into that.

thanks for you response. If i ever figure this out i'll post it here.

-skurpyun

 

[SQLBill]

i can never replicate the error that a few of my users are getting

Is it always the same users? Is is all the time for those users? Have you tried it on THEIR machines?

If the answers are YES, YES, and NO. It could be a permissions problem.

-SQLBill

 

[skurpyun]

SQLBill,

the answer is actually NO, NO, and NO.

The users are all unique with the exception of one person who it happened to twice a week ago (she tried to sign up, got the error page, then immediately tried again and got the error page again, and tried a third time and was successful). So it does not happened all the time for the users that do try multiple times. And since this is a tool i had on the internet for anyone to try out, I could not test from the persons machines.

I have since pulled it from the internet until i can figure out what is going on.

I will definitely look into the possibility of a permissions issue nevertheless.

thanks.

-skurpyun.

 

[skurpyun]

Well, still no luck with figuring out why this script was throwing errors. To this day I continue to test and retest it without being able to replicate the errors.

So, I decided to give up and rewrite the entire chunk of code based on examples posted here:

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

So far, i have not had any problems. Thanks to all for your input.