problem when no selection made

[middletree]

Posted this to Access group, meant to do it here:
I have what I call a composite table. Can't recall what they called it in
database class, but it's where you take the PK of two different tables, and
make a new table consisting of only those two fields. That's for
normalization purposes, to resolve a many-to-many situation.

Anyway, I have three dropdowns, which I intentionally gave the same name. I
want people to select one, two, or three items. I know I could just do a
multiple select box, but this is the route I chose, and I believe that my
problem would not be fixed by a multiple box anyway, because when I do a
request.form, I am still going to get a returned value in the form of
(4,6,7), of course, with 4,6,7 being random examples I just made up.

So if a person fills out this form, they are making a new row in the
Personal table, and that gives them an ID, let's say of 12, then I get that
value, and insert these three rows into the composite table:
12,4
12,6
12,7

I should add that the table which will be associated with the 2nd value is a
static table, with values of 1 to 25. So I'm in Access2000, and was working
fine when something was selected from those dropdowns, but if I did not
select from them, I got this:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/grace/shapethankyou.asp, line 135
So I do a response.write and see that I am entering (12,). OK, no problem,
I'll just add a alue of 99, to the first selection in the dropdown:
<option value="99">--Select one--</option>

But that results in this when I do the insert, and did not select anything:
Error Type:
Microsoft JET Database Engine (0x80004005)
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.
/grace/shapethankyou.asp, line 128

So since I am in a test environment, I actually cleared out all tables
(except the static ones), and still got the same error. But when I selected
something from all three dropdowns, it worked fine.

Is there a way to fix this?

[Bob Barrows]

middletree wrote:

Posted this to Access group, meant to do it here:
I have what I call a composite table. Can't recall what they called
it in database class, but it's where you take the PK of two different
tables, and make a new table consisting of only those two fields.
That's for normalization purposes, to resolve a many-to-many
situation.

Anyway, I have three dropdowns, which I intentionally gave the same
name. I want people to select one, two, or three items. I know I
could just do a multiple select box, but this is the route I chose,
and I believe that my problem would not be fixed by a multiple box
anyway, because when I do a request.form, I am still going to get a
returned value in the form of (4,6,7), of course, with 4,6,7 being
random examples I just made up.

So if a person fills out this form, they are making a new row in the
Personal table, and that gives them an ID, let's say of 12, then I
get that value, and insert these three rows into the composite table:
12,4
12,6
12,7

I should add that the table which will be associated with the 2nd
value is a static table, with values of 1 to 25. So I'm in
Access2000, and was working fine when something was selected from
those dropdowns, but if I did not select from them, I got this:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/grace/shapethankyou.asp, line 135
So I do a response.write and see that I am entering (12,). OK, no
problem, I'll just add a alue of 99, to the first selection in the
dropdown: <option value="99">--Select one--</option>

But that results in this when I do the insert, and did not select
anything:
Error Type:
Microsoft JET Database Engine (0x80004005)
The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again. /grace/shapethankyou.asp, line 128

So since I am in a test environment, I actually cleared out all tables
(except the static ones), and still got the same error. But when I
selected something from all three dropdowns, it worked fine.

Is there a way to fix this?

Yes. Validate your data before attempting to insert it. Without seeing your
code I can't be more specific, but simply check the values passed from the
form and only insert them inot the database if they are valid.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[middletree]

I went into Access, into the table called PersonalGift, and removed the PK
designation on both fields, So now it has no PK (temporarily to solve this
issue)

I then did a response.write, and saw that I was trying to run this query:

INSERT into PersonalGift(PersonalID,GiftID) VALUES(211,4)INSERT into
PersonalGift(PersonalID,GiftID) VALUES(211,4)INSERT into
PersonalGift(PersonalID,GiftID) VALUES(211,4)

Of course, that is unaccaptable. I guess the way to handle this is to have
one select, with the ability to select multiples enabled.

thanks


"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message

Yes. Validate your data before attempting to insert it. Without seeing your code I can't be more specific, but simply check the values passed from the
form and only insert them inot the database if they are valid.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

middletree wrote:

I went into Access, into the table called PersonalGift, and removed
the PK designation on both fields, So now it has no PK (temporarily
to solve this issue)
I'm not quite sure why this was necessary, but ... continue. :-)

I then did a response.write, and saw that I was trying to run this
query:

INSERT into PersonalGift(PersonalID,GiftID) VALUES(211,4)INSERT into
PersonalGift(PersonalID,GiftID) VALUES(211,4)INSERT into
PersonalGift(PersonalID,GiftID) VALUES(211,4)

Of course, that is unaccaptable.
I would imagine it would be, even if there were not duplicate values
involved.

Jet is allowing you to run multiple sql statements in a single batch? I
would have thought that you would have received a syntax error ... which is
what happens when I test it:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Missing semicolon (;) at end of SQL statement.

So I put the semicolons in and tested again:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Characters found after end of SQL statement.
/test/test.asp, line 13

So, my recollection that Jet does not allow batched sql statement seems to
be correct. Could it be that you did several response.writes instead of "a
response.write"? I guess this must be the case but ... why are you making me
guess?
I guess the way to handle this is to
have one select, with the ability to select multiples enabled.

Not necessarily. Validate the data! Without seeing the code, I'm still not
sure where the problem lies? When you got the 3 "4" values, had you select
different values on the calling page? Were you expecting there to be 3
different values rather than 3 4's? What were you expecting to see in the
Request?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Peter Foti]

"middletree" <mi********@htomail.com> wrote in message
news:eA**************@TK2MSFTNGP12.phx.gbl...

I have what I call a composite table. Can't recall what they called it in
database class, but it's where you take the PK of two different tables, and make a new table consisting of only those two fields. That's for
normalization purposes, to resolve a many-to-many situation.
In your new table, do you only have these 2 fields? Have you designated
either (or both) of them as the primary key in this new table? The way I
would do it would be to create 3 fields in this composite table, the first
being an ID field (and primary key), the other 2 fields hold the ID values
from your other 2 tables (neither of these fields should be the primary key
in this table).

For example:

Personal
PersonalID (PK)

Gifts
GiftID (PK)

PersonalGift
PersonalGiftID (PK)
PersonalIDRef
GiftIDRef

Note that PersonalIDRef and GiftIDRef are foreign keys to the primary keys
in the respective tables, but they are NOT primary keys in the PersonalGift
table.
Anyway, I have three dropdowns, which I intentionally gave the same name. I want people to select one, two, or three items. I know I could just do a
multiple select box, but this is the route I chose, and I believe that my
problem would not be fixed by a multiple box anyway, because when I do a
request.form, I am still going to get a returned value in the form of
(4,6,7), of course, with 4,6,7 being random examples I just made up.
Do these 3 dropdowns all contain the same values? For example, could the
user select 4,4,4? There are some usability and validation issues that will
need to be addressed, but I will save my comments since you're not asking
about that.
So if a person fills out this form, they are making a new row in the
Personal table, and that gives them an ID, let's say of 12, then I get that value, and insert these three rows into the composite table:
12,4
12,6
12,7

I should add that the table which will be associated with the 2nd value is a static table, with values of 1 to 25. So I'm in Access2000, and was working fine when something was selected from those dropdowns, but if I did not
select from them, I got this:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in INSERT INTO statement.
/grace/shapethankyou.asp, line 135
So I do a response.write and see that I am entering (12,). OK, no problem, I'll just add a alue of 99, to the first selection in the dropdown:
<option value="99">--Select one--</option>

But that results in this when I do the insert, and did not select anything:

Error Type:
Microsoft JET Database Engine (0x80004005)
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again. /grace/shapethankyou.asp, line 128

So since I am in a test environment, I actually cleared out all tables
(except the static ones), and still got the same error. But when I selected something from all three dropdowns, it worked fine.

Is there a way to fix this?

So, you were trying to insert 12,99 into a table where the 99 value has a
relationship to another table which contains records with the values of 1 to
25. 99 does not exist in that table, so you will get an error because your
relationship only allows the values 1 to 25.

The correct approach would be to validate the data and only insert a new
record if it's valid. So if your input name is GiftID and it may look like
any of these:
4,,
,,,
4,,4
4,5,6
,4,
(etc.)

then you should only do the insert if the value is a number between 1 and
25. Here's some psuedo code that might help you out:

' Parse the GiftID to distinguish the 3 selections. Hint: use the Split
function
' Loop through the 3 values. Hint: use a For ... Next loop
' Make sure the value isn't empty
' Insert the value into the database
' End of loop

Presumably your input values came from the database values of GiftID, so you
shouldn't need to check that the value is between 1 and 25 (as long as it's
not empty) before inserting into the database, but if you really wanted to,
you could get the high and low values of GiftID from the database before
inserting and make sure the value is between those values.

Hope this helps.
Regards,
Peter Foti

[middletree]

Comments inline:

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message

news:O1*************@TK2MSFTNGP11.phx.gbl...

middletree wrote:

I went into Access, into the table called PersonalGift, and removed
the PK designation on both fields, So now it has no PK (temporarily
to solve this issue)
I'm not quite sure why this was necessary, but ... continue. :-)

I dunno, I was desparate. When you don't know where the problem is, you try
things

Jet is allowing you to run multiple sql statements in a single batch? So, my recollection that Jet does not allow batched sql statement seems to
be correct. Could it be that you did several response.writes instead of "a
response.write"? I guess this must be the case but ... why are you making me guess?

I don't know if I was making you guess. I didn't put all the needed info
into my post. I'm not perfect.

I was, in fact, doing a response.write in the loop, so it was actually 3
different queries.
Not necessarily. Validate the data!

Of course, I need to put validation in. That's why I was rtunning this test,
because I ddn't know which scenarios that a user might come up wiht which
would lead to errors in the insert. I was testing it without any validation
so I would catching things I might otherwise not catch. However, once hits
error came up, I couldn't figure out why it was an error, so I asked for
help. Then, once I solved it, I shared it here, in case anyone was
interested in the resolution. But I never intended to forego validation.

[Bob Barrows]

middletree wrote:

Jet is allowing you to run multiple sql statements in a single batch?

So, my recollection that Jet does not allow batched sql statement
seems to be correct. Could it be that you did several
response.writes instead of "a response.write"? I guess this must be
the case but ... why are you making me guess?

I don't know if I was making you guess. I didn't put all the needed
info into my post. I'm not perfect.

I did make a couple requests for you to show us the code earlier in this
thread. That's what I was referring to. If I had seen the code, I would not
have jumped to the conclusion I made about the sql. Also, If I could see the
code, I could make suggestions about where and how to validate your data.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[middletree]

Understood and noted.

Thanks for your help.
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:el**************@TK2MSFTNGP11.phx.gbl...

middletree wrote:

Jet is allowing you to run multiple sql statements in a single batch?

So, my recollection that Jet does not allow batched sql statement
seems to be correct. Could it be that you did several
response.writes instead of "a response.write"? I guess this must be
the case but ... why are you making me guess?

I don't know if I was making you guess. I didn't put all the needed
info into my post. I'm not perfect.

I did make a couple requests for you to show us the code earlier in this
thread. That's what I was referring to. If I had seen the code, I would

not have jumped to the conclusion I made about the sql. Also, If I could see the code, I could make suggestions about where and how to validate your data.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"