Dataapapter.fill Vs Executescalar

[jph2658]

Looking at the logs you'll see that the [userName] module uses the DataAdapter.Fill where the [TaskCount] uses the ExecuteScalar method. When attempting to use $RV[userName_1_1] in another module it works fine, however when trying to use $RV[TaskCount_1_1] it does not work. Neither does the $RV[TaskCount_RowCount]. Why would one module use the DataAdapter.Fill method as apposed to the ExecuteScalar method? I'd like the [TaskCount] module to use the DataAdapater.Fill method so I can access the $RV[TaskCount_*] values.

 

-----------------------------------------------------------------------

 

103059.655 6 1 1 state [userName] DB Query

103059.655 6 1 1 rv replace start: [sELECT firstname + ', ' + lastname AS username FROM Users where userID=$RV[gUserID];]

103059.655 6 1 1 rvns: [PathSysVoice]{C:\Program Files\VoiceGuide\system\voice\}[PathApp]{C:\Program Files\VoiceGuide\}[PathDataVm]{C:\Program Files\VoiceGuide\data\}[PathVgSys]{C:\Program Files\VoiceGuide\system\}[scriptPath]{C:\Program Files\VoiceGuide\Scripts\PTR_phone\}[scriptsPath]{C:\Program Files\VoiceGuide\Scripts\PTR_phone\}[$RV_STARTTIME]{2009-09-30 10:30:53}[$RV_DEVICEID]{1}[DlgcVoice]{dxxxB1C1}[DlgcNetwork]{dxxxB1C1}[$RV_CIDNAME]{}[PathApp]{C:\Program Files\VoiceGuide\}[scriptPath]{C:\Program Files\VoiceGuide\Scripts\PTR_phone\}[scriptsPath]{C:\Program Files\VoiceGuide\Scripts\PTR_phone\}[$RV_CIDNUMBER]{}[FirstMenu_menuText]{Please Enter your 7 digit pager number}[FirstMenu_1]{Please Enter your 7 digit pager number}[FirstMenu_menuText_1]{Please Enter your 7 digit pager number}[FirstMenu_1_1]{Please Enter your 7 digit pager number}[FirstMenu_RowCount]{1}[PagerNumber]{8888888}[PagerNumber_PathTaken]{success}[userID_userid]{595}[userID_1]{595}[userID_userid_1]{595}[userID_1_1]{595}[userID_RowCount]{1}[gMenu]{3}[gOptionID]{0}[gUserID]{595}

103059.655 6 1 1 rv replace end: [sELECT firstname + ', ' + lastname AS username FROM Users where userID=595;]

103059.655 6 1 1 db strDatabaseName=[system.Data.SqlClient] strDbConnectString=[Data Source=172.18.154.153,1433;Database=Highland_Transportation_Stage;User ID=user_Highland;Password=7Hrc4NB0n1Y1] sAdoNetFactoriesAvailable=[|System.Data.Odbc|System.Data.OleDb|System.Data.OracleClient|System.Data.SqlClient|VistaDB.NET20|]

103059.655 6 1 1 db using ado.net data provider factory.

103059.655 6 1 1 t timer set 30 sec : EV_TIMEOUT_HANGUP

103059.655 6 1 1 timer set completed lTimeLeft250msUnits=120, iTimeoutCode=9001

103059.655 6 1 1 db fctry RunQuery_AdoNetFactory UserName, System.Data.SqlClient, Data Source=172.18.154.153,1433;Database=Highland_Transportation_Stage;User ID=user_Highland;Password=7Hrc4NB0n1Y1, SELECT firstname + ', ' + lastname AS username FROM Users where userID=595;

103059.655 6 1 1 db fctry new db thread started.

103059.655 21 1 1 db fctry connection create call.

103059.655 21 1 1 db fctry connection open call.

103059.655 21 1 1 db fctry connection open returned.

103059.655 21 1 1 db fctry DataAdapter.Fill init.

103059.655 21 1 1 db fctry DataAdapter.Fill call.

103059.670 21 1 1 db fctry DataAdapter.Fill returned.

103059.670 21 1 1 db read loop iColIdx=0, sBlob_DbColumn=, strField_Name=username

103059.670 21 1 1 rv add [userName_username]{Ashley, Cowart}

103059.670 21 1 1 rv add [userName_1]{Ashley, Cowart}

103059.670 21 1 1 rv add [userName_username_1]{Ashley, Cowart}

103059.670 21 1 1 rv add [userName_1_1]{Ashley, Cowart}

103059.670 21 1 1 rv add [userName_RowCount]{1}

103059.670 21 1 1 oVgmDbQuery_AdoNetCommon_Completed SqlQueryType=READER, RowsCount=1

103059.670 21 1 1 FindNextVgmTitleInPathList: next module title is=[Confirm]

103059.670 21 1 1 RunModule (timer redirect) [Play,[Confirm],37]

103059.670 21 1 1 qScr add evScriptEvent 9800 CMD_RUN_SAME_MODULE_AGAIN

103059.670 21 1 1 db fctry query completed.

 

-----------------------------------------------------------------------

 

103102.842 6 1 1 state [TaskCount] DB Query

103102.842 6 1 1 rv replace start: [sELECT count(*) as taskCount FROM RequestTA WHERE userID=$RV[gUserID];]

103102.842 6 1 1 rvns: [PathSysVoice]{C:\Program Files\VoiceGuide\system\voice\}[PathApp]{C:\Program Files\VoiceGuide\}[PathDataVm]{C:\Program Files\VoiceGuide\data\}[PathVgSys]{C:\Program Files\VoiceGuide\system\}[scriptPath]{C:\Program Files\VoiceGuide\Scripts\PTR_phone\}[scriptsPath]{C:\Program Files\VoiceGuide\Scripts\PTR_phone\}[$RV_STARTTIME]{2009-09-30 10:30:53}[$RV_DEVICEID]{1}[DlgcVoice]{dxxxB1C1}[DlgcNetwork]{dxxxB1C1}[$RV_CIDNAME]{}[PathApp]{C:\Program Files\VoiceGuide\}[scriptPath]{C:\Program Files\VoiceGuide\Scripts\PTR_phone\}[scriptsPath]{C:\Program Files\VoiceGuide\Scripts\PTR_phone\}[$RV_CIDNUMBER]{}[FirstMenu_menuText]{Please Enter your 7 digit pager number}[FirstMenu_1]{Please Enter your 7 digit pager number}[FirstMenu_menuText_1]{Please Enter your 7 digit pager number}[FirstMenu_1_1]{Please Enter your 7 digit pager number}[FirstMenu_RowCount]{1}[PagerNumber]{8888888}[PagerNumber_PathTaken]{success}[userID_userid]{595}[userID_1]{595}[userID_userid_1]{595}[userID_1_1]{595}[userID_RowCount]{1}[gMenu]{3}[gOptionID]{0}[gUserID]{595}[userName_username]{Ashley, Cowart}[userName_1]{Ashley, Cowart}[userName_username_1]{Ashley, Cowart}[userName_1_1]{Ashley, Cowart}[userName_RowCount]{1}

103102.842 6 1 1 rv replace end: [sELECT count(*) as taskCount FROM RequestTA WHERE userID=595;]

103102.842 6 1 1 db strDatabaseName=[system.Data.SqlClient] strDbConnectString=[Data Source=172.18.154.153,1433;Database=Highland_Transportation_Stage;User ID=user_Highland;Password=7Hrc4NB0n1Y1] sAdoNetFactoriesAvailable=[|System.Data.Odbc|System.Data.OleDb|System.Data.OracleClient|System.Data.SqlClient|VistaDB.NET20|]

103102.842 6 1 1 db using ado.net data provider factory.

103102.842 6 1 1 t timer set 30 sec : EV_TIMEOUT_HANGUP

103102.842 6 1 1 timer set completed lTimeLeft250msUnits=120, iTimeoutCode=9001

103102.842 6 1 1 db fctry RunQuery_AdoNetFactory TaskCount, System.Data.SqlClient, Data Source=172.18.154.153,1433;Database=Highland_Transportation_Stage;User ID=user_Highland;Password=7Hrc4NB0n1Y1, SELECT count(*) as taskCount FROM RequestTA WHERE userID=595;

103102.842 6 1 1 db fctry new db thread started.

103102.842 21 1 1 db fctry connection create call.

103102.842 21 1 1 db fctry connection open call.

103102.842 21 1 1 db fctry connection open returned.

103102.842 21 1 1 db fctry ExecuteScalar call.

103103.124 21 1 1 db fctry 4xecuteScalar returned.

103103.124 21 1 1 oVgmDbQuery_AdoNetCommon_Completed SqlQueryType=SCALAR, RowsCount=1

103103.124 21 1 1 FindNextVgmTitleInPathList: next module title is=[HasTask]

103103.124 21 1 1 RunModule (timer redirect) [Run VB Script,[HasTask],31]

103103.124 21 1 1 qScr add evScriptEvent 9800 CMD_RUN_SAME_MODULE_AGAIN

103103.124 21 1 1 db fctry query completed.[/size]

vbs_1_2.zip

[SupportTeam]

In the [TaskCount] module the SQL expression is:

SELECT count(*) as taskCount FROM RequestTA WHERE userID=$RV[gUserID];

This is a scalar expression, returning the count of matching rows, not row data.
If you want to retrieve and use the actual row data then an SQL query that retrieves row data should be used, eg:

SELECT * FROM RequestTA WHERE userID=$RV[gUserID];

The above will retrieve both row data and the row count.


The SELECT count(*) ... statement should have however resulted in the $RV[TaskCount_RowCount] being set.
This update has this _RowCount RV issue fixed: [old link removed]
Please download above update and install it over the top of your current VoiceGuide install.

Please let us know if you have further questions.

Edited 07/27/2017 12:54 AM by SupportTeam
changed download link