×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Jobs

SQL Server Stored Procedures

SQL Server Stored Procedures

SQL Server Stored Procedures

(OP)
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.

CODE


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          
    gm_conn.Open(xdsn)
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...")
        showFactoryStatus("")
        exit sub
    end if
    
    ' maintenance           
    '                       
    showFactoryStatus("> performing table maintenance")
    showFactoryStatus("...cleaning rsm table")
    runQuery("cleanRSMTable")
    showFactoryStatus("...populating rsm table")
    runQuery("popRSMTable")
    showFactoryStatus("...cleaning rep table")
    runQuery("cleanRepTable")
    showFactoryStatus("...populating rep table")
    runQuery("popRepTable")
    showFactoryStatus("...cleaning bookings_by_rep table")
    runQuery("cleanBookingsTable")


    ' products          
    '                       
    showFactoryStatus("...arch")
    runQuery("bksEJCArch")
    showFactoryStatus("...parking")
    runQuery("bksEJCPark")

    ' products           
    '                       
    showFactoryStatus("... sheets + extruded")
    runQuery("bksGE")
    showFactoryStatus("... sheets")
    runQuery("bksGESheet")
    showFactoryStatus("... mats & grids")
    runQuery("bksGEMatsAndGrids")
    showFactoryStatus("... extruded")
    runQuery("bksGEExtruded")
End Sub

sub runQuery(sql as string)
' runs an MSAccess-defined  
' query                     
    dbConn.execute(sql)
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( )
    loop

    ' remove rs             
    set rs = Nothing

    dbConn.OpenCursor("exec cspGetRSMInfo" )
end sub
 

RE: SQL Server Stored Procedures

Hi,
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
www.projetech.com

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!

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