'''''' open database
SUB opendb
    dim db
    db = xDblocation & "\" & xDatabase     
    Set gDbConn= Server.CreateObject("ADODB.Connection")
    'gDbConn.mode = 3 ' adModeReadWrite, fix Unspecified Error (0x80004005)
    gDbConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath(db) & ";"		'local access file    
END SUB


''''' Close record set and database
SUB closeRsDb
'''close record set

	''' better smooth code
	If IsObject(gRs) Then
		If Not (gRs Is Nothing) Then
			if gRs.state <> 0 Then
				gRs.close()
				Set gRs= Nothing
			end If
		end If 
	end if	

	
	closeConn  
END SUB


''close database connection

SUB closeConn
	if gDbConn<>null then
	    gDbConn.Close
	    Set gDbConn = Nothing
	end if 
END SUB


''' use database
SUB sqlUtility
dim sql
	opendb
	sql="SELECT * FROM products "
	Set gRs = gDbConn.Execute(sql)
	debug "return value = " & gRs("cname") 
	closeRsDb
END SUB


''' use different cursor to retrieve data
SUB getOneProducts (productId)
dim sql
dim recCnt
dim imgArray		''can define size late

    openDb
	''this simple cursor cannot get RecordCountvalue. It is always -1
	sql = "SELECT * FROM images WHERE productid=" & productId
	Set gRs = gDbConn.Execute(sql)
	recCnt= gRs.RecordCount		''always -1


	''Following cursor have more control, you can get recordCount, and paganation, etc
	set gRs =Server.CreateObject("ADODB.recordset")
	gRs.CursorLocation = 3	'adUseClient
	gRs.CursorType = 3	'adOpenStatic
	    
	sql = "SELECT * FROM images WHERE productid=" & productId
	gRs.Open sql, gDbConn
	recCnt= gRs.RecordCount	''return real count value


	''you can use the return size to defind the array size, and loop through to read all values
	redim imgArray(recCnt, 2)		''only need two column

	recCnt = 0	
	do while not gRs.EOF
		imgArray(recCnt, 0) = gRs("imageurl")
		imgArray(recCnt, 1) = gRs("imagedetail")
		recCnt= recCnt+ 1
		gRs.MoveNext
	loop
	
	closeRsDb
	
END SUB