SQL INSERT Problem

[gmagerr]

Hi guys, i have an application i'm working on that will add items to an access database. The INSERT Works fine until i try to insert something that's already been inserted, then i get this error

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] 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.
/Telmon/Intranet/PSS_Old2/InsertMaterial.asp, line 24

here's the code i'm using for the insert.

<%
Session(&quot;Qty&quot = Request(&quot;Qty&quot
Session(&quot;ProductID&quot = Request.Form(&quot;ProductID&quot
Session(&quot;JobCode&quot = Request(&quot;JobCode&quot


JobCode = Session(&quot;JobCode&quot
qty = Session(&quot;Qty&quot
prodID = Session(&quot;ProductID&quot

strSQL = &quot;INSERT INTO Orders (ProductID, JobCode, Quantity) VALUES (&quot; & prodID & &quot;, &quot; & JobCode & &quot;, &quot; & qty & &quot&quot;

Set cnMat = Server.CreateObject(&quot;ADODB.Connection&quot
cnMat.Open &quot;DSN=PSS&quot;
cnMat.Execute(strSQL)

Response.Redirect &quot;DisplayQty.asp?JobCode=&quot; & JobCode & &quot;&ProductID=&quot; & prodID

cnMat.Close
Set cnMat = Nothing
%>


Thanks in advance.

 

[durug]

Probably you have to check your database(table) constraints.
Like duplicating the primary key, and so on ..

 

[Guest_imported]

Hey
I think it's a database error.... try checking your database to make sure that field allows for duplicate errors.... also make sure it isn't indexed.

 

[Jorgandr]

I would definetly check your database. But it sounds like your table is going to need 2 primary keys (ProductID and JobCode) Maybe you just forgot to assign both of them as the primary key

 

[rico14d]

Its always best to have a primary key that has no important information in it.

try creating a column called order_no and define it as an autonumber. This will enforce referential integrity on the table.