Sp to update TEXT field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sp to update TEXT field

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-04-26 : 15:38:50

Hi, every body,

I need your help.
My stored procedure did not make it through. It did not get any error/exception, but the data did not get inserted.
I have the permission checked to allow exec on the sp. And I can run the sp from my sql QA successfully.
The connection string and other settings have been used on other similar calls without problem.

Please help! And here are the pieces.
1) UI (win form app in vs2005 vb.net), the param values are set correctly under break point.
Private Sub UpdateNote(ByVal MailtoEmail As String)
Dim arParms() As SqlParameter = New SqlParameter(1) {}
Try
arParms(0) = New SqlParameter("@P1", SqlDbType.VarChar, ParameterDirection.Input)
arParms(0).Value = JobID
arParms(1) = New SqlParameter("@P2", SqlDbType.VarChar, ParameterDirection.Input)
arParms(1).Value = MailtoEmail
SqlHelper.ExecuteNonQuery(cn_str_QWPro, CommandType.StoredProcedure, _
"CT_setIntNote", arParms)
Catch Ex As Exception
Console.WriteLine(Ex.Message)
End Try
End Sub

2) data access, i.e. SqlHelper.ExecuteNonQuery is from ms application block data access lib

3) sp (the field I try to update is of data type TEXT in sql2k, and I have tried two different type sp, one using straight UPDATE, another is for WRTETEXT, but neither works)

3)-1 UPDATE version
CREATE PROCEDURE [dbo].[CT_setIntNote]
@p1 as varchar(40),
@p2 as varchar(50)
AS
SET NOCOUNT ON
declare @mystr as varchar(2000)

Select @mystr = int_notes from orderheader where order_nbr = @p1
set @mystr = convert(varchar(20), getdate(), 0) + ' >>>Proof emailed to ' + @p2 + ' mdw ' + @mystr
Update orderheader
set int_Notes =@mystr
where order_nbr = @p1
GO

3) -2 WRITETEXT version
CREATE PROCEDURE [dbo].[CT_setIntNote]
@p1 as varchar(40),
@p2 as varchar(50)
AS
SET NOCOUNT ON
declare @mystr as varchar(8000)
DECLARE @ptrval binary(16)
--get the pointer
SELECT @ptrval = TEXTPTR(head_int_notes) from order_header where head_order_nbr = @p1
--get the existing note
Select @mystr = head_int_notes from order_header where head_order_nbr = @p1
--add the new parts in
set @mystr = convert(varchar(20), getdate(), 0) + ' >>>Proof #1 emailed to ' + @p2 + ' .mdw ' + @mystr
--save back
WRITETEXT order_header.head_int_notes @ptrval @mystr

GO

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-04-27 : 12:25:00

I used profiler to see what came in and found that
my TextDada = exec CT_setIntNote @P1 = '8', @P2 = 'p'

On the calling side,
I have tried
SqlDbType.VarChar
and
SqlDbType.Char
The later gave me the right value in immediate window as ?arParms(0).Value string '881412' and arParms(1).Value string 'psomething@shsd.com'

What/who took the rest of my string away?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-04-27 : 14:25:45

Just guessing, but maybe you need to specify a varchar length here, which may be defaulting to 1:

Try
arParms(0) = New SqlParameter("@P1", SqlDbType.VarChar, ParameterDirection.Input)
arParms(0).Value = JobID
arParms(1) = New SqlParameter("@P2", SqlDbType.VarChar, ParameterDirection.Input)
arParms(1).Value = MailtoEmail

e4 d5 xd5 Nf6

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-04-27 : 15:14:00

I have tried

SqlDbType.VarChar(10)

but that is not the right syntax in dotnet.

Well, I did find out the "real" cause, but I am more confused than ever!

When I stepped into SQLHelper.ExecuteNonQuery or ExecuteDataSet, I saw the parameter array size =1! If I manually changed it to 10, then every thing worked!

I have never had to changed any thing in MS Application Block Data Access lib, and the same class/dll is in production use.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources