473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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
4 3808
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
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
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
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Alfredo Agosti | last post by:
Hi folks, I have an Access 2000 db with a memo field. Into the memo field I put text with bold attributes, URL etc etc What I need to to is converting the rich text contained into the memo...
10
by: Tony Archer | last post by:
Gentlemen, your urgent help is needed in solving a STRANGE problem. I have two servers a dev box, and a production box. On the dev box everything works fine, in production it hoses: One of...
16
by: Robbie | last post by:
Hi All This is a belter that my little brain can't handle. Basically I have 1 SQL table that contains the following fields: Stock Code Stock Desc Reference Transaction Date
7
by: Aaron Bertrand - MVP | last post by:
Based on a complaint that one of my articles just links to the MS documentation, I'm writing a tutorial on FileSystemObject. However I'm currently getting stalled by this bug. Environment:...
13
by: | last post by:
From ASP I run a query using MIN(some_field/parameter) Now I need to pass this parameter to the query from ASP code How do I do that? Syntax like MIN( / ) does not work
8
by: Alistair | last post by:
this has been driving me nuts for over an hour now. I have a DB with a date field that is empty Because of this the records sometimes get included in searches because their contents are less...
8
by: JT | last post by:
i have written some asp that reads a fixed length text file, line by line, inserting each line into my database. my problem is that the text file format seems to have extra space at the end of the...
5
by: Drifter | last post by:
The quote below is part of the information i want to save to a MS Access database - or update, as the case may be. The original database was built a long time ago, and looking at the code for...
3
by: Steve | last post by:
I've been told that an e-mail can be constructed using headers so that both a text and HTML version can be sent out simultaneously and the e-mail client will display the type that it can handle...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.