数据库操作

1。 显示数据库所有记录：51Testing软件测试网S%W%m~0?~"vW2M

j'Oq[)\ [f;h0pb?0Dim oConn, oRst, oFieldDim sql51Testing软件测试网OYeZHU2p
set ōConn = CreateObject("ADODB.Connection")51Testing软件测试网j%{Jc}F
oConn.Open "QT_Flight32"
y[
Ip.[K f,DXQ0set ōRst = CreateObject("ADODB.recordset")51Testing软件测试网
y8ek$pEc&L
oRst.Open "Select * from Orders", oConn51Testing软件测试网 o(i c9lV&|V p
Do Until oRst.EOF   
"w#t*|8m R0 For each oField in oRst.Fields      51Testing软件测试网ypY.TR[g
   Print oField.Name & " = " & oField.Value  51Testing软件测试网/H-H c'?/A#A.c
 Next   
|)dJaZdc0Print String( 20, "-" )   
,] X*\t:x[0NW9T0oRst.MoveNext
v N2q"g1tP9RD0loop
#MC i4s0O_
bLT0oRst.close51Testing软件测试网}O:PV-ir5X} `Rn4] NG
oConn.close51Testing软件测试网'~c!{B3eS_

-L-|'aL+?JW02。 Database功能收集：51Testing软件测试网 ?B#|1{-\8q

9IZ%cgv$`051Testing软件测试网0x&EIpAO9[
'Example of How to use functions.

$z"^,F3r#lIGv0''************************************************************************************
(Tl&Q{MiqW0' Example use DSN created for database of Flight sample application.
h;mLD:HYt w0''************************************************************************************
Hnqr2nmS~;U F0SQL="SELECT * FROM ORDERS"51Testing软件测试网S6QNb(k.Z
connection_string="QT_Flight32"51Testing软件测试网 vq$Y|_x \f
 51Testing软件测试网P[NzGt \
isConnected = db_connect ( curConnection ,connection_string )51Testing软件测试网gc;d"Mu)@}[V
If isConnected = 0 then51Testing软件测试网.lP*^Mm
    ' execute the basic SQL statement
_+Z$gOh8gY8UY0    set myrs=db_execute_query( curConnection , SQL )51Testing软件测试网K-V q(J-lE6R;uV"\
   
?#ODmu.E7Z!H]0    ' report the query and the connection string
2o3v_ RmlI!n0R5n0    Reporter.ReportEvent micInfo ,"Executed query and created recordset ","Connection_string is ==> " & connection_string & " SQL query is ===> " & SQL51Testing软件测试网4KR&a@4RG#B6t
    ' show the number of rows in the table using a record set
@mH,LF
B-U1zg0    msgBox " Quantity of rows in queried DB ( db_get_rows_count )==> " & db_get_rows_count( myrs )51Testing软件测试网)G-\/i L!g g8u
    ' show the number of rows in the table using a new SQL statement
S9R^ F1{/Bl0    msgBox " Quantity of rows in queried DB (db_get_rows_count_SQL ) ==> " & db_get_rows_count_SQL( curConnection , "SELECT COUNT(*) FROM ORDERS" )
?;pSAi4ofF w$J%}0 
/|E#B2G!_L|~C0    ' change a value of a field in an existing row51Testing软件测试网%dwP F/o0R$S
    rc = db_set_field_value (curConnection, "ORDERS" , "Agents_Name" , "test", "Agents_Name", "AGENT_TESTER")
/RTS ED![e-i;s0 51Testing软件测试网g [0SxP ldpR
    ' examples of how to retrieve values from the table51Testing软件测试网Y_-vrn
    msgBox "val row 0 col 0: " & db_get_field_value( myrs , 0 , 0 )
K%X,UwqcB r1D"k0    msgBox "val row 0 col 1: " & db_get_field_value( myrs , 0 , 1 )51Testing软件测试网zp!`)bD:G/B4I)w
    msgBox "val row 1 col Name: " & db_get_field_value( myrs , 1 , "Agents_Name" )51Testing软件测试网P4v#zl%g*{Q
    msgBox "val SQL row 1 col Name: " & db_get_field_value_SQL( curConnection , "ORDERS" , 1 , "Agents_Name" )
d-b:ELi `_h"e0 51Testing软件测试网F[-L;T.oJ
    db_disconnect curConnection
md!Hz `0ye0End If51Testing软件测试网$FpVU7OG
 51Testing软件测试网{'?1^3eZ7S w {
''****************************************************************************************
2Z/@3mb{9`0 51Testing软件测试网'g:~Ri^FYj&Z
 51Testing软件测试网Ng@6j5V1Q
|
' Database Functions library51Testing软件测试网5]/zfl_5R
'******************************************************************************************
k)N]*OO7O&t0'db_connect
/BSigJ!p]0' ---------------
y-} uoe0' The function creates a new connection session to a database.51Testing软件测试网C4So$o$N2e7~
' curSession - the session name (string)51Testing软件测试网
U6\7_~Qn1`
' connection_string - a connection string
1U9G_9^wU5yTrx0' for example the connection_string can be "DSN=SQLServer_Source;UID=SA;PWD=abc123"51Testing软件测试网-^3ghU@kf+cCC
'******************************************************************************************51Testing软件测试网/EOj7z7s\v
Function db_connect( byRef curSession ,connection_string)
C0]w%{;C8\3Y
Qek0    dim connection51Testing软件测试网"JOW%t }pn%S
    on error Resume next
"y.R"\B |1b,D0    ' Opening connection51Testing软件测试网v'g!mT T'Rn`6J
    set connection = CreateObject("ADODB.Connection")
M@%aX
T D%i|0    If Err.Number <> 0 then51Testing软件测试网Qz'A5] W6b QH1G
        db_connect= "Error # " & CStr(Err.Number) & " " & Err.Descrīption51Testing软件测试网d,M*pfF0V|
        err.clear51Testing软件测试网)R(I*I+j
A"kp%vW
        Exit Function51Testing软件测试网%c,q4k} \B
YT
    End If
&]%Yeeb xt/v0 
r{4sVF1o \0    connection.Open connection_string51Testing软件测试网JGV)?,u
    If Err.Number <> 0 then51Testing软件测试网#K,Bs%k`ta#a-ds
        db_connect= "Error # " & CStr(Err.Number) & " " & Err.Descrīption51Testing软件测试网'X0^.a*zu!T+W5@
        err.clear
"pI pQC.D9G0        Exit Function
(OG IJ&gR5Y kL0    End If
(L.s{O$q9I0    set curSession=connection
$i"xN| j0g0    db_connect=0
DoOa\jP0End Function51Testing软件测试网3nN(K(?n\,NB
 51Testing软件测试网*r`
u?\(w
'********************************************************************************************
?@g(_KiwB0' db_disconnect
iG^A+w1V!sw!A*K0h@0' ---------------------
)[w:q
?-Q2t A0' The function disconnects from the database and deletes the session.
W2qiF e:[J0' curSession - the session name (string)
(AEA6si9}'W0'********************************************************************************************
H oXk7k#}UK0Function db_disconnect( byRef curSession )51Testing软件测试网hD$Q+`#h yk
    curSession.close51Testing软件测试网8QuN'z0gC*E%xx9KUE
    set curSession = Nothing
d+iHv0Z#J[.dU0End Function
!DxC&b8@0 51Testing软件测试网|6R7Z cv
'*********************************************************************************************
|a(YF6Y9O1\0' db_execute_query
?'Ma)[
z4r0' ---------------------------51Testing软件测试网}'EAJ3T$Y6h
' The function executes an SQL statement.51Testing软件测试网0z K!G_(Ga8z\
' Note that a db_connect for (arg1) must be called before this function51Testing软件测试网2S$F$t'ac:v*d
' curSession - the session name (string)
9z3j } an
jp"G0' SQL - an SQL statement51Testing软件测试网
Wa{2cZdeUng'l
'**********************************************************************************************
cY#M ACT2Ic0Function db_execute_query ( byRef curSession , SQL)51Testing软件测试网5FE0{H0c8lD
    set rs = curSession.Execute( SQL )51Testing软件测试网 o1D5yD^g!g
    set db_execute_query = rs
:om6J$`#Z3P+vsz0End Function
-`a-p[am)Lx`0 
xD}wt)CR0''***********************************************************************************************
wT$^q;DZxS2[0' db_get_rows_count51Testing软件测试网s]p4[jY
' ----------------------------51Testing软件测试网 pQ#b"y2UB"t
' The function returns the number of rows in the record set51Testing软件测试网K/KbE"k1{
' curRS - variable , contain record set , that contain all values that retrieved from DB by query execution51Testing软件测试网VD t |t} a
''***********************************************************************************************51Testing软件测试网RDK;s;V_
MFr
Function db_get_rows_count( byRef curRS )
kM2p H-C0    dim rows51Testing软件测试网C5p0U^K
    rows = 0
P
S7Q2I(H4S0    curRS.MoveFirst51Testing软件测试网b,{+c2} R"N4{+Aw
    Do Until curRS.EOF51Testing软件测试网d"AT!f z4u.e
        rows = rows+151Testing软件测试网.[V_f\h+o p
        curRS.MoveNext
)U u7d!OH;M0    Loop
:]l;Y$q w-G0    db_get_rows_count = rows
0I#^R3N*mZ(X.~N5b a ]0End Function
D%Omip%qwBQ0 
}&B!eP?0''************************************************************************************************
Z9`6R U~i5_.e0' db_get_rows_count_SQL51Testing软件测试网d WMHb4T
' ------------------------------------51Testing软件测试网G#kBm!Y)fI-J[
' The function returns the number of rows that are the result of a given SQL statement
Q0C*v9P&P%E#e0' curSession - the session name (string)
b%n D9QZeA8tv0' CountSQL - SQL statement
av])ZO]1A~g#_u3r0''************************************************************************************************
6_*{yB M#t{0Function db_get_rows_count_SQL( byRef curSession ,CountSQL )51Testing软件测试网S2iAu ?&EbPh
    dim cur_rs
zv,|f$O0    set cur_rs = curSession.Execute( CountSQL )
%JyXF&QN0    db_get_rows_count_SQL = cur_rs.fields(0).value
(?e-h]2_e e|V6PR"O0End Function51Testing软件测试网pY{L;l
 51Testing软件测试网Dp's#BDf7[
''*************************************************************************************************
C.UYJ9F%No8u6G)D3d2?0' db_get_field_value_SQL
+cnw*e+|0r]0' -----------------------------------
0Q&?,j~.@ME/u0' curSession - variable denote current active connection
_tp9T ?e p{(Fwh$c0' tableName - name of the table , where value should be retrieved51Testing软件测试网 s:R*}Y-EE
' rowIndex - row number
RHHT+Zu!jD8F0' colName - the column name.51Testing软件测试网"C{K9tm#GfS
'*************************************************************************************************
cot n
~|q/d0Function db_get_field_value_SQL( curSession , tableName , rowIndex , colName )51Testing软件测试网(IxHOMB8A7e
    dim rs
9e5J,Pi4f*kK&e#rOjq0    SQL = " select " & colName & " from " & tableName51Testing软件测试网a3fJ&r$yCUa%l
    set rs = curSession.Execute( SQL )51Testing软件测试网I%ZZ,Q I2H+O?J]

    rs.move rowIndex51Testing软件测试网w,fmM;O,r
    db_get_field_value_SQL = rs.fields(colName).value51Testing软件测试网C6Z)s ]
YS/z
End Function

K;L L@;Q0'*************************************************************************************************
` d;UtxM0' db_get_field_value51Testing软件测试网j+yy+? ~"W|n
' --------------------------51Testing软件测试网uX"A V8?l"b X}
' The function returns the value of a single item of an executed query.51Testing软件测试网#o2s)CLG"V g6_+Es
' Note that a db_execute_query for (arg1) must called before this function
$N-SS&^9]!zE0 
Y-kG3vB0X1^0' curRecordSet - variable , contain record set , that contain all values that retrieved from DB by query execution51Testing软件测试网^,[Gf:g'@m/uh
' rowIndex - the row index number (zero based)51Testing软件测试网AP'EvFH
' colIndex - the column index number (zero based) or the column name.51Testing软件测试网aH)J]S
' returned values51Testing软件测试网G\ lwy&T2r
' -1 - requested field index more than exists in record set51Testing软件测试网&T?b0R%kqHV)Z#?
'*************************************************************************************************
1Ag)Z,X_'Rg$Hag|0Function db_get_field_value( curRecordSet , rowIndex , colIndex )51Testing软件测试网0j{-bAaS-o
    dim curRow
0l!hQyoe0 51Testing软件测试网;]_*nB(L@
    curRecordSet.MoveFirst
Gw-cf0B~.}0    count_fields = curRecordSet.fields.count-1
"U,J)q Yv&~A-BeNk0    If ( TypeName(colIndex)<> "String" ) and ( count_fields < colIndex ) then
*rMCTX]6N0        db_get_field_value = -1 'requested field index more than exists in recordset
c#@Z/Xfi7e/Q0    Else51Testing软件测试网4q1H+g!P@r
        curRecordSet.Move rowIndex
{ ZKQ0o0        db_get_field_value = curRecordSet.fields(colIndex).Value51Testing软件测试网4`O+su3Q
    End If
~#F,Q?VZ0End Function51Testing软件测试网E*R\l
BR%d
 51Testing软件测试网 n5?(B.FS
'*************************************************************************************************51Testing软件测试网w'F/U&k"~2d
' db_set_field_value
a9R4U;dRi Cq|#M"Y0' ---------------------------
!kRww!F)KW0' The function changes the value of a field according to a search criteria.51Testing软件测试网e w7`[-YCP#RhPn
' We search for a certain row according to a column name and the desired vale, then we change a value in that row according
7TG.~"L.vc(@.R V0' to a desired columns51Testing软件测试网D K,@
u
e
 51Testing软件测试网+RR x$jP3@3zn B
' curConnection - the session name (string)
$~vNGv0' tableName - name of the table , where value should be retrieved
_Jmf3SC0' colFind - the column we search the criteria in51Testing软件测试网R(yAkW,N%vFQ
' colFindValue - the value we search in the column51Testing软件测试网#C)~7I1N
W)T4XIbah
' colChange - the column were we want to change the value
.~a8h }*j
g q#m`0' colChangeValue - the new value
9`2U3HG3E4j$T0 
e0[e9h8{w0' returned values
[TZ"Rv(bxc e_0' -1 - requested field index that doesn't exists in the recordset
?
C5ha-UY0'*************************************************************************************************51Testing软件测试网R n4h%h7l'@+_![N
Function db_set_field_value(curConnection, tableName , colFind , colFindValue, colChange, colChangeValue)
v,O _lw$oLe0    dim curRow51Testing软件测试网y5U+t+|6l#?$kVn\D
    dim updateSQL
~G{AR gd0    dim checkSQL
G)_Ih;jH;o0 
7GOQhf0    checkSQL = "select * from Details"51Testing软件测试网5dP7^/~]!Un
    set myrs1 = db_execute_query( curConnection , SQL )
r[4iJP0    myrs1.MoveFirst51Testing软件测试网m^mK m Pfe
    count_fields = myrs1.fields.count51Testing软件测试网f)D:f3OH
    If ( TypeName(colFind)<> "String" ) or ( TypeName(colChange)<> "String" ) then51Testing软件测试网ZW/f&X%ZI
        db_set_field_value = -1 'requested field index that doesn't exists in the record set
;vfitg3`l0    Else
4_Z5{)|0v*T0        updateSQL = "UPDATE " & tableName & " SET " & colChange & "='" & colChangeValue & "' WHERE " & colFind & "='" & colFindValue & "'"51Testing软件测试网@v)jCQwZt7ag
        set myrs1 = db_execute_query( curConnection , updateSQL )
6I#R;F;o^nO0        db_set_field_value = 1 'operation suceeded51Testing软件测试网r1p7R p2E?,}
    End If51Testing软件测试网a3|w_m2gN9v%e
End Function51Testing软件测试网j5n#RZ
ZY0H
 51Testing软件测试网h:w.aOn!H8v
'*************************************************************************************************
'Zh#D1B2p]D0' db_add_row
!U-y)}*M0Y
?0' -----------------51Testing软件测试网8l!w Z:XqF:RP#_y
' The function adds a new row to the desired table
WF1B a3v1]$BT3{0 51Testing软件测试网K#V;kc$\-ZhE
' curConnection - variable , contains a recordset , that contains all the values to be retrieved from DB by query execution51Testing软件测试网0Oe6B.w5f;`p_o
' tableName - name of the table , where value should be retrieved from51Testing软件测试网L_ G8|^qf
' values - array that contains values to be entered in a new row to the table.

' Note: the function must receive values for all the columns in the table!
dNZ9D_{)X5I0' returned values51Testing软件测试网{0d.o t0^
' -1 - the number of values to be entered to the table doesn't fit the number of columns51Testing软件测试网bm*G}9SJ'Y
' 1 - execution of the query succeed and the data was entered to the table51Testing软件测试网s'_ yNCV"Ly9H _*v%j
'*************************************************************************************************
~o~ ~*]qz0Function db_add_row(curConnection, tableName , byRef values)51Testing软件测试网cfZEmO
    dim i
Y5^3r2o)D_0    dim updateSQL
)Jq@"M?9I7?N0    dim myrs1
Xr XJ#y^y0 51Testing软件测试网GmEW!b:xT)iF$N
    updateSQL = "INSERT INTO " & tableName & " VALUES ("51Testing软件测试网$z'x3_H^}-\Y@9u
    arrLen = UBound (values) - LBound (values) + 1
(x\3f6MSPM"x0 51Testing软件测试网p$YK0Q)[vj*g|3d
    set myrs1=db_execute_query( curConnection , SQL )51Testing软件测试网_I#v\&^j'X5cr!q8[
    myrs1.MoveFirst51Testing软件测试网!dT d0Gy T0z3k4t~N
    count_fields = myrs1.fields.count
b2Z([
PH["O i:q+M8p0    ' check if numbers of values fit the numbers of columns51Testing软件测试网CR1V{dr1V'y
    If arrLen <> count_fields then51Testing软件测试网(q^p+Z3^z h
        db_add_row = -151Testing软件测试网+H;Lrc!L9^ ~W i
    Else
-\Go*eA`4Q0        For i = 0 to arrLen-1
OK rm$B&p0            updateSQL = updateSQL & values (i)51Testing软件测试网6gy#l)dkz+B
            If i <> arrLen-1 then51Testing软件测试网,FB;Wl+qR5lt
                updateSQL = updateSQL & ","
.~V0\}k%Z0            End If51Testing软件测试网B%k5v9EUl:G&@qhfH
        Next
U#Pu)F?0        updateSQL = updateSQL & ")"
%?!p%vEwl-M0        set myrs1 = db_execute_query( curConnection , updateSQL )
'LV&t]2C#aT:?9I0        db_add_row = 151Testing软件测试网xU2P ?W'p&Y
     End If51Testing软件测试网3M3lr8RSqT-l@
End Function
.L\ s,OL-M0 
p yAS|9C2{1Q0'*************************************************************************************************
a&BU8I
W*M{w8YF1P I0' represent_values_of_RecordSet51Testing软件测试网MKY'D i+?*ad Qd
' ---------------------------------------------51Testing软件测试网#w7@ Xw$m
' the function reports all the values on fields in a record set51Testing软件测试网5H$f;do?;oo%z
' curRS - variable , contains the recordset , that contains all the values that were retrieved from the DB by the query execution
U ld6[ C8p0'*************************************************************************************************51Testing软件测试网6b1S-?.b2gu"]#[KPcA
Function represent_values_of_RecordSet( myrs)
&bblUb+COs0    dim curRowString51Testing软件测试网$c1y$e1KQwmV&{
    myrs.MoveFirst51Testing软件测试网2Ub(FU?f
    reporter.ReportEvent 4,"Fields quantity" , myrs.fields.count
fL7jC7bd},Q0    count_fields = myrs.fields.count-1
}0`4WS(Eu c4Q0    curRow=051Testing软件测试网O,rV }A
    Do Until myrs.EOF
b N g6Mf&SFI0        curRowString= ""
t$p1sF/RH0        curRow = curRow+1
Dn]zK-R$j0        For ii=0 to count_fields
2t%H*m8eB9L{l#p!E0            curRowString = curRowString& "Field " &"==> " & myrs.fields(ii).Name &" : Value ==>" & myrs.fields(ii).Value & vbCrLf51Testing软件测试网7txV@.G P{4F
        Next
1B1z1HA.nE0        myrs.MoveNext
J#o2p+XT \6U!K0        reporter.ReportEvent 4,"Current row"& curRow , curRowString
-L3T/BZI0    Loop
j%y#S9[D~0End Function51Testing软件测试网:^3l/~2C _ h0ib `

 51Testing软件测试网gN!i;w/g+I