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
Solved

GUID - VB & Stored Procedures

Posted on 2002-03-28
10
503 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
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…

829 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