[ASP3][ACCESS][SQL]

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
'////////////////////////////////
'/SQL Tools
'///////////////////////////////
Set CurrentConnect=Nothing
'Persistence save the current connection to the DB
'=-1:Not active
'=0: Init->Connect()
'=1: Active: use CurrentConnect
'=2: Active: Use CurrentConnect and Close()

'Generic ODBC_Connect->Simple connection to ODBC Base
Function ODBC_Connect(Persistence)
	If Persistence=-1 OR Persistence=0 Then
		Set Connect = Server.CreateObject("ADODB.Connection")
		Connect.Open BaseName
		Set CurrentConnect = Connect
		Else If Persistence=2 OR Persistence=1 Then
			Set Connect = CurrentConnect
		End If
	End If
	Set ODBC_Connect = Connect
End Function

'Generic ODBC_Close->Simple Closer connection to ODBC Base: Need Link DB
Function ODBC_Close(Connect,Persistence)
	If Persistence=-1 OR Persistence=2 Then
		Connect.close
		Set Connect = Nothing
		Set CurrentConnect=Connect
	End If
	Set ODBC_Close = Connect
End Function

'Generic Select From DB
Function ODBC_Select(SQL,Pointer,Persistence)
	'Response.Write("SQL="&SQL&"<br />")
	'Variables///////////////////////////////////////
	Dim i:i=0
	Dim TheKey, TheValue, oDict
	'Configuration of DB link////////////////////////
	Set Connect = ODBC_Connect(Persistence)
	'////////////////////////////////////////////////
	Set RS = Server.CreateObject("ADODB.Recordset")
	Set objDict = createObject("Scripting.Dictionary")
	'////////////////////////////////////////////////
	With RS
		.Open SQL,Connect , 3, 3
		If (.EOF and .BOF) Then
			'No data
			ODBC_Select=-1
		Else
			Dim MaxRecord : Maxrecord=.RecordCount
			'Verif MaxRecord////////////////////////////
			If MaxRecord=0 Then
			End If
			'Response.Write("Maxrecord="&Maxrecord&"<br />")
			'MAX <Pointer> in DB/////////////////////////
			If Pointer >= MaxRecord Then
				Pointer = MaxRecord-1
			End If
			'Response.Write("Pointer="&Pointer&"<br />")
			If Pointer >= 0 Then
				.Move(Pointer)
			Else
				.MoveFirst
			End If
			'///////////////////////////////////////////
	
			'Set Dictionary Data///////////////////////
			Do While Not .EOF 
				For each Item in .Fields
					With Item
						TheKey=.name&"["&i&"]"
						TheValue=.value
						'objDict.Add TheKey, TheValue
						objDict(TheKey)=TheValue
						'Response.Write(Item.name&" = "&Item.value&"<br />")
					End With
				Next
				i=i+1
				.MoveNext 
			Loop
			'///////////////////////////////////////////
			.close
		End If
	End With
	Set Connect = ODBC_Close(Connect,Persistence)
	'Return/////////////////////////////////////////////
	Set ODBC_Select = objDict
End Function

'Generic Insert Dictionary indexed by same Fields Name of DB Into DB
Sub ODBC_Insert(Tbl,DicVal,Persistence)
	Dim SQL,Rows,Values,i,Key,Val,MaxTab
	Set Connect = ODBC_Connect(Persistence)

	Rows=""
	Values="VALUES ("
	SQL="INSERT INTO "&Tbl
	Key=DicVal.Keys
	Val=DicVal.Items
	MaxTab=DicVal.Count-1
	
	For i=0 To MaxTab
		If i<MaxTab Then
			Rows=Rows&Key(i)&", "
			Values=Values&"'"&Val(i)&"',"
		Else
			Rows=Rows&Key(i)
			Values=Values&"'"&Val(i)&"'"
		End If
	Next
	
	SQL=SQL&"("&Rows&") "&Values&")"
	Connect.execute(SQL)

	Set Connect = ODBC_Close(Connect,Persistence)
End Sub

'Generic Delete Data From DB.
Sub ODBC_Delete(Tbl,Condition,Persistence)
	Dim SQL
	Set Connect = ODBC_Connect(Persistence)
	
	SQL="DELETE FROM "&Tbl&" WHERE "&Condition
	Connect.execute(SQL)

	Set Connect = ODBC_Close(Connect,Persistence)
End Sub

'Generic Update Data of DB
Sub ODBC_Update(Tbl,DicVal,Where,Persistence)
	Dim SQL,Key,Val,MaxTab
	
	Set Connect = ODBC_Connect(Persistence)
	
	SQL="UPDATE "&Tbl&" SET "
	Key=DicVal.Keys
	Val=DicVal.Items
	MaxTab=DicVal.Count-1
	
	For i=0 To MaxTab
		If i<MaxTab Then
			SQL=SQL&Key(i)&"='"&Val(i)&"', "
		Else
			SQL=SQL&Key(i)&"='"&Val(i)&"' WHERE "&Where
		End If
	Next
	Connect.execute(SQL)
	
	Set Connect = ODBC_Close(Connect,Persistence)
End Sub


'////////////////////////////////
'/TESTS
'///////////////////////////////


'///////////////////////////////
'/Main
'///////////////////////////////

'INSERT
'Dim oDict
'Set oDict = Server.CreateObject("Scripting.Dictionary")
'oDict("Name")="Eva"
'Call ODBC_Insert(aTable,oDict,-1)

'DELETE
'Dim Condition:Condition="id=34"
'Call ODBC_Delete(aTable,Condition,-1)

'UPDATE
'Dim Where:Where="id=42"
'Set oDict = Server.CreateObject("Scripting.Dictionary")
'oDict("Name")="Bob"
'Call ODBC_Update(aTable,oDict,Where,-1)

'BASIC SELECT
'Dim Requete:Requete="SELECT TOP 3 Name, id FROM aTable ORDER BY id"
'Set oDict=ODBC_Select(Requete,-1)
'Key=oDict.Keys
'Val=oDict.Items
'MaxTab=oDict.Count-1

'For i=0 To MaxTab
'		Response.Write(Key(i)&"="&Val(i)&"<br />")
'Next

'ALL TYPE OF SELECT
'Dim Requete:Requete="SELECT Name, id FROM aTable ORDER BY id"
'Response.Write(Requete&"<br /><br />")
'Set Content=ODBC_Select(Requete,-1,0)
'Key=Content.Keys
'Val=Content.Items
'MaxTab=(Content.Count/2)-1'2 is num of select (name+id=2) coz we can t do a dico matrice

'For i=0 To MaxTab
'		Response.Write(Key(i)&"="&Val(i)&"<br />")
'Next

'Requete="SELECT Name FROM aTable ORDER BY id"
'Response.Write(Requete&"<br /><br />")
'Set Content=ODBC_Select(Requete,-1,1)
'Key=Content.Keys
'Val=Content.Items
'MaxTab=(Content.Count/1)-1'1 is num of select (Name=1) coz we can t do a dico matrice

'For i=0 To MaxTab
'		Response.Write(Key(i)&"="&Val(i)&"<br />")
'Next

'Requete="SELECT Name, id, firstname FROM aTable ORDER BY idActor"
'Response.Write(Requete&"<br /><br />")
'Set Content=ODBC_Select(Requete,-1,2)
'Key=Content.Keys
'Val=Content.Items
'MaxTab=(Content.Count/3)-1'3 is num of select (Name+id+firstname=3) coz we can t do a dico matrice

'For i=0 To MaxTab
'		Response.Write(Key(i)&"="&Val(i)&"<br />")
'Next
%>