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