Solved

GUID - VB & Stored Procedures

Posted on 2002-03-28
10
495 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
  • 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now