Database Scripts 2

Database Scripts-II

1) Insert Data into a database table using Database Command Object

Dim objCon,objCom
Set objCon=Createobject("ADODB.connection")

objCon.open"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\gcreddy.mdb;"

Set objCom=Createobject("ADODB.Command")
objCom.ActiveConnection=objCon

objCom.CommandText="insert into Emp values('G C Reddy',88233,30000)"
objCom.Execute

objCon.Close
Set objCom=Nothing
Set objCon=Nothing

2) Insert multiple sets of Data (using Excel sheet)  into a database table using Database Command Object

Dim objCon,objCom,strEmpName,intEmpNo,intEmpSal,intRowcount,i
Set objCon=Createobject("ADODB.connection")

objCon.open"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\gcreddy.mdb;"

Set objCom=Createobject("ADODB.Command")
objCom.ActiveConnection=objCon

Datatable.AddSheet("input")
Datatable.ImportSheet "C:\gcreddy.xls",1,"input"
intRowcount=Datatable.GetSheet("input").GetRowCount
Msgbox intRowcount
For i=1 to intRowcount step 1
    DataTable.SetCurrentRow(i)
    strEmpName=    DataTable.Value(1,"input")
    intEmpNo=    DataTable.Value(2,"input")
    intEmpSal=    DataTable.Value(3,"input")
objCom.CommandText="insert into Emp values( '"&strEmpName&" ',"&intEmpNo&","&intEmpSal&")"
objCom.Execute

Next

objCon.Close
Set objCom=Nothing
Set objCon=Nothing  

3) Fetch data from a database, and compare with expected data in Excel file.
----------
Dim objCon, objRs, objExcel, myFile, mysheet
Set objCon=CreateObject("Adodb.Connection")
Set objRs= CreateObject("Adodb.Recordset")
Set objExcel=CreateObject("Excel.Application")
Set myFile=objExcel.Workbooks.Open ("C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\TestData2.xls")
Set mySheet=myFile.Worksheets("Sheet1")
Rc=mySheet.usedrange.rows.count
'Msgbox Rc
objCon.Provider=("Microsoft.Jet.oledb.4.0")
objCon.Open "C:\Documents and Settings\gcr.GCRC-9A12FBD3D9\Desktop\Comp.mdb"
objRs.Open "Select EMPName from Employee", objCon



Do Until objRs.EOF=True
x=objRs.Fields("EMPName")
    For j= 2 to Rc
y=mySheet.cells(j,"A")

If x=y  Then
    Reporter.ReportEvent micPass,"Res","Name: "& y &"  Available"
'    Else
'Reporter.ReportEvent micFail,"Res","Name: "& y &"  Not Available"

End If
    Next
   
objRs.MoveNext
Loop
objExcel.Quit
Set objExcel=Nothing
objRs.Close
objCon.Close
Set objRs=Nothing
Set objCom=Nothing
------------------------------------