Strange behavior with SQL text field and ADO Parameter....a challenge for the experts (Aaron Bertran

Discussion in 'ASP General' started by Dan, Jul 1, 2004.

  1. Dan

    Dan Guest

    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 side).
    This only occurs if the length of the value inserted for the text column is
    >= 8002.


    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, adParamOutput,
    , 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 is
    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 problem
    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 the
    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 output
    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
    Dan, Jul 1, 2004
    #1
    1. Advertising

  2. The first thing I see is the lack of SET NOCOUNT ON in your stored
    procedure. Without that, your procedure is returning a resultset containing
    an "x records affected by the insert statement" message.Ouput parameter
    values are not returned until resultsets are consumed. Add SET NOCOUNT ON to
    prevent the creation of the resultset.

    Bob Barrows


    Dan wrote:
    > 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 side). This only occurs if the length of the value inserted
    > for the text column is
    >> = 8002.

    >
    > 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,
    > adParamOutput, , 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 is 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 problem 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 the 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 output 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


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jul 1, 2004
    #2
    1. Advertising

  3. Dan

    Dan Guest

    Tried it, but it still doesn't work (output parameter is 0 after execute
    statement).

    Also, remember it works fine if the 2nd parameter's length is < 8002.

    Thanks,
    Dan

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > The first thing I see is the lack of SET NOCOUNT ON in your stored
    > procedure. Without that, your procedure is returning a resultset

    containing
    > an "x records affected by the insert statement" message.Ouput parameter
    > values are not returned until resultsets are consumed. Add SET NOCOUNT ON

    to
    > prevent the creation of the resultset.
    >
    > Bob Barrows
    >
    >
    > Dan wrote:
    > > 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 side). This only occurs if the length of the value inserted
    > > for the text column is
    > >> = 8002.

    > >
    > > 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,
    > > adParamOutput, , 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 is 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 problem 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 the 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 output 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

    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Dan, Jul 1, 2004
    #3
  4. Dan

    Dan Guest

    It's been solved kinda...

    Someone was kind enough to point me to
    http://support.microsoft.com/default.aspx?scid=kb;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" <> wrote in message
    news:...
    > 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

    side).
    > This only occurs if the length of the value inserted for the text column

    is
    > >= 8002.

    >
    > 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,

    adParamOutput,
    > , 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

    is
    > 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

    problem
    > 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

    the
    > 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

    output
    > 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
    >
    >
    Dan, Jul 2, 2004
    #4
  5. Re: It's been solved kinda...

    > 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".


    Because ODBC has been deprecated, so you should be botching all of your DSNs
    anyway?

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
    Aaron [SQL Server MVP], Jul 2, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joel
    Replies:
    0
    Views:
    113
  2. A.M-SG

    ADO.NET 2.0, WebMethods and datatable strange behavior

    A.M-SG, Jan 30, 2006, in forum: ASP .Net Web Services
    Replies:
    1
    Views:
    121
    Josh Twist
    Jan 31, 2006
  3. Robbie
    Replies:
    11
    Views:
    198
    Bob Barrows
    Dec 9, 2003
  4. Rudi Cilibrasi
    Replies:
    1
    Views:
    100
    Stuart Halloway
    Dec 2, 2008
  5. Terry Reedy
    Replies:
    1
    Views:
    86
    Matej Cepl
    Mar 6, 2013
Loading...

Share This Page