[RESOLVED] Where is the error?

[landslide]

I trying to figur out what is wrong with this update statment. The code does not give any errors but it doesnot update ethier. The code updates multiple records at once. Any Ideas?

<%
var Recordset1 = Server.CreateObject("ADODB.Recordset");
Recordset1.ActiveConnection = MM_MemphisAssessment_STRING;
Recordset1.Source = "SELECT * FROM [option] WHERE questionid = 725";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 1;
Recordset1.Open();
var Recordset1_numRows = 0;

aroption = new Array();
arquestionid = new Array();

var f=new Enumerator(Request.Form);
var el;
var start;
var len;
var idx;


for (; !f.atEnd(); f.moveNext()) {
el = f.item().toString().toLowerCase();
start = el.indexOf("_");

if(start > -1) {
start++; len = el.length - start;
idx = parseInt(el.substr(start, len));

if(!isNaN(idx)) {
if(el.indexOf("option")!=-1) aroption[idx] = Request.Form(el);
if(el.indexOf("questionid")!=-1) arquestionid[idx] = Request.Form(el);
}
}
}

for(var i=0;i<arquestionid.length;i++) {
if(aroption[i] && arquestionid[i]) {
updateDB (aroption[i], arquestionid[i]);
}
else {
Response.Write("");
}
}

function updateDB(param_option, param_questionid) {
var update = Server.CreateObject("ADODB.Command");
update.ActiveConnection = MM_MemphisAssessment_STRING;
update.CommandText = "UPDATE option SET option = '" + param_option + "' WHERE questionID = '" + param_questionID + "'";
update.CommandType = 1;
update.CommandTimeout = 0;
update.Prepared = true;
update.Execute();
update.ActiveConnection.Close();
}

var Repeat1__numRows = -1;
var Repeat1__index = 0;
Recordset1_numRows += Repeat1__numRows;
%>

[landslide]

Any ideas why it wont update. It updates multiple records at once when it is working correctly.

[landslide]

any ideas on this I still cant find my problem

[russell]

what is the value of arquestionid.length?

[landslide]

I hope I answer this correctly. This is for a online test. This page is used to update the four multiple choice answers at once. In my query the 725 is the value that = that test questions answers. So in my query Recordset1.Source = "SELECT * FROM [option] WHERE questionid = 725";
finds the four test answer options for my test. The aroption reffers to a field in my DB. this field has the text answers. The arquestion is a numeric field that identifys which answer applies to what question. It is not unique and usally has four felds with the same number. I have one more field which I think is where I am messing up. I have a filed call option ID and this is the uniqe number four each field. I created the page using some modified code that you helped me with about six months ago. (Thanks again for that). So if the code does not make perfect sense thats why. I am still learning.

[russell]

in this block of code

for(var i=0;i<arquestionid.length;i++) {
if(aroption[i] && arquestionid[i]) {
updateDB (aroption[i], arquestionid[i]);
}
else {
Response.Write("");
}
}

where u are calling the updateDB() function...if arquestionid.length = 0 then you will never call the function. so u need to know what the value is...

i would add this just b4 that block (for troubleshooting only)...

Response.Write("arquestionid.length=" + arquestionid.length);

[landslide]

The value is 725 how would I write this. also what does this mean?
for(var i=0;i<arquestionid.length;i++) Im confused as what the ;i++ means

[aaron.martinas]

that is how you write a typical for loop (in most languages)

i = 0;
set the control variable of the for loop to 0

i < arquestionid.length;
run this loop as long as the value of "i" is less than the value of "arquestionid.length"

i++
shorthand for "i = i + 1"... increments your control variable by one, and then the loop will run again as long as "i" is less than "arquestionid.length"

[landslide]

Thanks for all your replys Im still stuck on this one. All it is I have a db with three fields OptionID which is a unique auto number, QuestionID which has four records with the same value (725) and Option which is my answer field. I am trying to update all four answers at once maybe I am using the wrong field. Any additional help is more than welcome. Thanks in advance.

[landslide]

Any idea whats wrong with my code?

[mattyblah]

do you know what profiler is? can you run it and post the queries that are run when you submit the page?

[landslide]

I never used that how does it work. I only have 1 query and my update statement.
"SELECT * FROM [option] WHERE questionid = 725";
is my only query on this page besides my update statment.

[mattyblah]

profiler monitors a sql server for various "triggers", for lack of a better term. You can use it to monitor your development sql server for all t-sql batch statements that are run. what I do is clear the profiler window, then run the page and validate the sql that was run. when you aren't getting the expected results profiler is invaluable.

[landslide]

I am using a access database That wont work will it?

[mattyblah]

no it wont. can you response.write the query before it runs? I would run it multiple times to ensure it's the same query. once you figure that out, you can go from there. let me know how it goes.

[landslide]

Thanks for all your help I found an alternative. I just did a simple update statment and droped thbe multi update statment. Thanks for your help.