Trouble with SQL Server Records Updation

swornavidhya_m

Problem we are Facing Currently: Records getting inserted as only one during the process of updating the server. Previously, all the given records get inserted into the database table during the updation process. "updateprice" is the .mdb exists locally in the same folder where the calling file for updating records exists. It contains record data to be uploaded to the server database. It acts as an temporary storage database. The records to be updated to the server are either in an notepad or excel. Whatever be the count in records, previously it was updated fine. "rsTextData.Update" is line 86. I'm blinking where it fails.The error which we're facing and the code is given below for the assistance.

Error Showing as
****************
1 record(s) converted
File Name:DTRinggit081110Ringgit Extends Downtrend Against Greenback
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.

/data_maintain/News.asp, line 86

code ****

<tr><td height="25" class="mtext" background="../images/top_bar.jpg" align="center">ADMINISTRATION</td></tr>
<tr><td height="10"></td></tr>
<tr><td height="20"></td></tr>

<tr><td width="100%" class="mtext">

<center>
 Uploading Text Data in progress... 
 Please wait..
</center>

<%
set myconn=server.createobject("ADODB.connection")

myconn.open "updateprice"
myconn.CommandTimeout=0
set rsData = Server.CreateObject ("ADODB.Recordset")

NewsCodeSelectSql="Select \* from text\_data"
set rsData=myconn.execute(NewsCodeselectsql)
code = 0 
intRecCount = 0
set myconn1=server.createobject("ADODB.connection")
myconn1.open "DSN=commodities;uid=fbspl07;pwd=07#web&;"
myconn1.commandtimeout=0
Response.Write("1  record(s) converted<br>")
do while not rsdata.eof
Set cmdTemp=Server.CreateObject("ADODB.Command")
cmdTemp.CommandType=2
	sql="select \* from text\_Data where 1<>1"
	
	set rsTextData=Server.CreateObject("ADODB.Recordset")
	
	rstextdata.locktype=adLockOptimistic
	
	rsTextData.Open "s

Lost User

swornavidhya_m wrote:

Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.

There's already a record in the "text_data" table that holds the same value in the "Text_Id" field as the one that you're trying to insert. That fails, since a primary key needs to be unique; you can't add two records in the table with the same key. Do a SELECT on the table, using the value from rsdata(0) - you'll see that it's already in there.

I are Troll :suss:

swornavidhya_m

Eddy Vluggen wrote:

There's already a record in the "text_data" table that holds the same value in the "Text_Id" field as the one that you're trying to insert. That fails, since a primary key needs to be unique; you can't add two records in the table with the same key.

Actually, the datas stored in notepad or excel. Then those stored values are copied to "updateprice.mdb". The values from "updateprice.mdb" are transferred to the "text_data" table in server with the method called for updating the values to the server. In this method only i'm facing the problem. The "Text_Id" field is the unique one and won't have any duplicates in any case. Also, the data team members who are in-charge for inserting datas to server, said that the server updation by inserting is done properly when the record count<=3. If the recordcount exceeds 3, then the error is showing as:

Error Showing as
****************
1 record(s) converted
File Name:DTRinggit081110Ringgit Extends Downtrend Against Greenback
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_text_data'. Cannot insert duplicate key in object 'text_data'.

/data_maintain/News.asp, line 86

I'm blinking and stuck with datas getting updated to the server. Kindly help me.

M.Sworna Vidhya

Lost User

swornavidhya_m wrote:

The "Text_Id" field is the unique one and won't have any duplicates in any case.

You'll get the error that you described if you try to insert one that's already there.

swornavidhya_m wrote:

If the recordcount exceeds 3

What's the value of the PK-field (Text_Id) in those three records?

I are Troll :suss:

swornavidhya_m

The value of TEXT_ID will be as like 1779562, 1779563, 1779564, and so on.

Lost User

There are three records already in the database you said. What are *those* values? (List them all three please) And what is the value of the PK-field of the record that you're inserting?

I are Troll :suss: