?
Solved

calling system stored procedures from vb

Posted on 2002-06-28
3
Medium Priority
?
150 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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 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…
Suggested Courses

770 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