Re: Primary key error
- From: Eric <Eric@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 26 May 2009 23:48:01 -0700
Hi,
Each column of data is just a VARCHAR store, except the one for ID, that is
a BIGINT with auto numbering.
The column names as used in the code below, are put in as strings.
So: GeneralLog.YPcode is a string for "yp_code". I use this to be
constistent with column names thoughout my code.
So to explain again:
Step 1: I read the data from the sql server with a basic SELECT command.
It's put in a dataset and then linked to textboxes and checkboxes.
step 2: the user changes the data
step 3: When the user clicks on the finish button, the program first makes a
backup for each row where the column YPCODE has the value of the read data
from step 1.
step 4: the changed data is written to the sql server as a new record.
So this means after these steps I would have two records in the tables, ie:
columns: YPcode, data
record 1: test_BAK, testdata
record 2: test, changedtestdata
For the tables where YPcode is the primary key, this is not a problem, but I
have some tables which are allowed to have more then one line for the same
YPcode, so they have an extra column ID, which is an Identy column with
autoincrement of 1.
When inserting the new record with the changed data into these tables, gives
the exception error:
{"Violation of PRIMARY KEY constraint 'PK__OverallS__3214EC2775435199'.
Cannot insert duplicate key in object 'dbo.OverallStatus'. The statement has
been terminated."}
My program stops then in de debug mode, if I then step it through, the
record is added and it continues fine.
It seems like I need to wait (?) or do some other command to have the server
deal with the record?
Do I have to do something like for a dataset: acceptchanges?
how do I solve this?
rg,
Eric
"Russell Fields" wrote:
Eric,.
Your notes below do not make it clear to me what you are doing with each
column of data.
I would suggest that you use SQL Server Profiler to trace a problem case and
capture the SQL statements that are issued. I see that you are building a
string of SQL code for the update, so if you create the INSERT the same way
the profiler trace should show all the parameters that you are inserting.
Looking at that might help you.
To help somebody else duplicate your problem, follow the guidelines at:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
RLF
"Eric" <Eric@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D1637D50-833A-4E34-A917-38C2B48010C4@xxxxxxxxxxxxxxxx
Hi,
In my VB.NET program the user can update a bunch of information.
Just before the update is put in the database, I first change one field by
putting _bak behind the text with:
mySQL = "UPDATE " + Project.GeneralLog + " SET " + GeneralLog.YPcode + " =
" + GeneralLog.YPcode + "+ '_BAK' "
mySQL += "WHERE " + GeneralLog.YPcode + " = '" + ypcode + "'"
myCMD = New SqlCommand(mySQL, conn)
Try
myCMD.ExecuteNonQuery()
Catch ae As SqlException
conn.Close()
Return False
End Try
After that I use INSERT TO to put the the changed data in.
In most tables it goes ok because the primary key is the field that gets
changed, but in the tables that have a seperate ID field to be the primary
key (because the field that is primary in the other tables can have double
entries in this table).
For these tables I get an error:
{"Violation of PRIMARY KEY constraint 'PK__OverallS__3214EC2775435199'.
Cannot insert duplicate key in object 'dbo.OverallStatus'. The statement
has
been terminated."}
The table is created with:
mySQL = "CREATE TABLE " + Project.OverallStatus + " (ID BIGINT
IDENTITY(1,1) PRIMARY KEY, " + OverallStatus.YPcode + " VARCHAR(15), "
mySQL += OverallStatus.Datum + " DATETIME, "
mySQL += OverallStatus.Description + " VARCHAR(5000))"
myCMD = New SqlCommand(mySQL, conn)
Try
myCMD.ExecuteNonQuery()
Catch ae As SqlException
nOK = True
End Try
The INSTERT INTO command I use, is the same one as for putting the data in
the table in the first time, so that is working properly.
What am I doing wrong?
rg,
Eric
- Follow-Ups:
- Re: Primary key error
- From: John Bell
- Re: Primary key error
- References:
- Primary key error
- From: Eric
- Re: Primary key error
- From: Russell Fields
- Primary key error
- Prev by Date: Re: varchar and performance
- Next by Date: Re: Primary key error
- Previous by thread: Re: Primary key error
- Next by thread: Re: Primary key error
- Index(es):
Relevant Pages
|
