Results Files

[health4us]

I have an sql query to return all records from an access database table that match 2 criteria.

 

When i run the query from Access it works fine and displays several records in the results. However, when i do the query using voiceguide and try return the output using the $RV it only displays the output from 1 record.

 

How do i make it so voiceguide writes every record into the result variable?

 

----Query----

SELECT * FROM MembersCallLog WHERE (((MembersCallLog.JobId)="$RV[JobID]") AND ((MembersCallLog.MemberRef)="$RV"));

[SupportTeam]

You should be able to access all matching records. VG does not limit returned data to 1 row. DB Query module actually limits to 100 rows - to guard against large retrievals which may slow down the system - and it's unlikely that more then 100 entries would even need to be reported back to the caller.

 

From VG Help file:

 

ModuleTitle_ColumnIndex_RowIndex

 

If more then one item was requested in the query, the Column Index can be used to access the individual items in each retrieved row.

 

eg: module LookupTelFax uses a query: SELECT Tel, Fax FROM Customer WHERE CustId='$RV[Get CustId]' . We can use $RV[LookupTelFax_1_1] to access the contents of the Tel column and use LookupTelFax_2_1 to access he contents of the Fax column for customer 1. We can use $RV[LookupTelFax_1_2] to access the contents of the Tel column for customer 2, etc.

 

If you are still experiencing problems please send us a copy of VoiceGuide's Debug Printout which captures the problem, this will allow us to see what is going wrong.

(When running the script click on VoiceGuide's View menu and select 'Event Trace Log' option - any log information will then appear in this window. You must open the trace window before making the call.)

 

 

If you need to maniplute more then 100 entries it's probably better to use a V Script module and use ADO objects to perfom the queries.

[health4us]

I got you thanks... Next question is i understand this but what happens if there is say 10,000 records returned then would i need to put the RV in 10,000 times on the email i want to output the info to?

Thanks

Matt

[SupportTeam]

If you want to work with such large database queries then I'd recommend using VB Script module to retrieve the data and save it separately in one Result Variable - and then use that Result Variable in your email.

 

We'll probably in future allow users to change the '100 records' limit to something else (or remove the limit altogether). As I've mentioned before having this limit is useful now to ensure that the DB Queries don't take too long... right now such long queries would affect performance if other lines want to make a DB Query at the same time....

 

Right now if you would want to use 10,000 RVs you would need to actually list the 10,000 RVs in the email body - not a practical way of handling such large data sets - doing it programmatically from within a VB Script module is preferable when working with large data sets is necessary....

[health4us]

yes, thanks for your help.. I totally agree with you.

Can you tell me or show me an example of such a VB script please.

Thanks

Matt Thompson

[SupportTeam]

Some examples of ADO programming have been posted on forum before, plz see:

 

http://voiceguide.com/forums/index.php?showtopic=19

http://voiceguide.com/forums/index.php?showtopic=619

http://voiceguide.com/forums/index.php?showtopic=819

http://voiceguide.com/forums/index.php?showtopic=959

 

also I'd recommend searching for:

 

CreateObject("ADODB.Connection")

 

on Google Groups ( www.google.com/groups ) and on msdn.microsoft.com/scripting