Trouble with SQL Server Records Updation

[swornavidhya_m]

Hai,

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 Part

<table width="100%" cellspacing="0" cellpadding="0" align="center">

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

<tr><td width="100%" class="mtext">
<p style="margin-left: 10; margin-right: 3">
<center>
    <b> Uploading  Text Data in progress...<br>
	<br>Please wait..</b>
</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 "select * from text_Data where 1<>1",myconn1 
		msgbox rsTextData.RecordCount 
		rsTextData.Addnew
		 	response.write "File Name:" &rsdata(3)
		   'response.write "Title:      " &rsdata(4)&"<br>"
		   
			rsTextData("Text_id")=rsdata(0)
			rsTextData("Date1")=rsdata(1)
			rsTextData("Comname_id")=rsdata(2)
			rsTextData("Filename")=rsdata(3)
			'response.write "Title= " & rsdata(4) &"<br>"	
			title1=replace(rsdata(4),"("," ")
			title1=replace(title1,")"," ")
			title1=replace(title1,":","-")
			response.write title1
			'response.end
			rsTextData("Title")=title1
			
			'rsTextData("Title")= rsdata(4)
			rsTextData("Source")=rsdata(5)
			rsTextData("isspot")=rsdata(6)
			rsTextData("Module_type")=rsdata(7)
			rsTextData("Submodule_type")=rsdata(8)
			rsTextData("Time1")=time()
		rsTextData.Update 

		rsTextData.close
		rsData.movenext
		code = code + 1
		If code mod 25 = 0 then
		  Response.Write(code &"  records converted<br>")
		end If  
	
	loop
	

	response.WRITE("<br><br><b> Text Data is Inserted Successfully....</b><br><br>")
	
%>



<p align="RIGHT"><a href="javascript:history.back()" target="_parent"><font face="Arial" size="2">Back</font></a>
&nbsp;
</td></tr>

<tr><td height="20"></td></tr>

</table>

M.Sworna Vidhya

[tesuji]

Well, you are working on MS SQL Server.

The error message:

Violation of PRIMARY KEY CONSTRAINT 'PK_text_data'. Cannot INSERT duplicate key IN object 'text_data'.

means that the new row you are trying to insert in table text_data already exists. That happens if one inserts same data twice.

You can examine this by using sql server management console and query table text_data, for example:

select * from text_data;

The output can then be verified with the notpad or excel data. (If text_data is too large, you may reduce selected data by adding a where clause above.)

-- tesu

[swornavidhya_m]

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

[tesuji]

Well, you should carefully read my posting. The cited error message is very clear.

You chould verify the old content of SQL Server table "text_data" with the content of "updateprice.mdb" which you are trying to insert into table "text_data". If primary-key column "Text_Id" of table "text_data" already contains values of the corresponding column of "updateprice.mdb", there is a "Violation of PRIMARY KEY constraint 'PK_text_data'".

If there aren't any duplicates, well, then it will become more difficult. The mess is that '80004005' is a common error number what stands for a fair quantity of individual errors!

You can get further detail error information by enabling the error log of your ODBC datasource. This can be done by starting MS ODBC management tool, checking the log mark and entering a file name where the log data should be written to.

-- tesu