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" |
 |
|
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" |
 |
|
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??? |
 |
|
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" |
 |
|
richieWolf
Starting Member
7 Posts |
Posted - 2008-07-14 : 07:08:31
|
Changed to nvarchar but I still get an error :( |
 |
|
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" |
 |
|
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 |
 |
|
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" |
 |
|
richieWolf
Starting Member
7 Posts |
Posted - 2008-07-14 : 07:38:06
|
Still confused!!! Is it 4000 then??? |
 |
|
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" |
 |
|
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 uniqueidentifierSET @guid = convert(uniqueidentifier, @UserID )SET @newComments = @Comments + CONVERT(VARCHAR(4000), GETDATE(), 120) UPDATE Users SET New_Comments = @newComments WHERE UserID = @guid |
 |
|
|