Friday, July 25, 2014

Database Scripts-II

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:\automation.mdb;"
Set objCom=Createobject("ADODB.Command") objCom.ActiveConnection=objCon
objCom.CommandText="insert into Emp values('automation infotech',89652,30000)"
objCon.Close
Set objCom=Nothing
Set objCon=Nothing
Set objCon=Createobject("ADODB.connection")
objCon.open"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\automation.mdb;"
Set objCom=Createobject("ADODB.Command")
objCom.ActiveConnection=objCon
Datatable.AddSheet("input")
Datatable.ImportSheet "C:\automation.xls",1,"input"
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  
----------
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\automation\Desktop\TestData2.xls")
Rc=mySheet.usedrange.rows.count
'Msgbox Rc
objCon.Provider=("Microsoft.Jet.oledb.4.0")
objCon.Open "C:\Documents and Settings\automation\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

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
objCom.Execute

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
intRowcount=Datatable.GetSheet("input").GetRowCount

3) Fetch data from a database, and compare with expected data in Excel file.
Set mySheet=myFile.Worksheets("Sheet1")
------------------------------------

No comments: