Edit Database Issue

[Guest]

I'm using FP created forms to put data in an Access db. One of the results
wizards I've put together draws the data forward based on a specific field
(JudgeNumber)

With a results wizard, the user is able to select an edit button to go to an
edit page where they may edit the record.

However, in a small number of cases, the record being pulled to the edit
page is NOT the one originally displayed.

After studying on it...I see the POST code for the button only references
one field (EntryNumber) where it actually takes two fields to make the record
unique (EntryNumber and JudgeNumber).


So, I believe my form button has to change from:

<form method="POST" action="../Scoring/editor/edit.asp">
<input type="hidden" name="EntryNumber"
value="<%=FP_FieldHTML(fp_rs,"EntryNumber")%>">
<p><input type="submit" value=" Edit " name="B2"></p>
</form>

to:

<form method="POST" action="../Scoring/editor/edit.asp">
<input type="hidden" name="JudgeNumber"
value="<%=FP_FieldHTML(fp_rs,"JudgeNumber")%>">
<input type="hidden" name="EntryNumber"
value="<%=FP_FieldHTML(fp_rs,"EntryNumber")%>">
<p><input type="submit" value=" Edit " name="B2"></p>
</form>

However, the error still occurs on specific records.

I am thinking that my edit form SQL needs adjustment as well. My theory is
that this code:

<%
fp_sQry="SELECT * FROM ScoreTable WHERE EntryNumber = ::EntryNumber::"
fp_sDefault="EntryNumber=0"
fp_sNoRecords="No records returned."
fp_sDataConn="judging1"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&EntryNumber=3&JudgeNumber=3&UsabilityScore=3&UsabilityComments=203&AppearanceScore=3&AppearanceComments=203&ContentScore=3&ContentComments=203&BonusScore=3&BonusComment=203&SubScore=202&TotalScore=3&LikeMost=203&NeedImprovement=203&"
fp_iDisplayCols=14
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>


Needs the SELECT to actually read:

fp_sQry="SELECT * FROM ScoreTable WHERE EntryNumber = ::EntryNumber:: AND
JudgeNumber = ::JudgeNumber::"

However, if I manually edit the code in the FP code view, it is not saved
since the code was originally produced by a component, it will not save the
data.

However, the page very clearly states that I can't change the database
results wizard either, because that would render the Database Editor
unusuable.

If anyone actually understands what I am trying to accomplish here has an
answer on how to "get around" the component auto regression issue on manual
entry of code, please advise?

Or if you know a way out of the hole, I'd appreciate a bone.

Thanks,
Pam

 

[Kathleen Anderson [MVP - FrontPage]]

Make your changes to the gray colored code, not the maroon, and save your
changes while you're still in HTML/Code view. Depending on your version of
FP, you may need to scroll waaaaaaaaaaay over to the right to find the SQL
statement in the gray colored code.

--

~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

 

[Tom Gahagan]

After studying on it...I see the POST code for the button only references
one field (EntryNumber) where it actually takes two fields to make the
record
unique (EntryNumber and JudgeNumber).

Best to stop here and regroup.

Good database design would dictate that you do NOT use two fields to make a
record unique.

The judge number and info about the judges... assuming there are more than
one judge.... should be extracted to a separate table. In that table a link
to the entry database can be established using the entry number.

Actually.... you really should have a table that stores info on the judges
AND a judges used table that stores the info on what judges scored to a
particular entry. The judges table would define a unique number for each
judge and be linked to the judges used table by the judge number. The judges
used table would be linked to the entry table by the entry number.

Well.... a lot more database design theory than you probably wanted but if
you are going to do it.... you need to do it right!!! < s >

I don't use the results wizard so I'm not sure it can handle the joins
needed for this type of query. It may not.... but it can be done in asp.

Best to you.......
Tom Gahagan

 

[Guest]

Actually.... you really should have a table that stores info on the judges
AND a judges used table that stores the info on what judges scored to a
particular entry. The judges table would define a unique number for each
judge and be linked to the judges used table by the judge number. The judges
used table would be linked to the entry table by the entry number.

Actually, on the advice my db engineers, I have three tables. One is all
entry info, including a unique entry number. One is a judges table, where
each judge has a unique number. The scoring table contains the score values
which are referenced back to the judge and entry numbers, respectively. Each
entry in the score table is unique because no judge will score the same entry
twice. However, each entry will be scored by three judges.

Well.... a lot more database design theory than you probably wanted but if
you are going to do it.... you need to do it right!!! < s >

So based on the above info, would you recommend any changes in future design?

I don't use the results wizard so I'm not sure it can handle the joins
needed for this type of query. It may not.... but it can be done in asp.

The results wizard can handle custom SQL with joins - I just designed the
queries in Access and pasted them into the wizard. The pages are saved as
..asp.

Thanks for your advice...

Pam

 

[Guest]

Kathleen -

Bless you! That exactly solved the dilemma I've been meandering about for
three hours solving!

I love learning new stuff!

Pam

 

[Tom Gahagan]

Actually, on the advice my db engineers, I have three tables. One is all

entry info, including a unique entry number. One is a judges table, where
each judge has a unique number. The scoring table contains the score
values
which are referenced back to the judge and entry numbers, respectively.
Each
entry in the score table is unique because no judge will score the same
entry
twice. However, each entry will be scored by three judges.

So based on the above info, would you recommend any changes in future
design?

You are almost there but as I suggested.... and it is just that... if you
have more than one judge scoring an entry there needs to be a separate table
for the scoring by each judge and linked to the judges info table. You
really must do that or you have to do something in the score table like...

judge_one
judge_two
judge_three
judge_one_score
judge_two_score
judge_three_score

which can work but what happens if you have 4 judges...or one event has 10
judges.... what are you going to do???

In the design that I am suggesting it does not matter whether you have one
judge of 1001 judges.

Anyway..... perhaps I'm picking at a minor point..... just a little of my
old database days jumping out. < s >

Best to you........
Tom Gahagan

 

[Guest]

Minor or not, I do appreciate the insight.

Thanks for the advice.