Solved

calling system stored procedures from vb

Posted on 2002-06-28
3
143 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
  • 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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.
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…
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now