Row and Column in a database table difficulty

Results 1 to 4 of 4

Thread: Row and Column in a database table difficulty

  1. #1
    Noa Guest

    Default Row and Column in a database table difficulty

    I have a database table with 39 rows (beginning with MA and ending with BM) with each row having 35 columns (beginning with 01 and ending with 35). Each cell will contain either 0 or 1. <BR><BR>Here&#039;s my question...<BR><BR>how can I query the exact row and column by refering to the row and column name without have to write an IF-then-elseif statement 35 times for each row?<BR><BR>For eample: a customer fills out a form and the conclusion are these variables...<BR><BR>TKT1a = "MC"<BR>TKT1b = "03"<BR>PRO = "Table name"<BR>strConnect = "DNS-less connection string"<BR><BR>code...(this works)<BR><BR>Set oRS1 = Server.CreateObject("ADODB.Recordset")<BR>oRS1.Ope n PRO, strConnect, adOpenKeyset, adLockOptimistic<BR>oRS1.Find "Row = &#039;" & TKT1a & "&#039;"<BR> If TKT1b = 01 then<BR> oRS1.Fields("01") = 1<BR> elseif TKT1b = 02 then<BR> oRS1.Fields("02") = 1<BR> elseif TKT1b = 03 then<BR> oRS1.Fields("03") = 1<BR><BR>etc. 35 times.<BR><BR><BR>To write this to the database I would have to write 35 instances of If-then-elseif statements. My code would be incredible long if this customer picks 20 selections and gives me 20 sets of these variables.<BR><BR>I have tried this (and other keysets)...<BR><BR>Set oRS1 = Server.CreateObject("ADODB.Recordset")<BR>oRS1.Ope n "Select " & TKT1b & " From " & PRO & " Where Row = &#039;" & TKT1a & "&#039;", strConnect, adOpenKeyset, adLockOptimistic<BR> oRS1.Fields("*") = 1<BR> oRS1.Update<BR>oRS1.Close<BR>Set oRS1 = Nothing<BR><BR>but I get this error...<BR><BR>ADODB.Recordset error &#039;800a0cc1&#039; <BR>Item cannot be found in the collection corresponding to the requested name or ordinal<BR><BR>Is there a way to do this (probably something simple that I keep overlooking)?<BR><BR>Thanks....<BR>

  2. #2
    Tim Snyder Guest

    Default RE: Row and Column in a database table difficulty

    oRS1.Fields("& TKTib &") = 1 <BR><BR>I would have given more but I&#039;m away home... Hope this gives you something to think about

  3. #3
    Join Date
    Dec 1969

    Default RE: Row and Column in a database table difficulty

    Why don&#039;t you use an update statement, in which case there is no need for a recordset at all<BR><BR>sql = "update " & pro & " set " & tk1b & " = 1 where row = &#039;" & _<BR> tk1a & "&#039;"<BR>set conn = server.createobject("adodb.connection")<BR>conn.op en strConnect<BR>conn.execute sql,,128<BR>conn.close<BR>set conn = nothing<BR><BR>If you are adamant about the recordset you could use this<BR><BR>oRS1.Fields(0) = 1<BR>

  4. #4
    noa Guest

    Default RE: Row and Column in a database table difficulty

    Thanks a lot... that worked great. <BR><BR>I am relatively new to asp but I consumed 3 large books from wrox and have mostly been able to do evrything I need. Each time I have come to this message board my questions have been answered and my progress greatly enhanced. I want to thank you for your input. I am a very good artist and Flash developer... if you need anything in that line let me know and I will do what I can...<BR><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