error inserting

  • Thread starter Thread starter middletree
  • Start date Start date
M

middletree

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?
 
I have what I call a composite table. Can't recall what they
called it in database class,....
Click to expand...

I have see the following terms: Join, Intersection, Linking and Junction table.

But when I selected something from all three dropdowns, it worked fine.
Click to expand...

So, if you select from just one or two dropdowns you get the error, right? If so, it
sounds like your code is attempting to insert nulls into a composite primary key field,
when you have not made selections from all three dropdowns. Recall that a primary key
cannot be null. Also, a composite PK cannot have a null field. In lieu of nulls, do you
have any type of default value set for your dropdowns, or defined at the table level?

Do you have code to check whether the user selected anything from the dropdown boxes, and
attempt the insert operation only if a selection was actually made?

Tom

_____________________________________


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?
 
I forgot to mention that this is an ASP app.

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. Or to have
checkboxes. I have chosen the latter, and thi problem has been overcome.

Thanks!
 
Back
Top