Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server Stored Procedures

Status
Not open for further replies.

pwomack

Technical User
Apr 16, 2005
121
US
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top