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]

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

 

[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]

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

 

[middletree]

Comments inline:

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]

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

 

[middletree]

Understood and noted.

Thanks for your help.

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"