473,699 Members | 2,078 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_Gene ral_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...Referen ces ADO
' *************** *************** **
Private Sub Save_Foo()
Dim cn As New Connection, cmd As New Command

Call cn.Open("DSN=xx xx;UID=xxxx;pwd =xxxx") ' Enter your connectionstrin g
here

cmd.ActiveConne ction = cn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Foo_Insert "

cmd.Parameters. Append cmd.CreateParam eter("FooID", adInteger, adParamOutput,
, 0)
cmd.Parameters. Append cmd.CreateParam eter("FooText", adLongVarChar,
adParamInput, 8002, String(8002, "@"))

Call cmd.Execute

MsgBox "Returned FooID: " & cmd.Parameters( "FooID").Va lue
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 3833
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_Gene ral_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...Referen ces ADO
' *************** *************** **
Private Sub Save_Foo()
Dim cn As New Connection, cmd As New Command

Call cn.Open("DSN=xx xx;UID=xxxx;pwd =xxxx") ' Enter your
connectionstrin g here

cmd.ActiveConne ction = cn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Foo_Insert "

cmd.Parameters. Append cmd.CreateParam eter("FooID", adInteger,
adParamOutput, , 0)
cmd.Parameters. Append cmd.CreateParam eter("FooText", adLongVarChar,
adParamInput, 8002, String(8002, "@"))

Call cmd.Execute

MsgBox "Returned FooID: " & cmd.Parameters( "FooID").Va lue
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******@NOyah oo.SPAMcom> wrote in message
news:Oj******** ******@TK2MSFTN GP11.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_Gene ral_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...Referen ces ADO
' *************** *************** **
Private Sub Save_Foo()
Dim cn As New Connection, cmd As New Command

Call cn.Open("DSN=xx xx;UID=xxxx;pwd =xxxx") ' Enter your
connectionstrin g here

cmd.ActiveConne ction = cn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Foo_Insert "

cmd.Parameters. Append cmd.CreateParam eter("FooID", adInteger,
adParamOutput, , 0)
cmd.Parameters. Append cmd.CreateParam eter("FooText", adLongVarChar,
adParamInput, 8002, String(8002, "@"))

Call cmd.Execute

MsgBox "Returned FooID: " & cmd.Parameters( "FooID").Va lue
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*****@somewh ere.com> wrote in message
news:ef******** ******@tk2msftn gp13.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_Gene ral_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...Referen ces ADO
' *************** *************** **
Private Sub Save_Foo()
Dim cn As New Connection, cmd As New Command

Call cn.Open("DSN=xx xx;UID=xxxx;pwd =xxxx") ' Enter your connectionstrin g
here

cmd.ActiveConne ction = cn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Foo_Insert "

cmd.Parameters. Append cmd.CreateParam eter("FooID", adInteger,

adParamOutput, , 0)
cmd.Parameters. Append cmd.CreateParam eter("FooText", adLongVarChar,
adParamInput, 8002, String(8002, "@"))

Call cmd.Execute

MsgBox "Returned FooID: " & cmd.Parameters( "FooID").Va lue
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
8675
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 field in plain text. That's because I truncate the string text but if, when I truncate it there's a rich tag the code following the truncated string is corrupted. Don't know if it's clear enough.
10
2556
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 the things a user can do is click a "Change region" link which takes them to a map of the world. They can then select one of four regions. When they select a region it takes them to a page which reads a variable and then sets a cookie which is...
16
2289
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
3992
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: Windows Server 2003, IIS 6.0, VBScript 5.6, all windows and IE update patches (to my knowledge). From msdn.microsoft.com/library/en-us/script56/html/jsmthcreatetextfile.asp
13
2732
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
1859
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 than a particular search. so..I had planned to populate them with any old date. BUT I CAN'T!!!
8
2849
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 file so my code thinks there is actually one more line when there is not. is there a way to remove these spaces before i loop through the file? here is my sample code: Do While df.AtEndOfStream <> True line = df.ReadLine
5
2732
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 add/update on the original site-it does not compensate for odd syntax like the quotes. Problem i am hitting now is syntax issue in the SQL when I go to save/update. The syntax present causes failures every time. User said she had no problems...
3
1880
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 (http://www.wilsonweb.com/wmt5/html-email-multi.htm). How is this done using CDONTS and ASP to send an e-mail? Is there a specific field that the content type gets put into? I tried putting: Content-Type: multipart/alternative;...
0
8630
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8944
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8899
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7786
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6550
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5889
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4391
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4638
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2364
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.