dynamic update statements?

Results 1 to 3 of 3

Thread: dynamic update statements?

  1. #1
    todw17 Guest

    Default dynamic update statements?

    I have a table with over 50 fields in it. I am wondering if there is a way to write the SQL update statement dynamically, so that I wouldnt have to write out an update statement with 50 fields and 50 values. Let me try to explain it with an example. <BR><BR>Suppose I have a Baseball Card Club. In this club, we want to be able to look and see what cards each member needs to complete their set. Let&#039;s say there are 50 baseball cards in the set. I have a form set up with the list of the 50 cards with a checkbox next to each name. Each member of the club would then enter into the form all of the cards that they already had. This would be entered into a table with the 50 cards acting as the fields, and the member name would be the record. <BR><BR>The checkboxes are named "checkboxCard1", "checkboxCard2", etc... so that I can use a loop to write out reports and things. My question arises when I want to go ahead and insert this information into the table. Insead of having to write:<BR>-------------------------------------------------------------<BR>SQL_insert = "INSERT INTO 50cardset(memberName, card1, card2, card3,.....,card 50) values (&#039;" & nameFromForm & "&#039;, &#039;" & checkboxCard1 & "&#039;, &#039;" & checkboxCard2 & "&#039;, ......, &#039;" & checkboxCard50 & "&#039;)&#039; <BR>-------------------------------------------------------------<BR>I would like to be able to do something like this:<BR>-------------------------------------------------------------<BR>SQL_insert = "INSERT INTO 50cardset (memberName) values (&#039;" & John Doe & "&#039;)&#039;<BR>SQL_update = "UPDATE 50cardset " & _<BR> "SET 50cardset.card(counter) = &#039;" & checkbox(counter) & "&#039; " & _<BR> "WHERE (((50cardset.memberName)= " & nameFromForm & "))" <BR><BR>for counter = 1 to 50<BR>connBaseball.Execute(SQL_update, counter)<BR>next<BR>-------------------------------------------------------------<BR>I realize that what I have above wont work. It was just an attempt at trying to explain what I want to do. If anyone has any ideas, I&#039;d glady accept any suggestions. <BR><BR>If I havent made myself clear enough, let me know and I&#039;ll try to explain myself better.<BR><BR>Thanks in advance - Todd<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: dynamic update statements?

    Yes, you could do that (with a couple of minor changes to the syntax to fix your bugs), but it would be 50 times slower. SURELY you don&#039;t want that???<BR><BR>This is a case where ugly VBS code is worth it.<BR><BR>Of course you *could* use a loop in VBS to generate the SQL. *THAT* might be worth doing.<BR><BR>

  3. #3
    todw17 Guest

    Default RE: dynamic update statements?

    Bill - <BR>I didnt think about execution time until after I posted earlier. I did see the disadvantage of using the loop. <BR><BR>I will look into the VBScript loop to generate the SQL. It might be more work for me to figure that out than it would be for me to just write the ugly code. But I&#039;d rather learn something new than spend all that time doing it the other way.<BR><BR>Thanks for the help.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts