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
>

Error when inserting datetime in german SQL Server 2000

Hello!
I have a program that are using a stored procedure to insert a row in
an SQL Server. During several years this program has worked fine using
an English version of SQL server. Now a customer wants to use a german
SQL server, but it doesn't work. The row is never inserted. I get the
following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
Konvertieren von Datentyp varchar in datetime.
Now, if I change the language setting to english in the "SQL Server
Login properties for 'sa'" it all works. If I restore the language
setting to german in the "SQL Server Login properties for 'sa'", it
all continues to work until I re-boot the PC. When I have re-booted, I
once gain end up with the error that I have described before.
It seems strange to me that SQL Server doesn't behave in a consistent
way. Sometimes it works with german language settings, sometimes it
doesn't.
Any explanations would be very appriciated.
BR / PatrikThis is because dateformat changes according to language settings.
date format considerations while insert and update:
You can SET DATEFORMAT and modify code as follows.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
set dateformat dmy --change dateformat
insert into test values(convert(datetime,@.dt)) ;
select * from test;
go
OR other method would be to use explicit coversion of the
date using CONVERT function.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
insert into test values(convert(datetime,@.dt, 103)) ;
select * from test;
go
--
-Vishal
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Patrik,
Yes, language and localizations are a problem to deal with. You might find
this topic helpful:
Writing International Transact-SQL Statements in the MSDN at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp
You can also manage this by setting the language of the connection that is
transmitting data. For this use: SET LANGUAGE xxx for a connection or use
sp_defaultlanguage to set the default for a login.
Russell Fields
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no confusion
for people *or* the software.
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Only YYYYMMDD without any separators works always:
SET DATEFORMAT dmy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
SET DATEFORMAT mdy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
I can see that you as an American are not that used to dealing with date
formats as I as a European am ;-)
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uame7U9PDHA.2768@.tk2msftngp13.phx.gbl...
> Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no
confusion
> for people *or* the software.
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0307010514.b59427d@.posting.google.com...
> > Hello!
> >
> > I have a program that are using a stored procedure to insert a row in
> > an SQL Server. During several years this program has worked fine using
> > an English version of SQL server. Now a customer wants to use a german
> > SQL server, but it doesn't work. The row is never inserted. I get the
> > following error:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> > Konvertieren von Datentyp varchar in datetime.
> >
> > Now, if I change the language setting to english in the "SQL Server
> > Login properties for 'sa'" it all works. If I restore the language
> > setting to german in the "SQL Server Login properties for 'sa'", it
> > all continues to work until I re-boot the PC. When I have re-booted, I
> > once gain end up with the error that I have described before.
> >
> > It seems strange to me that SQL Server doesn't behave in a consistent
> > way. Sometimes it works with german language settings, sometimes it
> > doesn't.
> >
> > Any explanations would be very appriciated.
> >
> > BR / Patrik
>|||> I can see that you as an American are not that used to dealing with date
> formats as I as a European am ;-)
Hey, don't call me an American... I'm a Canuck, just ask Tom...
:-Psql

Error Validating Formula for Column

Hi,
I have three fields APPLICANT_FIRST_NAME, APPLICANT_LAST_NAME and
APPLICANT_NAME
While designing table in sql enterprise manager I am inserting
"APPLICANT_FIRST_NAME & APPLICANT_LAST_NAME" for Formula Value of
APPLICANT_NAME field
But I am getting the following error
"Error Validating Formula for Column"
I need a field APPLICANT_NAME that has both values APPLICANT_FIRST_NAME and
APPLICANT_LAST_NAME in it
Can someone help me
Thanks
PonnurangamHi
Your question is answered in .programming group. Don't multi-post.
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
news:eSljuKsqEHA.3416@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have three fields APPLICANT_FIRST_NAME, APPLICANT_LAST_NAME and
> APPLICANT_NAME
> While designing table in sql enterprise manager I am inserting
> "APPLICANT_FIRST_NAME & APPLICANT_LAST_NAME" for Formula Value of
> APPLICANT_NAME field
> But I am getting the following error
> "Error Validating Formula for Column"
> I need a field APPLICANT_NAME that has both values APPLICANT_FIRST_NAME
and
> APPLICANT_LAST_NAME in it
> Can someone help me
> Thanks
> Ponnurangam
>