Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


SQL Server Stored Procedures

SQL Server Stored Procedures

SQL Server Stored Procedures

Using eRDPro Version 8 Service Pack 1.

Does anyone have sample Basic programming code for executing a SQL Server 2005 Stored Prodecure?

What I'm attempting to do in Basic code is to programmatically create a database connection object and then use the object to execute a stored procedure.

I also need some sample code for executing a proc that returns a result set with multiple records.

I've inheirated a report that does what I want using a MS Access database, but I'm having trouble modifying the code to use SQL Server.

All of this code resides in an included Basic library file.

Thanks.  Some sample code for a MS Access database follows.


sub initDBConn( )
' initializes conn object   
    set gm_conn = createObject("ADODB.Connection")
    dim xdsn
    xdsn = DB_CONNSTRING
    if getDSN( ) <> DB_DSN then
        xdsn = "dsn=" & getDSN( )
    end if

    ' gm_conn.Open(DB_CONNSTRING)           
    ' as per A, it gives connection error   
    ' to the default dsn, BOOKINGS          
end sub

function dbConn( ) as object
' returns:                  
' ref to gm_conn            
    if gm_conn is Nothing then initDBConn( )
    set dbConn = gm_conn
end function

sub buildBookings( )
' calls all needed query to
' build table for report    
    if NOT gmp_bRefreshTable then
        showFactoryStatus("bypassing table maintenance...")
        showFactoryStatus("ignoring generation of bookings info for all products...")
        exit sub
    end if
    ' maintenance           
    showFactoryStatus("> performing table maintenance")
    showFactoryStatus("...cleaning rsm table")
    showFactoryStatus("...populating rsm table")
    showFactoryStatus("...cleaning rep table")
    showFactoryStatus("...populating rep table")
    showFactoryStatus("...cleaning bookings_by_rep table")

    ' products          

    ' products           
    showFactoryStatus("... sheets + extruded")
    showFactoryStatus("... sheets")
    showFactoryStatus("... mats & grids")
    showFactoryStatus("... extruded")
End Sub

sub runQuery(sql as string)
' runs an MSAccess-defined  
' query                     
end sub

sub buildGSMInfo( )
' gets the GSM info         
' and store it to gm_arGSM  
    dim rs as object
    set rs = createObject("ADODB.Recordset")
    set rs = dbConn.execute("getGSMInfo")

    dim i as integer
    do while NOT rs.eof( )
        ' gsm id as index   
        ' of array          
        i = rs.fields("gsm_id").value
        if (uBound(gm_arGSM) <= i) then
            redim preserve gm_arGSM(i)
        end if

        ' save gsm info     
        gm_arGSM(i).gid     = rs.fields("gsm_id").value
        gm_arGSM(i).gname   = rs.fields("gsm_name").value

        ' date info         
        gm_dtInfo.imonth    = rs.fields("actual_month").value
        gm_dtInfo.iyear     = rs.fields("actual_year").value

        ' next gsm pls      
        rs.moveNext( )

    ' remove rs             
    set rs = Nothing

    dbConn.OpenCursor("exec cspGetRSMInfo" )
end sub

RE: SQL Server Stored Procedures

This may be over simple but have you tried bringing the inherited report with MS Access connection into eRDPro?  
-Use the Slot Information to drop the MS Access connection
-Drag a SQL SERVER/ODBC connection from your library
-Make your call to the stored proc and use the data from the returned resultset cursor.


Norma Ramey
Maximo Analyst/Actuate Specialist

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close