SilkTest Question 31 : How to update an Excel spreadsheet using SilkTest?

First of all the QA engineer should verify that ODBC data source, which SilkTest is going to use, is not set to read-only otherwise the Excel worksheet cannot be updated

The Microsoft's Excel OBDC driver does not support INSERT keyword, so QA Engineer will not be able to use this keyword in the SQL statement. Instead of INSERT keyword 4test code developer have to use to use the UPDATE keyword.

The following 4test example will update data in Microsoft Excel.

[ ]
[ ] HANDLE hDBC = DB_Connect("DSN=Documentation")
[ ]
[ ] HANDLE hSQL
[ ]
[ ] hSQLq = DB_ExecuteSQL(hDB, "UPDATE 'Sheet2$' SET 'Sheet2$'.Language='4Test' WHERE 'Sheet2$'.Name='Winrunner'")
[ ]
[ ] DB_Disconnect(hDBC)

Also take a look at SilkTest Interview Question 18: How to read data from Microsoft Excel worksheet?

SilkTest Question 18: How to read data from Microsoft Excel worksheet?

For QA purposes we are trying to read a block of cells from Excel spreadsheet and use values later for creating data driving automated test. The testing process set up in such way that we don’t want to use exact column names and prefer to just get data from cell numbers specified for example as B4:D11. Basically we want to obtain the cell values for all spreadsheet cells from B4 to D11 and print them out.

Try to create your test script based on the following code

[] hDBC = DB_Connect ("DRIVER=Microsoft Excel Driver (*.xls); FIRSTROWHASNAMES=1;READONLY=FALSE;
DRIVERID=790;DBQ=C:\QA.xls")
[] //run a SQL statement
[] hSQLq = DB_ExecuteSQL (hDBC, "SELECT * from [Sheet1$B4:D11]")
[] //while there are still rows to retrieve
[-] while DB_FetchNext (hSQLq, description, var1, var2)
[] print("{++i}:{ description } { var1} { var2} !")
[]
[] DB_Disconnect(hDBC)

For training purposes you have to take a look at SilkTest user manual for the following functions:

• DB_Connect
• DB_ExecuteSQL
• DB_FetchNext
• DB_FinishSql

and Datatypes:

• HDATABASE
• HSQL

Also take a look for the following answer about DBTester database functions.