Update Query in Exel using jdbc

praveen24by7

Dear team,

I have test data like below in excel sheet.

login password1 password2 prefix first last degree address city testresult
done20 password password Mr. Kumar Praveen PHD East Mada Street Chennai


am able to retrive the data using below code without any issues.

//to retrive the data from excel

var db = _getDB("sun.jdbc.odbc.JdbcOdbcDriver","jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\\sahi\\testbed\\usr_data.xls;readOnly=false","","");
$rs = db.select("select * from [Sheet1$]");

and passed same values to the my create_user function.
after Successfull creation i just wnat to udpate the same excel sheet with "Pass/Fail" in the "testresult" column based on the login.

//here is the code which i wrote to update.


_set($rwcnt, _table("patientresults").rows.length); // To check whether cerated user is available in the Users table in web page.

for ($j=1 ; $j<$rwcnt; $j++)
{

if (_condition(_getText(_cell(_table("patientresults"), $j, 1))==$rs[$i]["login"]))
{

db.update("update [Sheet1$] set testresult='created' where login='$rs[$i]["login"]'");

}
else {

db.update("update [Sheet1$] set testresult='not created' where login=$rs[$i]["login"]");

}

}


//error which i got

but i am getting error "JSERROR missing ) after argument list". Please verify my update Query and suggest me how i can proceed.

narayan

You need to concatenate the value of the variable $rs[$i]["login"] to the sql query string, and also enclose it in single quotes. (This is standard string concatenation)

if (_condition(_getText(_cell(_table("patientresults"), $j, 1))==$rs[$i]["login"])) { 
    db.update("update [Sheet1$] set testresult='created' where login='" + $rs[$i]["login"] + "'");
} else { 
    db.update("update [Sheet1$] set testresult='not created' where login='" + $rs[$i]["login"] + "'");
}

praveen24by7

Thank you Narayan for your fast reply.