SQL Server Stored Procedures

[pwomack]

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          
    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

 

[AbbyNL]

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

http://www.projetech.com