error when inserting same data into database

hello,
im having a problem with my sql statement. i have an insert statement
that inserts new "comments" into my database, but every so often, the
data that needs to be inserted is the same "comment" that is already in
the database. this produces an error. i want the statement to add a
new comment if it's not there, and to override or skip over the
comments that already exist in the database. here's my code:
'Insert New Comments Into Comments
strSql = "INSERT INTO Comment ( [Order], Comment, CommentDate,
Commentator ) " & _
"SELECT [Order Log].ID AS [Order], Iris_Comments_Temp.Comment,
Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
"FROM Iris_Comments_Temp INNER JOIN [Order Log] ON
Iris_Comments_Temp.[TRACKER NO] = [Order Log].[TRACKER NO];"
objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
If Err < 0 Then
MsgBox "Insert Comments: " & Err.DESCRIPTION
Err.Clear
End If
'Insert Comments Into Comments_History
strSql = "INSERT INTO Iris_Comments_History ( [Order], Comment,
CommentDate, Commentator ) " & _
"SELECT [Order Log].ID, Iris_Comments_Temp.Comment,
Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
"FROM [Order Log] INNER JOIN Iris_Comments_Temp ON [Order
Log].[TRACKER NO]=Iris_Comments_Temp.[TRACKER NO];"
objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic
If Err < 0 Then
MsgBox "Insert Comments History: " & Err.DESCRIPTION
Err.Clear
End If
any help would be outstanding!
thanks!!!
rickWhat is the error you are getting? The most likely suspect is that you are
inserting duplicate values into a column with a primary key or unique index
defined. Logically, it would seem that [Order], [CommentDate] would be the
primary key, but it's a crazy world and the database developer may have
placed the key on [Comment].
<RSummersJr@.gmail.com> wrote in message
news:1135025948.470130.281510@.o13g2000cwo.googlegroups.com...
> hello,
> im having a problem with my sql statement. i have an insert statement
> that inserts new "comments" into my database, but every so often, the
> data that needs to be inserted is the same "comment" that is already in
> the database. this produces an error. i want the statement to add a
> new comment if it's not there, and to override or skip over the
> comments that already exist in the database. here's my code:
> 'Insert New Comments Into Comments
> strSql = "INSERT INTO Comment ( [Order], Comment, CommentDate,
> Commentator ) " & _
> "SELECT [Order Log].ID AS [Order], Iris_Comments_Temp.Comment,
> Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
> "FROM Iris_Comments_Temp INNER JOIN [Order Log] ON
> Iris_Comments_Temp.[TRACKER NO] = [Order Log].[TRACKER NO];"
> objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
> adLockOptimistic
> If Err < 0 Then
> MsgBox "Insert Comments: " & Err.DESCRIPTION
> Err.Clear
> End If
> 'Insert Comments Into Comments_History
> strSql = "INSERT INTO Iris_Comments_History ( [Order], Comment,
> CommentDate, Commentator ) " & _
> "SELECT [Order Log].ID, Iris_Comments_Temp.Comment,
> Iris_Comments_Temp.CommentDate, Iris_Comments_Temp.Commentator " & _
> "FROM [Order Log] INNER JOIN Iris_Comments_Temp ON [Order
> Log].[TRACKER NO]=Iris_Comments_Temp.[TRACKER NO];"
> objRS.Open strSql, CurrentProject.Connection, adOpenForwardOnly,
> adLockOptimistic
> If Err < 0 Then
> MsgBox "Insert Comments History: " & Err.DESCRIPTION
> Err.Clear
> End If
>
> any help would be outstanding!
> thanks!!!
> rick
>|||well...what i think is happening is that the person is trying to
re-save the same comments, and then the insert statement produces an
error because there are two of the same comments in the database...the
error is:
Insert Comments History: 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.
I want the comment to override the existing comment instead of adding
the same comment again. any help would be great...thanks!
rick|||So you want your INSERT to become an UPDATE, right?
You need to prevent an insert if the key already exists and instead update
the values. Please post DDL and sample data to get better help.
ML
http://milambda.blogspot.com/|||Ask whomover designed the database what primary keys, unique indexes or
constraints are defined on the table. Without that information, it's hard
for a developer to code against it.
<RSummersJr@.gmail.com> wrote in message
news:1135030402.665994.163870@.g47g2000cwa.googlegroups.com...
> well...what i think is happening is that the person is trying to
> re-save the same comments, and then the insert statement produces an
> error because there are two of the same comments in the database...the
> error is:
> Insert Comments History: 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.
> I want the comment to override the existing comment instead of adding
> the same comment again. any help would be great...thanks!
> rick
>