Felicitous
asked on
GUID - VB & Stored Procedures
I have a SQL 2000 SP2 stored procedure which accepts a parameter of type 'uniqueidentifier', which I want to use to update a GUID.
Yes I do want to update a GUID directly with a specific value.
I pass a parameter from VB as follows:
.Append DB_Command.CreateParameter
Where pmGUID is a VARIANT.
Inside my proc I want to test the GUID first.
If a GUID is not passed I want to do a slightly different INSERT.
In the stored proc:
Param spec looks like:
@Guild_GUID_6 [uniqueidentifier])
code looks like:
IF @GUID_6 IS NULL
BEGIN
INSERT INTO [Peals_Manager_Data].[dbo]
( [Guild_Name],
[Guild_Coat_Of_Arms],
[Guild_Information])
VALUES
( @Guild_Name_2,
@Guild_Coat_Of_Arms_4,
@Guild_Information_5)
etc etc
I get the following error when I try and run it:
-2147217913Operand type clash: uniqueidentifier is incompatible with int
I must have a conflict between data types in VB but for the life of me I cannot resolve it.
Any one got any code that does this please ?
Kind Regards,
Graham
Yes I do want to update a GUID directly with a specific value.
I pass a parameter from VB as follows:
.Append DB_Command.CreateParameter
Where pmGUID is a VARIANT.
Inside my proc I want to test the GUID first.
If a GUID is not passed I want to do a slightly different INSERT.
In the stored proc:
Param spec looks like:
@Guild_GUID_6 [uniqueidentifier])
code looks like:
IF @GUID_6 IS NULL
BEGIN
INSERT INTO [Peals_Manager_Data].[dbo]
( [Guild_Name],
[Guild_Coat_Of_Arms],
[Guild_Information])
VALUES
( @Guild_Name_2,
@Guild_Coat_Of_Arms_4,
@Guild_Information_5)
etc etc
I get the following error when I try and run it:
-2147217913Operand type clash: uniqueidentifier is incompatible with int
I must have a conflict between data types in VB but for the life of me I cannot resolve it.
Any one got any code that does this please ?
Kind Regards,
Graham
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Otana, thanks for the help, this is one thing I have yet to try. I am going to give it a go and see what happens.
Ianouii !?! I have read all the books, however you are right on the name error, a slip when I copied a large chunk of text and tried to trim it for here.
Kind Regards,
Graham
Ianouii !?! I have read all the books, however you are right on the name error, a slip when I copied a large chunk of text and tried to trim it for here.
Kind Regards,
Graham
Update us if Otana solution isn't appropriate. Cheers!
ASKER
OK I cant get anything to work
The code currently looks like this, but there have been various iterations which dont work:
Any help really appreciated.
Private pmGuild_GUID as Variant
Public Property Let pmGUID(prmGuild_GUID As Variant)
pmGuild_GUID = prmGuild_GUID
End Property
Public Property Get pmGUID() As Variant
pmGUID = pmGuild_GUID
End Property
============
DB_Command.CommandType = adCmdStoredProc
DB_Command.CommandText = "pm_insert_GUILD"
With DB_Command.Parameters
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
End With
DB_Command.Execute , , adCmdStoredProc
============
Alter PROCEDURE pm_insert_GUILD
( @Guild_Name_2 [varchar](255),
@Guild_Coat_Of_Arms_4 [varchar](255),
@Guild_Information_5 [text],
@Guild_GUID_6 [uniqueidentifier],
@Guild_Identity_7 [int] OUTPUT)
AS
IF @Guild_Identity_7 = 0
BEGIN
IF @Guild_GUID_6 = CAST('00000000-0000-0000-0
BEGIN
INSERT INTO [Peals_Manager_Data].[dbo]
( [Guild_Name],
[Guild_Coat_Of_Arms],
[Guild_Information])
VALUES
( @Guild_Name_2,
@Guild_Coat_Of_Arms_4,
@Guild_Information_5)
END
ELSE
BEGIN
INSERT INTO [Peals_Manager_Data].[dbo]
( [Guild_Name],
[Guild_GUID],
[Guild_Coat_Of_Arms],
[Guild_Information])
VALUES
( @Guild_Name_2,
@Guild_GUID_6,
@Guild_Coat_Of_Arms_4,
@Guild_Information_5)
END
Kind Regards,
Graham
The code currently looks like this, but there have been various iterations which dont work:
Any help really appreciated.
Private pmGuild_GUID as Variant
Public Property Let pmGUID(prmGuild_GUID As Variant)
pmGuild_GUID = prmGuild_GUID
End Property
Public Property Get pmGUID() As Variant
pmGUID = pmGuild_GUID
End Property
============
DB_Command.CommandType = adCmdStoredProc
DB_Command.CommandText = "pm_insert_GUILD"
With DB_Command.Parameters
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
.Append DB_Command.CreateParameter
End With
DB_Command.Execute , , adCmdStoredProc
============
Alter PROCEDURE pm_insert_GUILD
( @Guild_Name_2 [varchar](255),
@Guild_Coat_Of_Arms_4 [varchar](255),
@Guild_Information_5 [text],
@Guild_GUID_6 [uniqueidentifier],
@Guild_Identity_7 [int] OUTPUT)
AS
IF @Guild_Identity_7 = 0
BEGIN
IF @Guild_GUID_6 = CAST('00000000-0000-0000-0
BEGIN
INSERT INTO [Peals_Manager_Data].[dbo]
( [Guild_Name],
[Guild_Coat_Of_Arms],
[Guild_Information])
VALUES
( @Guild_Name_2,
@Guild_Coat_Of_Arms_4,
@Guild_Information_5)
END
ELSE
BEGIN
INSERT INTO [Peals_Manager_Data].[dbo]
( [Guild_Name],
[Guild_GUID],
[Guild_Coat_Of_Arms],
[Guild_Information])
VALUES
( @Guild_Name_2,
@Guild_GUID_6,
@Guild_Coat_Of_Arms_4,
@Guild_Information_5)
END
Kind Regards,
Graham
ASKER
I have solved my problem.
The problem lies in the basic format of my GUID itself.
I have been submitting the final portion of the GUID as 10 digits, when in reality its 12. This simple basic counting mistake has been causing the cast, conversion and type errors I have been experiencing.
This datatype really is a fussy little critter, or maybe I should be more careful :-)
now although the comments posted by Otana and Ianouii did not find my actual problem, it was only through bouncing ideas around the problem was solved. What I would like to do is split the points between them.
How do I do this?
Regards,
Graham
The problem lies in the basic format of my GUID itself.
I have been submitting the final portion of the GUID as 10 digits, when in reality its 12. This simple basic counting mistake has been causing the cast, conversion and type errors I have been experiencing.
This datatype really is a fussy little critter, or maybe I should be more careful :-)
now although the comments posted by Otana and Ianouii did not find my actual problem, it was only through bouncing ideas around the problem was solved. What I would like to do is split the points between them.
How do I do this?
Regards,
Graham
ASKER
I have asked community support how to split a question.
thanks Felicitous.
Thanks.
Thank you. Point split has been completed for you.
Points for Otana -> qid=20286691 Please comment there so I may complete the other half of this point split award.
Felicitous, the following is helpful for future point splits you'd like to do.
In order to split points, this process is used:
Let us know which expert you intend to award in the primary question and the points you wish to grant. We will then reduce the value of the original question to that value and you accept that expert's comment or Proposed Answer within that question. Then please do the following for each additional expert you wish to award points:
Within that same topic area, post a new question for each expert at the point value you wish to grant.
Topic = Points for __expertname__
In the comments section please include the Question Link (QID number).
It is ideal that you also post this information in the original question, and include the new Question Link so they can quickly and easily find it, and an audit trail is maintained.
That's all there is to it. The experts will then either add comments for you to convert to the Accepted Answer to then grade and close, or will Propose an Answer for you to then accept to grade and close.
These links are very helpful on site-related processes and Questions/Answers:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp
https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
Thank you.
Moondancer
Community Support Moderator @ Experts Exchange
Points for Otana -> qid=20286691 Please comment there so I may complete the other half of this point split award.
Felicitous, the following is helpful for future point splits you'd like to do.
In order to split points, this process is used:
Let us know which expert you intend to award in the primary question and the points you wish to grant. We will then reduce the value of the original question to that value and you accept that expert's comment or Proposed Answer within that question. Then please do the following for each additional expert you wish to award points:
Within that same topic area, post a new question for each expert at the point value you wish to grant.
Topic = Points for __expertname__
In the comments section please include the Question Link (QID number).
It is ideal that you also post this information in the original question, and include the new Question Link so they can quickly and easily find it, and an audit trail is maintained.
That's all there is to it. The experts will then either add comments for you to convert to the Accepted Answer to then grade and close, or will Propose an Answer for you to then accept to grade and close.
These links are very helpful on site-related processes and Questions/Answers:
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp
https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
Thank you.
Moondancer
Community Support Moderator @ Experts Exchange
in your SP, you can test like this:
IF @Guild_GUID_6 = ''
insert ...