Object required: "

[indupriya9]

I have two files, one which displays questions from an Access database (this is working fine) The problem is with the inserting of results into the databse. the code is as follows : I am getting an Object Required :" error in line 66


<%@ Language=VBScript %>
<%
Option Explicit
Response.buffer=true


Response.write "Hi You have reached the results page"

Dim rs, conn1, NumPerPage, CurrentPage, mySQL, c_id, radiogp

set conn1 = Server.Createobject("ADODB.Connection")
conn1.open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & server.mappath("../quiz/profile.mdb")
set rs=Server.CreateObject("ADODB.RecordSet")
set rs = conn1.Execute("select * from questions order by Q_Id")
'response.write rs
'response.end


dim conn,rst, sqltext
dim category,answer,adate,ipaddr,q_id,item

For each item in REquest.Form("q_id")

category = Request.Form("category")
ipaddr = request.serverVariables("REMOTE_ADDR")
adate = Request.Form("Day")
q_id = Request.Form("q_id")
Response.write "The q_id is :" &q_id
dim Maint
Maint = Request.Form ("q_id")


select case Maint
case 1
answer = rs.fields("rating1").value
case 2
answer = rs.fields("rating2").value
case else
answer = rs.fields("rating3").value
end select

response.write "the answer is:" &answer
set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & server.mappath("../quiz/profile.mdb")
'sqltext = "select * from answers;"
set rst = Server.CreateObject("ADODB.RecordSet")

'rst.Open sqltext, conn
sqltext = ""
sqltext = sqltext & "Insert into answers ([Category_Id],[IPAddr],[Adate],[Answer]) Values ("
sqltext = sqltext & Category & ", '" & request.serverVariables("REMOTE_ADDR") & "', #" & date() & "#, " & answer & ");"

response.write "<br>"
response.write "The category is " & category
response.write "<br>"
response.write "The ipaddress is " & ipaddr
response.write "<br>"
response.write "The Question ID is " & q_id
'response.end

response.write "<br>"
response.write sqltext

conn.execute sqltext
Next
line 66 = conn.close
set conn = Nothing



%>


Please let me know what I am doing wrong.

Thanks
ip

 

[Tarwn]

Why don't you trim out the portions of that page you don't need first. I don't see where you are actually using the conn1 connection (why do you need two connections to the same db?) and the recordset in that first section (since you never access it).
It might have something to do with connection pooling, but thats an out and out guess as I don't see anything wrong with your code other then a bunch of extraneous stuff at the beginning. If it fixes itself after you trim out tat beginning stuff then it's likely it was a connection pooling issue, as I think the conn1 and conn object will end up referencing the same actual connection.

SideNote: You don't need to instantiate Recordset objects if your going to be assigning them to a conn.Execute statement. The .Execute method will create it's own recordset so you are basically instantiating a recordset tat will then get thrown away. Here are the lines I'm referenceing (I'm highlighting the unnecessary one):

[highlight]set rs=Server.CreateObject("ADODB.RecordSet")[/highlight]
set rs = conn1.Execute("select * from questions order by Q_Id")

Just thought I would point thaty out for future reference.

-T

 

[indupriya9]

Hi Tarwn

Thanks a lot for your message. I have removed the code as per your suggestions and tried and now it is giving another error named "Number of query values and destination fields are not the same."

Hence I tries the following code to see the form output variables.

<%
For x = 1 To Request.Form.count()
Response.Write(Request.Form.key(x) & _
"(" & x & ")" & " = ")
Response.Write(Request.Form.item(x) & _
"<br>")
Next
%>

The output I received is as follows:

question(1) = 1, 2, 3, 4, 5
Day(2) = 20/10/2004 16:16:21, 20/10/2004 16:16:21, 20/10/2004 16:16:21, 20/10/2004 16:16:21, 20/10/2004 16:16:21
category(3) = 1, 1, 2, 3, 4
Q1(4) = 1
Q2(5) = 2
Q3(6) = 3
Q4(7) = 2
Q5(8) = 1
total(9) = 5

My answer table has four fields namely
category_Id
Answer
Adate
IPaddress

I need to store all these fields for each question. That is my first record in the databse should be 1 for category, 1 for answer of Q1, date for the adate field and IPaddress
My second record will be 1 for category, 2 for anser of Q2, date for the adate field and IPaddress and so on. Depending on the number of questions, I want to insert that many records in the database. I donot know how to proceed with this. Can you give me a clue what I should do in this scenario please?

Thanks
Indira

 

[Tarwn]

What line is it throwing this error on? I'm assuming it is an SQL error (probably on one of your Execute line sin the loop) but unsure if i am guessing corectly...

-T

 

[gijsterbeek]

Hi there,

Seeing the output, i count 9 items. I see only 5 sets of data. The loop you use to insert records, uses 9 cycles (the # of items in the form), and after 5 valid inserts, the 6th - 9th insert statement will look as follows:

Insert into
answers ([Category_Id],[IPAddr],[Adate],[Answer])
Values (",'',##,); <--- no data

You could verify this by Response.Write(sqltext) instead of only your output...

 

[indupriya9]

Hi Tarwn

The error is at the line

conn.execute sqltext

gijsterbeek The response.write sql command is giving the following statement.

Insert into answers ([Category_Id],[IPAddr],[Adate],[Answer]) Values (1, 1, 2, 3, 4, '127.0.0.1', #21/10/2004#, );

For some reason it is not capturing the answer

any thoughts on how to fix this?

Thanks
IP

 

[Tarwn]

Thats the problem, if you look at your SQL statement your giving it 8 values to fit into 4 fields

Firsdt: Your category has 5 values so when you output it you get them all (comma delimited). You shouldl split this into an array and only output the one value that corresponds to the q_id your working with at the moment. You can keep track of that by simply having a counter in the loop that increments at the end of each loop.
Second problem: answer is either an empty string or null, so that is going to be an issue. Are the Q1-Q5 values the answers for each question?