Insert produces error

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.

ToddHi,

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" <infoNOSPAM@.MAPSONgroomery.biz> wrote in message
news:T1j5e.11405$FN4.303@.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