Insert produces error

[Todd]

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table
Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

Todd

[Todd]

Hi,

Found the answer elsewhere but thought I'd share it here in case someone
else has this problem.

Access's upsizing wizard created a trigger on tblAddrTypes which (evidently)
was meant to emulate Access's autonumber functionality. Once I deleted that
trigger, everything worked fine.

Todd
"Todd" <in********@MAPSONgroomery.biz> wrote in message
news:T1*****************@newssvr21.news.prodigy.co m...

Hi,

Using SQL Server 2000 with Windows 2000 Adv Server
&
Microsoft Access linked table (running stored procedure using ADO as
follows:

************************************************** ********
Private Sub cboAddrType_NotInList(NewData As String, Response As Integer)

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim msg As String

On Error GoTo Err_AddrType_NotInList
'Exit the procedure if the combo box was cleared
If Trim(NewData) = "" Then Exit Sub

'Confirm that the user wants to add AddrType
msg = "'" & Trim(NewData) & "' is not in the list." & vbCr & vbCr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
'If the user chose not to add AddrType, set the response
'argument to supress an error message and undo changes.
Response = acDataErrContinue
MsgBox "No record added.", vbOKOnly, "Action Cancelled"
Else
'If the user chose to add AddrType, open a recordset
'using the AddrType table
Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
cnn.Open "Provider=SQLOLEDB;Data Source=penland01;Initial
Catalog=groomery;Integrated Security=SSPI;"

cmd.ActiveConnection = cnn
cmd.CommandText = "spInsertAddrType"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("AddrType", adVarChar,
adParamInput, , Trim(NewData))
cmd.Execute Parameters:=prm
'Set Response argument to indicate that new data is being added
Response = acDataErrAdded

cnn.Close
Set cnn = Nothing
End If

Exit_AddrType_NotInList:
Exit Sub

Err_AddrType_NotInList:
MsgBox Err.Description
Response = acDataErrContinue
************************************************** ********

"NewData" is a text string - in this case "Test"

The stored procedure referenced in the code is:

************************************
CREATE PROCEDURE [spInsertAddrType]
(@AddrType [nvarchar](50))

AS
INSERT INTO [groomery].[dbo].[tblAddrTypes]
([fldAddrType])

VALUES
(@AddrType)
GO
*************************************

When I execute this code, I receive the following error

"Cannot update identity column 'fldAddrTypeID'."

fldAddrTypeID is configured as follows:

***************************
Data Type = int
Identity = Yes
Identity Seed = 1
Identity Increment = 1
***************************

The documentation I've found online concerning this error says that it is
produced when you try to supply a value for an identity field without SET
IDENTITY_INSERT on. Obviously I am NOT specifying a value, so I can't
figure why I'm getting this error.

Thanks for any help you can offer.

Todd