Hi,

I'm tearing my hair out with a script I've almost got working and it's falling over on something daft...I think
For some reason I can only access post data from a form once

I have a page with a big list of products and form fields for each item of data. Ideally a user changes a few details and then updates everything with one click. The update bit works ok, but the (Request("product"+xx)) bit only works on the first loop. What am I missing?



<%
if (String(Request("MM_update") == "form2" &&
String(Request("hidRecIDs") != "undefined" {
var bob = String(Request("hidRecIDs");
var intRecIDs = bob.replace(/xx/gi, ""; // the gi bit is global and ignore case flag
var arrRecIDs = intRecIDs.split(",";

//testing 123
var sebarray = "";
for(y = 0;y< arrRecIDs.length;y++){
xx = arrRecIDs[y];
sebarray = sebarray+String(Request("product"+xx))+",";
}
Response.Write(sebarray);
//testing123


for(i = 0;i< arrRecIDs.length;i++){

x=String(Request("product"+arrRecIDs[i])) ; /// some sort of fuck up here on the second loop
strText = x.replace( "'", "''";

var commUpdate = Server.CreateObject("ADODB.Command";
commUpdate.ActiveConnection = MM_pb_STRING;
commUpdate.CommandText = "update tbl_Products set Product = '" + strText + "' WHERE ID = " + arrRecIDs[i];
commUpdate.Execute();
commUpdate.ActiveConnection.Close();


}
var strMessage = i + " Records Updated";
Response.Write(strMessage);

}
%>


I found a tutorial written for VBScript here www.drdev.net/article11.asp but I always work in Javascript so I've been trying to translate it. Thought I had it sussed <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

Thanks

Seb

Hi Seb

Comment out the command text and put a Response.Write(strText) and Response.Write(arrRecIDs[i])to see whats being generated. it looks right to me but you know how exact Javascript has to be.


Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi Lee,

I found what it was - You're right - I missed a Space

var arrRecIDs = intRecIDs.split(","; should be var arrRecIDs = intRecIDs.split(", ";

I've been trying to get something that updates multiple records with one click for ages.
Finally sussed and it was either than I thought. I was going to buy that ASP Grid software to do the job.

Thanks

Seb

Excellent Seb!

Just as a thought, as I do this multiple insert/update thing quite a lot. On each loop/update you are creating and destroying a command object, why not cut down on server resources and create the entire sql string in one go, create one command object, execute the entire string. The only thing to remember is to separate each update statement with a semi-colon, I also put a space after each too.

So

1. create a loop to create a single sql string, separating each statement with a semi colon.
2. check to make sure the string is not empty
3. create command object, execute string
4. clean up

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi lee,

thought I had it sussed but think i have a missing space or ; again . I tried to build an SQl string
like you said but it's giving me "Characters found after end of SQL statement." error message
guess there's something missing in the string?

thanks for your help



if (String(Request("MM_update") == "form2" &&
String(Request("hidRecIDs") != "undefined" {
var bob = String(Request("hidRecIDs");
var intRecIDs = bob.replace(/xx/gi, ""; // the gi bit is global and ignore case flag - gets rid of the xx around the ID nos
var arrRecIDs = intRecIDs.split(", ";// creates an array
var i=0;
var strText="";

while(i &lt; arrRecIDs.length ){
vproduct=String(Request("product"+arrRecIDs[i])) ;
vdescription=String(Request("description"+arrRecIDs[i])) ;
vpotsize=String(Request("potsize"+arrRecIDs[i])) ;
vplantsize=String(Request("plantsize"+arrRecIDs[i])) ;
vshelf=String(Request("shelf"+arrRecIDs[i])) ;
vinkoop=String(Request("inkoop"+arrRecIDs[i])) ;
// intNum = y.replace("'", "''";
strText+="update tbl_Products set Product = '" + vproduct +
"', Description = '" + vdescription +
"', Potsize = '" + vpotsize +
"', Plantsize = '" + vplantsize +
"', Shelf = '" + vshelf +
"', Inkoop = '" + vinkoop +
"' WHERE ID = " + arrRecIDs[i] +" ; " ;

if(arrRecIDs[i] &lt; arrRecIDs.length-1){
//strText+="; ";
}


i++;

}

Response.Write(strText);

var commUpdate = Server.CreateObject("ADODB.Command";
commUpdate.ActiveConnection = MM_pb_STRING;
commUpdate.CommandText = strText;
commUpdate.Execute();
commUpdate.ActiveConnection.Close();

var strMessage = i + " Records Updated";
Response.Write("----------------&lt;br&gt;-:&gt;"+strMessage+"&lt;BR&gt;----------------";

}

Hi Seb

Are you using SQL Server? I made an assumption you are, Access doesn't support this I think.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Yup - Using Access ... to my shame
does opening and closing the command object put much extra strain on the server?

Hi Seb

No shame in using Access, it's a perfectly adequate db. Depends on how many times you're going to do it, if you were processing 5000 updates at once, it would make more sense opening once rather than 5000, but for much smaller numbers, I can't see it being a problem, which is in fact far less work than editing the same number of records individually (get record, edit record etc)

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi lee,

the database isn't too big and there would probably only be a maximum of 30ish records being updated in one go.
I can see where it's more streamlined to use a string but it works nicely without even though the codes not very portable.

Thanks very much for your help - I can finally move onto all the other work I have to do on this <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

Seb

You're welcome! <img src=../images/dmxzone/forum/icon_smile_cool.gif border=0 align=middle>

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
<b>Lee Diggins</b> - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>