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)
 Error in calling Stored Procedure

Author  Topic 

richieWolf
Starting Member

7 Posts

Posted - 2008-07-14 : 06:34:44
The following Stored Procedure now works when ran in SQL Server.


CREATE PROCEDURE spUpdateUserComments
@UserID uniqueidentifier,
@Comments nvarchar(4000)

AS
BEGIN
SET NOCOUNT ON
DECLARE @newComments NVARCHAR(4000)
SET @newComments = @Comments + CONVERT(VARCHAR(19), GETDATE(), 120)

UPDATE Users
SET New_Comments = @newComments
WHERE UserID = @UserID


(Note: I append a DateTime to each comment)

However when I call it in VB I get the error 'Invalid character value for cast specification'. Any ideas why?

Set objCommand = New ADODB.Command
objCommand.ActiveConnection = m_Connection
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = "spUpdateUserComments"

objCommand.Parameters.Append objCommand.CreateParameter("UserID", adGUID, adParamInput, 72, strUserID)
objCommand.Parameters.Append objCommand.CreateParameter("Comments", adWChar, adParamInput, 202, strComments)
objCommand.Execute

Set objCommand.ActiveConnection = Nothing



Any ideas why???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 06:38:14
CONVERT(NVARCHAR(19), GETDATE(), 120)



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 06:39:14
ANd why have you put 72 and 202 as parameter values?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

richieWolf
Starting Member

7 Posts

Posted - 2008-07-14 : 07:03:37
ANd why have you put 72 and 202 as parameter values?

I'm not sure what size to put in and I found it on a website. What should the numbers be???
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 07:07:08
http://www.w3schools.com/ado/met_comm_createparameter.asp



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

richieWolf
Starting Member

7 Posts

Posted - 2008-07-14 : 07:08:31
Changed to nvarchar but I still get an error :(
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 07:26:35
A GUID is how many bytes? 16 I presume.
How many bytes are you to insert at maximum? 4000 according to SP code.




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

richieWolf
Starting Member

7 Posts

Posted - 2008-07-14 : 07:30:50
Think I made a mistake in the 1st CreateParameter parameter but changing it still get errors. Damn!!! :(

objCommand.Parameters.Append objCommand.CreateParameter("@UserID", adGUID, adParamInput, 16, strUserID)
objCommand.Parameters.Append objCommand.CreateParameter("@Comments", adWChar, adParamInput, 4000, strComments)
objCommand.Execute
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 07:33:04
@Comments nvarchar(4000)

this variable is 8000 bytes in storage.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

richieWolf
Starting Member

7 Posts

Posted - 2008-07-14 : 07:38:06
Still confused!!! Is it 4000 then???
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-14 : 08:01:11
It can store 4000 character but takes 8000 bytes in storage. Try 8000 for size.




E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

richieWolf
Starting Member

7 Posts

Posted - 2008-07-14 : 08:10:34
I got it working now by changing the SP. It's to do with convert a string to uniqueidentifier. It runs but the date does not get appended.

Any ideas?

CREATE PROCEDURE spUpdateUserComments
@UserID varchar(50),
@Comments nvarchar(4000)

AS
BEGIN
SET NOCOUNT ON
DECLARE @newComments NVARCHAR(4000)
DECLARE @guid uniqueidentifier
SET @guid = convert(uniqueidentifier, @UserID )
SET @newComments = @Comments + CONVERT(VARCHAR(4000), GETDATE(), 120)

UPDATE Users
SET New_Comments = @newComments
WHERE UserID = @guid

Go to Top of Page
   

- Advertisement -