Updating and form help

jackiew · January 8th, 2009, 04:00 PM

I have a simple form that collects names in time slots. The form lists all the time slots with the names and blank spots available.

The problem I'm running into is people are overwriting other names so they can get a time slot they want.
The code I have is:

Code:

Set rs = Server.CreateObject("ADODB.RecordSet")
SQL = "SELECT * FROM OpenEnrollment2 WHERE ID = '1'"
rs.Open SQL, conn, AdOpenStatic, AdLockOptimistic
If request("Submit") <> "" then
SQL = "UPDATE OpenEnrollment2 SET "
SQL = SQL & "C1 = '" & replace(request("C1"), "'", "`") & "', "
SQL = SQL & "C2 = '" & replace(request("C2"), "'", "`") & "', " 
SQL = SQL & "C3 = '" & replace(request("C3"), "'", "`") & "', " 
SQL = SQL & "C4 = '" & replace(request("C4"), "'", "`") & "', " 
SQL = SQL & "C5 = '" & replace(request("C5"), "'", "`") & "', " 
SQL = SQL & "C6 = '" & replace(request("C6"), "'", "`") & "', " 
SQL = SQL & "C7 = '" & replace(request("C7"), "'", "`") & "', " 
SQL = SQL & "C8 = '" & replace(request("C8"), "'", "`") & "', " 
SQL = SQL & "C9 = '" & replace(request("C9"), "'", "`") & "', " 
SQL = SQL & "C10 = '" & replace(request("C10"), "'" "`") & "', " 
SQL = SQL & "WHERE ID = '" & rs("ID") & "'"
conn.Execute SQL
response.redirect("confirm.asp")
End If

And the listing code is:

Code:

<input type="text" name="C1" size="20" value="<% = rs("C1") %>">

I'm new at this, so this may not be the way to do this type of form, but it is what I came up with. I have an established record in the database (ID = '1') and just have the form update that dataset.

I want to make it so Person2 cannot overwrite Person1's name when Person2 goes to sign up for a time slot.

So towards that, I updated the Listing code to:

Code:

If rs("C1") = "" then 
response.write "<input type=""text"" name=""C1"" size=""20"">"
Else
response.write rs("C1")
End If

It does what I want it to do for the form, if the field is filled in it shows as just the person's name, no text box. If the field isn't filled in, then it shows the text box.

The problem is when I submit a new name, the other names already on the form disappear. I'm sure this is because I'm updating the dataset, but there has to be some way I can update the dataset and still keep the current records intact.

I don't want everyone to have their own record, and I just can't seem to understand Join and InnerJoin, so this is what I'm stuck with.

Could someone help a newbie??

Old Pedant · January 8th, 2009, 06:06 PM

Well, your fundamental problem is a very bad DB design.

You should have *ONE* record per timeslot. And then a *SEPARATE* table with all the timeslots.

You can see a demo of something very much like this in the Calendar demo that I have here:
http://www.ClearviewDesign.com/Newbie

[That calendar allows multiple events per day. To be equivalent to what you want, I'd just change it to allow only one per day.]

But anyway, to fix your IMMEDIATE problem--until you have time to go back and rethink your DB design--you could just change your "if" code to this:

Code:

<%
...
If rs("C1") = "" then ro = "" Else ro = "readonly"
%>
<input type="text" name="C1" <%=ro%> size="20" value="<%=RS("C1)%>">
...

That will convert all existing slots to READONLY fields. Presto.

***********************

By the way, you can simplify the hell out of your UPDATE code, thus:

Code:

...
currentID = 1 ' or get it from a form field or or or
...
If request("Submit") <> "" then
    sets = ""
    For c = 1 To 10 
        cfld = "C" & c
        sets = sets & ", " cfld & " = '" & replace(request(cfld), "'", "`")
    Next
    SQL = "UPDATE OpenEnrollment2 SET " & Mid(sets,2) & "WHERE ID = " & currentID
    conn.Execute SQL
    response.redirect("confirm.asp")
End If
' not updating, so display:
SQL = "SELECT * FROM OpenEnrollment2 WHERE ID = " & currentID
Set rs = conn.Execute( SQL )
%>
<table>
<%
For c = 1 To 10
   cfld = "C" & c
   val = rs(cfld)
   If val = "" then ro = "" Else ro = "readonly"
%>
<tr>
    <td>Slot <%=c%></td>
    <td>
        <input type="text" name="<%=cfld%>" <%=ro%> size="20" value="<%=val%>">
    </td>
</tr>
<%
Next
%>
...

I'm assuming that eventually you will replace the constant 1 (used to select a single ID) with a variable coming from someplace, so I changed to use currentID for illustration purpose.

I'm also assuming that your ID field is a NUMBER (since you used the value '1'), in which case you should *NOT* enclose the value in apostrophes.

Okay?

jackiew · January 9th, 2009, 10:47 AM

Thank you, that worked perfectly.

I would like to talk about DB design. I'm very bad at it (as you can see). So, with no resources but me, books and the internet, how do I get better at it? With knowing so little, and teaching myself, it's very hard to see into the future and know what I should do. I want to not struggle with the little things, and to have someone come to me with a project and I have a good idea on how to program it. Instead of what I'm doing now of figuring out each little piece, fighting them together and then getting stymied on one fairly simple piece of the program, like my problem here.

I tried a local community college for ASP.NET. I paid for a one-on-one course and it was a joke. The instructor taught himself the lesson the night before he taught it to me. You can see I really didn't learn anything since I'm still trying Classic ASP.

How do I learn this correctly? Or am I dreaming that I can learn this without a proper education?

Thanks

Old Pedant · January 9th, 2009, 06:15 PM

You are asking the wrong person, since I'm completely self-taught.

Oh, I've read many articles on DB design, but I've never actually even read all the way through any book. Mosly just learning by doing.

Heck, I didn't even start out with relational databases! (Unless you count DBase III as relational...which it isn't, really.) I started out writing the internals of an object oriented database, without ever first learning query language(s).

My "problem" may be that I love computer languages. I've helped write several (mostly various dialects of BASIC, but one C compiler and one C++ preprocessor) and wrote two (a very primitive BASIC and an assembler) pretty much by myself. So when I found SQL it was almost love at first sight. Come to think of it, I think I learned the fundamentals of SQL by looking at the code for a DB engine that was written in C++ (or was it Java??? Ehhh...close enough to the same it doesn't matter). It was a demonstration of how relational databases worked, so I learned about how the theory and the practice differ. But anyway...not much help to you, I'm afraid.