Voici un petit code de gros fainéant lol:
J'utilise l'objet Dico pour pouvoir faire correspondre les clés du dico aux champs de la bases. Pareils pour les valeurs.
Je viens du PHP et je suis assez dessus par ce problème: on ne peut pas faire de dictionnaire en matrice!!!
Pour le coup avec un SELECT la clé est mondico["nomDuChamp[ligne d'enregistrement]"].
Tout a la fin du code il y a tout les tests avec toutes les possibilités.
Le SELECT est le plus dur a comprendre. Les autres sont trés accéssible.
Les paramètres de connection sont indiqués. Il y a un paramètre de persistance de connection.
Source / Exemple :
<%@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
%>
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.