Someone was kind enough to point me to
http://support.microsoft.com/default...b;en-us;218751, which
basically says that it is a known bug and the only solution is to use the
SQLOLEDB provider.
It also says, "This problem was corrected in MDAC 2.6.". However, it still
doesn't seem to work without the use of the SQLOLEDB provider, so I'm not
sure how that can be considered "corrected".
Thanks,
Dan
"Dan" <someone@somewhere.com> wrote in message
news:efRro46XEHA.3112@tk2msftngp13.phx.gbl...[color=blue]
> I've run into an interesting problem, and seemed to have stumped 3
> newsgroups and 2 other forums.
>
> For some reason when I try to insert a record into a SQL table that has a
> Text column, the returned autogenerated Identity is wrong (on the VB[/color]
side).[color=blue]
> This only occurs if the length of the value inserted for the text column[/color]
is[color=blue][color=green]
> >= 8002.[/color]
>
> I've included a simple example below.
>
> -----------------------------------------
>
> /* Simple table with 1 identity column, and 1 text column) */
> CREATE TABLE [dbo].[Foo] (
> [FooID] [int] IDENTITY (1, 1) NOT NULL ,
> [FooText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> /* Identity column is clustered and set as the primary key */
> ALTER TABLE [dbo].[Foo] WITH NOCHECK ADD
> CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED
> (
> [FooID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
> ' ********************************
> ' VB Code Below...References ADO
> ' ********************************
> Private Sub Save_Foo()
> Dim cn As New Connection, cmd As New Command
>
> Call cn.Open("DSN=xxxx;UID=xxxx;pwd=xxxx") ' Enter your connectionstring
> here
>
> cmd.ActiveConnection = cn
>
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "Foo_Insert"
>
> cmd.Parameters.Append cmd.CreateParameter("FooID", adInteger,[/color]
adParamOutput,[color=blue]
> , 0)
> cmd.Parameters.Append cmd.CreateParameter("FooText", adLongVarChar,
> adParamInput, 8002, String(8002, "@"))
>
> Call cmd.Execute
>
> MsgBox "Returned FooID: " & cmd.Parameters("FooID").Value
> End Sub
>
>
> /* Stored Procedure */
> Alter Procedure Foo_Insert
> (
> @FooID int output,
> @FooText text
> )
> As
>
> INSERT INTO Foo (FooText) VALUES (@FooText)
>
> SET @FooID = @@IDENTITY
>
> return
>
> -----------------------------------------
>
> When I run the code above the returned output parameter in VB is wrong. At
> first it was always returning 60368600, then 60387816, and now the value[/color]
is[color=blue]
> 0.
>
> If I change the value and length of the 2nd parameter to be only 8001
> characters long, it works fine (i.e. the output parameter is incremented
> normally 1,2,3...etc.)
>
> I don't believe the problem is on the SQL side because in both cases the
> information was inserted correctly into the table, and when I step through
> the stored procedure @@IDENTITY returns the correct value, the only[/color]
problem[color=blue]
> is VB assigns the wrong value to the output parameter.
>
> I thought it might be the Parameter Type I was using for the Text field
> (adLongVarChar), but I've tried others resulting in various errors.
>
> I've tried referencing different versions of ADO (2.1, 2.5, and 2.7) and[/color]
the[color=blue]
> problem persists.
>
> I've changed @@IDENTITY to SCOPE_IDENTITY(), and it still doesn't work
> (note: the stored procedure seems to work fine, so this shouldn't have any
> effect on it anyway).
>
> I'm not sure if anyone else has actually tried to run the code (please do
> so), so I can determine if it is something in our environment.
>
> What I'm trying to do (insert a record into a table with a text field, and
> return the auto-generated id via @@IDENTITY or SCOPE_IDENTITY() as an[/color]
output[color=blue]
> parameter) seems like a fairly common thing, and seems like it could be a
> major bug in ADO.
>
> Can anyone help me with this?
>
> Thanks in advance.
> Dan
>
>[/color]
