I am having difficulty getting a persisted recordset update to occur. I am using an asp page to provide a recordset to a dso and am databinding the fields to input tags to allow updates. But when I use the updatebatch method I am getting an error cannot 'update identity column'. I am trying to hide the connection details from the user.

Can anyone help?


<%
    response.expires=0
    response.ContentType = "text/xml"
    response.Buffer = False

    Dim conn
    Dim rst

    strSQL = "select fullname,email,role,nas_analyst_phone,userID,statu s from tblNASAnalyst where role='ANALYST' and userid='E038924'"

    Set conn = Server.CreateObject("ADODB.Connection")
    'conn.Open "driver=sql server;server=cmhnt156.dc.bankone.net;uid=enriquem ;pwd=del1cor;database=NAS"
    conn.open "Provider=sqloledb;Data Source=SQLServer7;Initial Catalog=myDb;User id=myID;password=myPWD;"
    set rst = Server.CreateObject("ADODB.Recordset")

    rst.CursorLocation=adUseClient
    rst.Open strSQL, conn, adOpenKeySet, adLockBatchOptimistic, adCmdText
      rst.ActiveConnection= nothing

    'Instantiate an ADO Stream object to persist the recordset in
    'XML format.

    'Dim objStream
    Set objStream = Server.CreateObject("ADODB.Stream")
    objStream.Mode = 3 ' Read/Write mode
    objStream.Open

    response.Write "<?xml version='1.0'?>" & vbLF

    'Persist the recordset in XML format to the ADO stream object.
    rst.Save objStream, adPersistXML

    'Reposition stream pointer to the beginning of the stream, and
    'return its contents to the client using the Write method
    'of the ASP Response object.

    objStream.Position = 0
    response.write objStream.ReadText

    rst.Close
    Set rst = Nothing

%>

<object classid="clsid:BD96C556-65A3-11D0-983A-00C04fC29E33" ID="dsoNASAnalyst" width="5" height="5">
    <param name="URL" value="datapage.asp" />
</object>

<table datasrc='#dsoNASAnalyst'><tr><td><input datafld='status'><input></td></tr>

<input type="button" value="SAVE" onclick="updateStatus()"></input>

<script language="JavaScript">
<!--
function updateStatus()
{
var stmData = new ActiveXObject("ADODB.STREAM")
var xmlhttp = new ActiveXObject("Microsoft.XMLHTTP")

dsoNASAnalyst.recordset.save (stmData, 1)

xmlhttp.open ("POST", "POSTXML.asp", false)

xmlhttp.send (stmData.readText())
alert (xmlhttp.responseText)
}
//-->
</script>


<%
response.expires = 0

set xmlDOM=server.createobject("Microsoft.XMLDOM")
xmlDOM.load (request)

set stmData=server.createobject("ADODB.Stream")

stmData.open
stmData.WriteText xmlDom.xml
stmData.setEOS

stmData.position=0

set rstData=server.createobject("ADODB.Recordset")
'rstData.CursorLocation=adUseClient

rstData.open stmData ', , adOpenKeySet, adLockBatchOptimistic, adCmdFile
set stmData=Nothing

rstData.ActiveConnection="Provider=sqloledb;Data Source=SQLServer7;Initial Catalog=mydb;User id=myid;password=mypwd;"

rstdata.updateBatch

rstData.close

set rstData= Nothing

%>

You can not update an identity column in a sql server database. Identity columns (autonumber in access) are columns whose value is automatically generated by the database. It's used a lot for primary keys to automatically generate a new and unique value each time. You can't update that column unless you use, in SQL, the code

SET IDENTITY_INSERT table_name ON
   INSERT STATEMENT
SET IDENTITY_INSERT table_name OFF

Updating an primary key column is not a good idea though, as it must always be a unique value.

Let me know if you need any more info!

T