Ask a Question

Data taking out from Database and write it back to Excel

mrajtc
New Contributor

Data taking out from Database and write it back to Excel

Hi ,

Can some one help me in the below feature?



1.      Take data out of the database & Save it in Excel file via Test Complete


2.      Take data out of an Excel file and save it to the database via Test Complete



I could able to read it from Excel, but dont know how to write it / insert into Database table

It will be greatful if some body has code.



I have a code for reading it from Excel DB & displaying.


Dim sAutomationPath


sAutomationPath

sAutomationPath = "D:\Database\"


 



Function ConnectDB


ConnectDB

Set AConnection = ADO.CreateADOConnection


Dim sConnectionString


 


sConnectionString = _


"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _


"DriverId=790;" & _


"Dbq=" + sAutomationPath + "Database_Test.xlsx;" & _


"DefaultDir=" + sAutomationPath


 


AConnection.ConnectionString = sConnectionString


AConnection.LoginPrompt = False

 



AConnection.Open


Set ConnectDB = AConnection



End Function


'********************************************************************



Function FetchDB (sTableName, sSheetName)





FetchDB (sTableName, sSheetName)

Dim iIndex


'Get ADO Connection Object

 



Set AConnection = ConnectDB ()


Set Driver = DDT.ExcelDriver(sAutomationPath + "Database_Test.xlsx", sSheetName,true)


MsgBox(sAutomationPath)


Set RecSet = AConnection.Execute_("SELECT * FROM "+ sTableName)


Set objDict = CreateObject("Scripting.Dictionary")


RecSet.MoveFirst


While Not RecSet.EOF


for iIndex = 1 to Driver.ColumnCount-1


objDict.Add (Driver.ColumnName(iIndex)), RecSet.Fields(Driver.ColumnName(iIndex)).Value


Next

RecSet.MoveNext



WEnd


Call DDT.CloseDriver(Driver.Name)


Set FetchDB = objDict



End Function



Regards,

Mohanraj R



ConnectDB FetchDB (sTableName, sSheetName)
4 REPLIES 4

Hi Mohanraj,


Please see the following How To entry to learn how to add data to an SQL table from TestComplete:

http://www.automatedqa.com/support/viewarticle.aspx?aid=9013


Best regards,
Alexey

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
mrajtc
New Contributor

Thanks a lot.

I am searching for this below requirement, can you help me in identifying the help link,



1. From UI, we will create a batch entry and submit it.

2. In Database, we need to validate whether that new batch is created.

I know this is nothing but database checkpoint. Help me in sending the help / sample programs for the same.



Thanks,

Mohanraj R
mrajtc
New Contributor

Forget to add this point, while going thru DB Checkpoint, i read that msadodc.ocx, In order to use this control i must install this library in my system. But i couldnt find it in my install extension folder, Is it a additional installer?

Hi Mohanraj,




Help me in sending the help / sample programs for the same.



Let me quote the "Database Checkpoints" ( http://www.automatedqa.com/support/viewarticle.aspx?aid=11037 ) help topic:




TestComplete includes sample projects (one for each supported language) that demonstrate how to create database checkpoints:

<TestComplete Samples>\Checkpoints\Database Checkpoints\


I guess, that's what you need. As for the msadodc.ocx component, it's a Microsoft component as it's mentioned in one of the help topics, and I believe that you have it installed in your system.


Best regards,
Alexey

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
cancel
Showing results for 
Search instead for 
Did you mean: