Solved

Stored proc syntax -- returning a boolean -- to VB 6 code

Posted on 2003-11-12
5
1,051 Views
Last Modified: 2012-08-13
Hello,

I need to see an example of a stored proc that receives a string (representing a logon_name) as an input parameter, and checks the user table to see if there is already a user with that logon_name, if so, returns a boolean of true.

Then, how can I call that stored proc from VB 6 code and "get"  the Boolean?

I have no idea how to do this, so thanks for your help, or suggestions for better ways of doing this.



0
Comment
Question by:edmund_mitchell
  • 2
  • 2
5 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 125 total points
ID: 9732875
I can help with SP.
Will need someone else to help with VB code.

There are two SP approaches you could take, either use the return code to indicate existence or use an output variable, for example:

CREATE PROCEDURE yourProcNameHere (
      @logon_name VARCHAR(50)
)
AS
RETURN CASE WHEN EXISTS(SELECT 1 FROM [user] WHERE logon_name = @logon_name) THEN 1 ELSE 0 END

<OR>

CREATE PROCEDURE yourProcNameHere (
      @logon_name VARCHAR(50),
      @name_found BIT = 0 OUTPUT
)
AS
SET @name_found = CASE WHEN EXISTS(SELECT 1 FROM [user] WHERE logon_name = @logon_name) THEN 1 ELSE 0 END
RETURN
0
 
LVL 4

Author Comment

by:edmund_mitchell
ID: 9732936
Thanks, Scott - if someone else does the VB then I'll split the points.  Do you happen to know if that search is case sensitive or insensitive?  
0
 
LVL 15

Assisted Solution

by:namasi_navaretnam
namasi_navaretnam earned 125 total points
ID: 9733010
Here is the VB code for Scott's Proc with output parameter.

Private Sub Form_Load()
Dim cmd As New ADODB.Command
Dim param1 As New ADODB.Parameter
Dim param2 As New ADODB.Parameter

With cmd
    .CommandText = "sp_GetUserInfo"
    .CommandType = adCmdStoredProc
    .ActiveConnection = CONN_STRING
    Set param1 = .CreateParameter("UserId", adVarChar, adParamInput, 50, "JohnDoe")
    .Parameters.Append param1
   
    Set param2 = .CreateParameter("NameFound", adInteger, adParamOutput)
    .Parameters.Append param2
   
    .Execute Options:=adExecuteNoRecords
    Set .ActiveConnection = Nothing
    Set param1 = Nothing
End With


Notice that in the Execute method, we specified adExecuteNoRecords. Doing so tells ADO not to bother building and populating a recordset just to hold the return value.

Once executed, your code can retrieve the output parameter value one of two ways: either by accessing the Parameters collection, or by using the Parameter object directly.  The following code illustrates both methods and concludes the previous code procedure.

MsgBox param2.Value

Set param2 = Nothing
Set cmd = Nothing
End Sub

0
 
LVL 4

Author Comment

by:edmund_mitchell
ID: 9733069
I hope that worked -- I split the points equally.  Thanks to you both!
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9733647
>> Do you happen to know if that search is case sensitive or insensitive?  

That depends on the type of installation.  If the installation is case sensitive, the comparison will be; if it is not, the comparison won't be.  

Now, if you want to force it to be insensitive, even on a case sensitive installation, convert both to upper (or lower) case, for example:

SELECT 1 FROM [user] WHERE UPPER(logon_name) = UPPER(@logon_name)

If you want to force it to be case sensitive on a case-insensitive installation, you have to cast to varbinary, like so:

SELECT 1 FROM [user] WHERE CAST(logon_name AS VARBINARY(50)) = CAST(@logon_name AS VARBINARY(50))
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

920 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

18 Experts available now in Live!

Get 1:1 Help Now