Solved

GUID - VB & Stored Procedures

Posted on 2002-03-28
10
513 Views
Last Modified: 2007-11-27
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("@GUID_6", adGUID, adParamInput, , pmGUID)

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]
( [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
0
Comment
Question by:Felicitous
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 5

Accepted Solution

by:
ianouii earned 150 total points
ID: 6904161
your statement has some ambigous elements.

@Guild_GUID_6  or @GUID_6 ?

How u set the GUID field in the db table? Is it an UniqueIdentifier? If so, how do u generate your GUID in your VB? SQL GUID is unique. For instance,
6F9619FF-8B86-D011-B42D-00C04FC964FF

If your VB doesn't supply such thing to it, the error will occur.

I'd suggest u to read more about Uniqueidentifier in MSDN & also SQL Server Book Online.

Go to index, type in uniqueidentifier. I'm sure u'll have a clearer view on how to solve your problem then.

If u need more helps, be more precise with your question & kindly post your code here so that all experts here can easily detect the problem.

Good luck.
0
 
LVL 11

Expert Comment

by:Otana
ID: 6904661
A trick I always use is defining the GUID parameter as a varchar(50), in VB it's a string.

in your SP, you can test like this:

IF @Guild_GUID_6 = ''
  insert ...
0
 

Author Comment

by:Felicitous
ID: 6904691
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:ianouii
ID: 6904696
Update us if Otana solution isn't appropriate. Cheers!
0
 

Author Comment

by:Felicitous
ID: 6905989
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("ReturnValue", adInteger, adParamReturnValue, , 0)
        .Append DB_Command.CreateParameter("@Guild_Name_2", adVarChar, adParamInput, 255, pmGuild_Name)
        .Append DB_Command.CreateParameter("@Guild_Coat_Of_Arms_4", adVarChar, adParamInput, 255, pmGuild_Coat_Of_Arms)
        .Append DB_Command.CreateParameter("@Guild_Information_5", adLongVarChar, adParamInput, 2048, pmGuild_Information)
        .Append DB_Command.CreateParameter("@Guild_GUID_6", adGUID, adParamInput, 16, pmGuild_GUID)
        .Append DB_Command.CreateParameter("@Guild_Identity_7", adInteger, adParamInputOutput, , pmIdentity)
    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-0000-0000000000' as uniqueidentifier)
               BEGIN
                INSERT INTO [Peals_Manager_Data].[dbo].[GUILD]
                         ( [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]
                          ( [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
0
 

Author Comment

by:Felicitous
ID: 6906774
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

0
 

Author Comment

by:Felicitous
ID: 6906781
I have asked community support how to split a question.
0
 
LVL 5

Expert Comment

by:ianouii
ID: 6909524
thanks Felicitous.
0
 
LVL 11

Expert Comment

by:Otana
ID: 6912202
Thanks.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6929504
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:
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp
http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp
 
Thank you.
 
Moondancer
Community Support Moderator @ Experts Exchange
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month4 days, 17 hours left to enroll

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question