Solved

GUID - VB & Stored Procedures

Posted on 2002-03-28
10
500 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

777 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