database update issue

[Path9898]

greetings.
I am having an issue with a piece of code, I was hoping someone can show me where I am going wrong. Basically I select 9 rows of data out of a table that have the same item number, but each row has a unique GUIDs, then I loop through the data to display it on a webpage. This all works fine.

The issues comes when I try to submit all the data (changed or not) back into the table. My submit code is below


strcreative_id = Request.Form("c reative_id")
strcreative_run time = Request.Form("c reative_runtime ")
strcreative_sta te = Request.Form("c reative_state")
strcreative_tex t_data = Replace(Request .Form("creative _text_data"),"' ","''")
strcreative_tex t_effects = Request.Form("c reative_text_ef fects")
strcreative_ima ge_name = Request.Form("c reative_image_n ame")
strcreative_gui d = Request.Form("c reative_guid")

' Build our SQL String
strSQL = ""
strSQL = strSQL & "UPDATE tbl_advert_setu p_creatives "
strSQL = strSQL & "SET creative_runtim e ='" & strcreative_run time & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "creative_s tate ='" & strcreative_sta te & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "creative_text_ data ='" & strcreative_tex t_data & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "creative_text_ effects ='" & strcreative_tex t_effects & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "creative_image _name ='" & strcreative_ima ge_name & "'"
strSQL = strSQL & "Where creative_guid = '" & strcreative_gui d & "';"

Response.Write strSQL

%>


The Response.Write produces this:

UPDATE tbl_advert_setu p_creatives SET creative_runtim e ='30, 60, 23, 60, 240, 33, 90, 22, 60', creative_state ='1, 1, 0, 1, 1, 0, 1, 1', creative_text_d ata ='tires, today only!!, Buy some today...please. , 75006, Obamas Transition Chief., Everyone is reviewing can.', creative_text_e ffects ='Stretch, Stretch, Inset', creative_image_ name ='2.jpg, 3.jpg, 5.jpg, 1.jpg'While creative_guid = '{B53BA5B7-6971-4442-9A0F-83EAF27E2F0E}, {8E123F5C-25BB-4CCD-A443-77C9D0961002}, {53D1FB08-2998-49CB-8D78-D128FAAF6232}, {693BD709-C65D-438F-AC3F-817C849B2ECE}, {9C7B4C1E-A466-4854-BDCE-250661639490}, {27448802-E779-4AFE-A147-41410CEE81A9}, {FF4F7F87-1FB2-4902-8055-2F688920A014}, {8CBF7404-2CDC-42DC-9DE1-8AEDA0B148DB}, {4440412C-951C-46BF-8935-F401D850815B}, {BEF028CB-DAF3-470E-B18D-DD9806DD9B04}';

How do I get the data back into each specific row of my db? It is trying to stuff all 9 rows of data back into the first row. The creative_guid column is a unique identifier.
I guess I need some sort of update loop statement, but can’t figure it out. when i add "Do while not Rs.EOF" to the update (above strcreative_id = Request.Form("c reative_id")) it says "Operation is not allowed when the object is closed. " the RS is not closed as best as i can tell.

thanks for any help.

P

[Nicodemas]

You have a complex problem. In this answer I provide a possible solution. I am not 100% my solution is what you looking for because of how you stated your problem. If my solution isn't what you need, please revise your first post.

Are all of the values supposed to be stored in only one record like that? When you Request something from the querystring or form that has multiple entries, the script will return a string of comma separated values.

Your SQL statement appears to have an error in it. You have the word 'While' in there, whereas I believe you meant to have 'WHERE'.

If you mean to update each individual row, yes, you will need a loop that executes a unique UPDATE SQL statement each iteration. To do that, however, you'll have to pull out the unique values from your Request data. That seems to indicate you'll need to Split() the strings.

I'm going to construct an example of how to do the looping update.

'// imagine this assigns "{ABC}, {XYZ}"
dim myGUIDs : myGUIDs = request.form("theGUIDs")
 
'// imagine this assigns "John Smith, Sacajawea"
dim myValues : myValues = request.form("theValues")
 
'// returns 
'// myGUID(0) = "{ABC}" 
'// myGUID(1) = "{XYZ}"
 
myGUID = split(myGUIDs, ",") 
 
'// returns 
'// myValue (0) = "John Smith"
'// myValue(1) = "Sacajawea"
 
myValue = split(myValues, ",") 
 
for x = 0 to 1
   sSQL = "UPATE myTable SET myName = '"& replace(myValue(x), "'", "") &"' WHERE myID = "& replace(myGUID(x), "'", "")
 
   myConnection.execute(sSQL)
next
 

[Path9898]

Nicodemas, thanks for your reply.
I think in my inexperience I am going about this the wrong way.I will try your solution ASAP, but what I am attempting to have is a webpage with 40 different updatable fields in it.
This is an upgrade to an existing (functioning) app I built last year. The difference is that the old app had 1 row of data with 42 columns in it. This limited me because I could not find efficient ways to pull out all the data from the row. For instance: there are 9 columns that are like xxx_state, xxy_state, xzy_state and so on. So getting a query out that showed all the columns that had a state of 1 or 0 was a pain.

 sqlstmt = "SELECT Screen_Location, Temperature_name, Temperature_zip_code, CL_name, CL_state,
 CL_runtime, BText_name, BText_text, BText_State, Picture_name1, Picture_state1, Picture_runtime1, 
Picture_name2, Picture_state2, Picture_runtime2, Picture_name3, Picture_state3, Picture_runtime3, 
Picture_name4, Picture_state4, Picture_runtime4, Picture_name5, Picture_state5, Picture_runtime5, 
Text_name1, Text_state1, Text_runtime1, Text_text1, Text_texteffect1, Text_name2, Text_state2, 
Text_runtime2, Text_text2, Text_texteffect2, Text_name3, Text_state3, Text_runtime3, Text_text3, 
Text_texteffect3 FROM tbl_A_Details WHERE business_GUID='{"& URLBusiness_GUID &"}' 
AND gAD_GUID='{"& URLgAD_GUID &"}' ;" 

So I am attempting to redesign the DB structure so there is 1 state column, 1 name column, 1 type col and so on, (although not all the columns have data in them), with relevant data spread across different rows. About 11 rows match the query because they all are linked by a guid that is in the URL query string.

 sqlstmt = "select business_guid, gAD_guid, creative_guid, creative_name, creative_id, 
creative_runtime, creative_state, creative_type, creative_text_data, creative_text_effects, 
creative_image_name from tbl_advert_setup_creatives where gAD_guid = '{"& URLgAD_GUID &"}' 
order by creative_id asc;" 

I then loop through and build the webpage using the results. In the end there is a single button to update all the values that exist on the page.
How do you pro’s do this?

[Nicodemas]

It might seem like a chore, but can you give me some insight into what the data you are storing is relevant to? The reason I ask is if I can wrap my head around what you are trying to do I'll be able to help at my fullest. I know you are trying to explain it as best you can, but I am having trouble picking up the idea.

For instance, if table tbl_Things had these fields:
*numArms
*age
*birthday
*name

I could say with some confidence that each row in tbl_Things described a attributes of a human. What does each row of your table describe?

[Path9898]

the table holds information for a web based image and text slide show. The slide show duration, images, text, colors and animations are controlled by the settings entered in the page i have been struggling with

So there are the names of 5 different images, times that each images will appear in a web slide show, 3 different pieces of text, 3 text animation names. Then the data is selected and run in the slide show based on the slide show's unique guid. A lot of the data is repeated in many columns in the current version (Picture_name1, Picture_name2, Picture_name3, Picture_name4, Picture_state1, Picture_state2, Picture_state3, Picture_state4) and will be replace by more generic col names likes creative_image_ name, creative_state.
the images are not kept in the DB, just the path's to them. Unfortunately I can't provide a link to show you it, my company would have a fit.

[Path9898]

Nicodemas
I updated my code using your example and it works great! thank you for helping me. I am still keen to know if i am going about this the correct way though.
One issue i found was that if any of my text fields have a sentance in it like "hello, world" the split() chops it up. Is there a way around this?

Path

[Nicodemas]

There is a way! If there is more than one field with the same name attribute in form, that becomes an array automatically through the Request object.

See this code example:

<html>
<body>
   <form method="post" action="_requesttest.asp">
      <input type="checkbox" name="chkTest" value="Alpha" /><br />
      <input type="checkbox" name="chkTest" value="Bravo, Bravo!" /><br />
      <input type="checkbox" name="chkTest" value="Charlie" /><br />
      <input type="checkbox" name="chkTest" value="Delta" /><br />
      <input type="submit" />
   </form>
 
   <h3>Count: <%= request.form("chkTest").count %></h3>
 
   <h3>List:</h3>
   <ol><%
      for x = 1 to request.form("chkTest").count
         response.write "<li>" & request.form("chkTest")(x) & "</li>"
      next
   %></ol>
</body>
</html>
 

See that there are multiple checkboxes with the same NAME attribute. I can access the individual values of all the checkboxes by iterating through the Request.Form("c hkTest") array that was automatically built by the web server.

Notice the array is NOT ZERO BASED! Unlike everything we know to be standard in arrays, the request array starts with one instead of zero. I exploit this by starting my For() loop by setting x = 1, not zero.

This is the amended code. I can't believe I didn't think about this before. I knew about it, but for some reason it skipped my mind. It'll capture your entire form data, and not Split() things unevenly.

'// request.form("theGUIDs")(1) = "{ABC}"
'// request.form("theGUIDs")(2) = "{XYZ}"
 
dim myGUIDs : myGUIDs = request.form("theGUIDs")
 
'// request.form("theValues")(1) = "John Smith"
'// request.form("theValues")(2) = "Sacajawea"
 
dim myValues : myValues = request.form("theValues")
 
for x = 1 to 2
   sSQL = "UPATE myTable SET myName = '"& replace(myValues(x), "'", "") &"' WHERE myID = "& replace(myGUID(x), "'", "")
 
   myConnection.execute(sSQL)
next
 

[Path9898]

Nicodemas, thanks for the update. I tried integrating your examples into my code and don’t think I have done it correctly. While the updated code does function and inserts all 11 rows of updates I am having issues with apostrophes.
Here is my new update statement

for x = 1 to 11
strSQL = "UPDATE tbl_advert_setup_creatives SET  " & _
"creative_runtime = '"& replace(request.form("creative_runtime")(x),"'","") &"', " & _
 "creative_state = '"& replace(request.form("creative_state")(x),"'","") &"', " & _
 "creative_text_data = '"& replace(request.form("creative_text_data")(x),"'","`") &"', " & _
 "creative_text_effects = '"& replace(request.form("creative_text_effects")(x),"'","") &"', " & _
 "creative_image_name = '"& replace(request.form("creative_image_name")(x),"'","") &"' " & _
 "WHERE creative_guid = '"& replace(request.form("creative_guid")(x),"'","") & "';"
  conn.execute(strSQL), lngRecsAffected, adCmdText Or adExecuteNoRecords
 next
 

This seems to be a far cry from your example but it was the only way I could make it work.
Is this a suitable method of doing this?


Apostrophe Issue:
If someone enters "She’s Hot!", the database saves “She” and “s Hot!” in 2 different database cells creating all kinds of fun issues.
I have tried to correct it but replace ‘ with ` (on the tilde key) with

"creative_text_data = '"& replace(request.form("creative_text_data")(x),"’","`") &"', " & _
 

This works but is not ideal.

I also tried this

"creative_text_data = '"& replace(request.form("creative_text_data")(x),"’","(apost)") &"', " & _
 

And then use this at the top of the form after the select statement to replace (apost) with a ’

'strcreative_text_data         = Replace(RS("creative_text_data"),"(apost)","’")
 

This fails because on page load it splits the field at the apostrophe and shows only “She” while the “s Hot!” missing is are all the creative_text_d ata fields because they have been saved into the incorrect cells.
Is this fixable or will i need to continue using the ` (tilde key) replace method?

Thanks

[Nicodemas]

Instead of replacing apostrophes with empty strings, replace it with two apostrophes.

str = replace(str, "'", "''")

[Path9898]

I tired that using

"creative_text_data = '"& replace(request.form("creative_text_data")(x),"'","''") &"',
 

but when I view the database I can see that only one ' gets saved into the cell.
For example if I enter and submit "these cat's rock" I thought it would save "these cat''s rock" in the database, but it saves "these cat's rock" and webpage displays "these cat" only.

What am I missing?