Solved

Command text was not set for the command object

Posted on 2004-08-30
12
420 Views
Last Modified: 2012-08-14
Hi,
I am getting the following error

Microsoft OLE DB Provider for ODBC Drivers error '80040e0c'

Command text was not set for the command object.

/working/results.asp, line 106

The code is as follows

if mid(Question1,1,9) = "question1"  Then
                Q1Response = mid(Question1,14,1)
                Select Case Q1Response
      Case "1"
                               InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_1) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "2"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_2) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "3"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_3) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "4"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_4) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "5"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_5) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "6"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_6) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "7"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_7) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "8"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_8) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "9"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_9) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      End Select
            
      Set rs1 = Server.CreateObject("ADODB.Recordset")
      rs1.ActiveConnection = MM_dsn1_STRING
      rs1.Source = InsertQuery
      rs1.CursorType = 0
      rs1.CursorLocation = 2
      rs1.LockType = 1
      rs1.Open()


I am getting the error in this following line
rs1.Open()


Please help
Thanks,
Harish
0
Comment
Question by:harish3699
  • 7
  • 5
12 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11932883
What happens when you do this:

if mid(Question1,1,9) = "question1"  Then
                Q1Response = mid(Question1,14,1)
                Select Case Q1Response
 Case "1"
                               InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_1) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case "2"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_2) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case "3"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_3) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case "4"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_4) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case "5"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_5) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case "6"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_6) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case "7"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_7) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case "8"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_8) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case "9"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_9) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     End Select
response.write InsertQuery
response.end

FtB
0
 

Author Comment

by:harish3699
ID: 11933047
Hi,
Thanks for the prompt reply.

I dont get the error but the records are not getting inserted into the table in the database.

Thanks,
Harish
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11933098
Okay, that is fine, but think about what just happened here---

we said to write out the result of InsertQuery, but you didn't get anything on your page, did you? That tells us that the Select Case did not capture anything. Try this:

 Select Case Q1Response
      Case "1"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_1) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
       Case "2"
              InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_2) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
       Case "3"
              InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_3) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
       Case "4"
              InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_4) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
       Case "5"
              InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_5) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
       Case "6"
              InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_6) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
       Case "7"
              InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_7) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
       Case "8"
              InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_8) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
       Case "9"
              InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_9) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case Else
            Response.write("No matching cases found!")
            Response.end
 End Select
0
 

Author Comment

by:harish3699
ID: 11933391
Sorry, i had a redirect page at the end of the code. That was the reason it was not displaying the InsertQuery. Now, I removed the redirect page and it displays the InsertQuery code. So, i think the select case is capturing data.

Harish
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11933418
Okay, let's go back to my earlier suggestion--which shouldn't have redirected if you did this exactly right:

Select Case Q1Response
     Case "1"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_1) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "2"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_2) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "3"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_3) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "4"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_4) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "5"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_5) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "6"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_6) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "7"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_7) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "8"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_8) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "9"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_9) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case Else
          Response.write("No matching cases found!")
          Response.end
 End Select

response.write ("The query was:" & InsertQuery )
response.end
0
 

Author Comment

by:harish3699
ID: 11933480
Hi,
I went back to your earlier suggestion. It is only displaying the InsertQuery string.

Harish
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11933514
Okay good. Please show me what the result is here.

FtB
0
 

Author Comment

by:harish3699
ID: 11933522
Hi,

INSERT INTO Political_Results (id, job, date_entered, 1_1) VALUES (5,'jack_lgmt','2004-08-14', 1)

Harish
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11933536
Oh hang on! I am being a complete idiot!!!!!!!!!!!!!!!!!!

If you are doing an update, you won't be able to get a recordset. Your code should be this:

Select Case Q1Response
     Case "1"
          InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_1) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "2"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_2) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "3"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_3) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "4"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_4) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "5"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_5) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "6"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_6) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "7"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_7) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "8"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_8) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
      Case "9"
            InsertQuery = "INSERT INTO Political_Results (id, job, date_entered, 1_9) VALUES (" & current_record & ",'" & job & "','" & t1 & "', 1)"
     Case Else
          Response.write("No matching cases found!")
          Response.end
 End Select

MM_dsn1_STRING.execute(InsertQuery)

FtB
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 250 total points
ID: 11933564
If that doesn't work, the do this:

Set objCommand= Server.CreateObject ("ADODB.Command")
objCommand.ActiveConnection = MM_dsn1_STRING
objCommand.CommandText = InsertQuery
objCommand.execute


FtB
0
 

Author Comment

by:harish3699
ID: 11935232
Thanks a lot. The second option worked.

Harish
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 11935279
Glad to have helped,

FtB
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now