[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

calling system stored procedures from vb

Posted on 2002-06-28
3
Medium Priority
?
152 Views
Last Modified: 2012-05-04
I want to call the following stored procedure on a sql 7.0 server.

EXEC sp_helprotect NULL,  'myusername'

I am trying to check permissions to a table and based on that , enable/disable a menu . Any ideas?

0
Comment
Question by:mmcmillen
[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
  • 2
3 Comments
 
LVL 18

Expert Comment

by:mdougan
ID: 7117044
Private Sub Command1_Click()
Dim CMD As ADODB.Command
Dim PARM1 As ADODB.Parameter
Dim PARM2 As ADODB.Parameter
Dim RS As ADODB.Recordset
Dim strSQL As String

  strSQL = "sp_helprotect"

' In case (unlikely) they were open, close them and free up the resources
    Set CMD = Nothing
    Set PARM1 = Nothing
    Set PARM2 = Nothing
   
' Create New empty instances
    Set CMD = New ADODB.Command
    Set PARM1 = New ADODB.Parameter
    Set PARM2 = New ADODB.Parameter
   
' The the properties for the command object, assumes CN, an ADODB.Connection has already been opened
    With CMD
        .ActiveConnection = CN
        .CommandType = adCmdStoredProc
        .CommandText = strSQL
    End With
   
' Set the properties for the first parameter
' I think this will work for passing null
    With PARM1
        .Direction = adParamInput
        .Type = adVarChar
        .Value = vbNull
        .Size = 1
    End With
   
' Parameters must be appended in the order they appear in the SQL Statement
    CMD.Parameters.Append PARM1
   
' Set the properties for the second parameter.  This shows a character column, notice, no
' special code for wrapping in single quotes.  And, this code will work if the customer column
' happens to contain an embedded single quote like   O'Brian
    With PARM2
        .Direction = adParamInput
        .Type = adVarChar
        .Size = 50
        .Value = "myusername"
    End With
   
    CMD.Parameters.Append PARM2
   
' Now, declare the new recordset
    Set RS = New ADODB.Recordset
' Here, you could set recordset properties like locktype, cursortype etc.
   
' Tell the recordset to get it's data from the command object
    Set RS.Source = CMD
   
' This runs the query
    RS.Open
   
' As long as we're here, this is a way to ensure that you cover all possible bases
' for the recordset's Open statement.  If you try to check EOF on a Closed recordset
' that will generate a run-time error
    If RS.State = adStateOpen Then
        If Not RS.EOF And Not RS.BOF Then
           While Not RS.EOF
              '... you can reference the returned columns by name or by using the fields collection
               msgbox rs.fields(0).Value
           Wend
        Else
           MsgBox "No Records Were Found Matching that Criteria"
        End If
' This is the best place to close the recordset as we've already determined that it was open
        RS.Close
    Else
        MsgBox "Error Opening the Recordset"
    End If
   
' Set everything to nothing to free up the resources (assuming that you are done with the Recordset)
    Set RS = Nothing
    Set PARM1 = Nothing
    Set PARM2 = Nothing
    Set CMD = Nothing
End Sub
0
 
LVL 18

Accepted Solution

by:
mdougan earned 400 total points
ID: 7117071
if you were trying to check permissions on a particular table, then the first parameter's value should be set to the name of the table (with the appropriate size filled in) and not set to NULL as in your original example.
0
 
LVL 2

Author Comment

by:mmcmillen
ID: 7117192
It worked except the vbNull thing needs to be NULL, and you didn't do a rs.movenext.  

but it works great now. thanks

God Bless America!

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
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…
Suggested Courses

650 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