By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,719 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,719 IT Pros & Developers. It's quick & easy.

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

P: n/a
Dan
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
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Jul 19 '05 #2

P: n/a
Dan
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:Oj**************@TK2MSFTNGP11.phx.gbl...
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.

Jul 19 '05 #3

P: n/a
Dan
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" <so*****@somewhere.com> wrote in message
news:ef**************@tk2msftngp13.phx.gbl...
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

Jul 19 '05 #4

P: n/a
> 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.)
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.