Solved

calling system stored procedures from vb

Posted on 2002-06-28
3
148 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 100 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

739 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