Graphics

Is there a way to use scripting, or even Visual Logic, to change the parameters of a database query?

We're trying to set up an election FS using ENPS Stats. The data that ENPS pushes out doesn't include anything we can use as a Title or Subtitle for the FS, so we're querying the Race ID. But that means that the scene can only be used for one race, based on that query.

What I'd like to do is be able to change the parameters in the query based on user input, like the Title. So, if the title that a user inputs is "Amendment 4" that the query changes the Race ID to whatever that race is. And if the user inputs "Sarasota County Commission," the query changes the Race ID to whatever that race is.

I hope that makes sense.

Thanks.

You can change all the fields in the "Set Datalinq Properties" window using scripting.

For example:

dim data, vari as xpTextObject
Self.GetObjectByName("Data", data)
Self.GetObjectByName("Vari", vari)
data.Datalinq.Column = vari.text

Put this script in OnBeforeOnline. It will change the value of the 'Column' Datalinq property of the "Data" text object with the content of the "Vari" text object. Ex. Putting 3 into the text object "Vari" will set the Datalinq Column property of Text Object "Data" to 3.

Look for xpDatalinq Object in the XPression SDK file.
#XPression

And if you want to change the value of a Datalinq Key:
dim data as xpTextObject
dim vari as xpTextObject
dim keys as xpDatalinqKeys
dim key as xpDatalinqKey

self.getObjectByName("vari", vari)
self.GetDatalinqKeys(keys)
keys.GetKeyByName("hey", key)
key.AsString = vari.text
Self.RefreshDatalinqs()

#XPression

Thanks Garner. We'll look into it. One of my engineers is familiar with scripting, so hopefully he'll understand more than me.

Since I don't quite understand, let me ask this... if we put:

SELECT RaceCandidateDisplayName,RaceCandidateVotes,ROUND(RaceCandidatePercentVotes,0),RaceCandidateWinner FROM dbo.ENPSElectionRaceResults WHERE RaceID=190 ORDER BY RaceCandidateVotes DESC

into the TABLE field before clicking Set, can we change the "190" in that based on user inputted data? If we had a list of the races with the race IDs that ENPS puts out, the user could input the race ID and a script could change that in all of the datalinqs, that would be amazing.

Currently I've got all the datalinqs running through Visual Logic. Does that matter?

As always, thank you! You guys are top notch when it comes to helping with this kind of stuff!
#XPression

Yes you could.
It would look something like this:

dim data, vari as xpTextObject

Self.GetObjectByName("Data", data)
Self.GetObjectByName("Vari", vari)

data.Datalinq.Table = "SELECT RaceCandidateDisplayName,RaceCandidateVotes,ROUND( RaceCandidatePercentVotes,0),RaceCandidateWinner FROM dbo.ENPSElectionRaceResults WHERE RaceID=" + vari.text + " ORDER BY RaceCandidateVotes DESC"


You can see that where "190" would be is now 'vari.text'.

This will work with Visual Logic as well, as long as you keep these scripts in OnBeforeOnline you should be fine. Let us know if you have issues with it, if there are problems it would probably just take some tweaking to get it working.

#XPression

You are the best! Thanks'
#XPression

OK, Garner, two more questions. I was off the last 3 days and am just now getting back to this. Here are my questions:

1. What do I do with the datalinq object in Visual Logic. Do I put the query in exactly how it is in the script, or does it stay blank? As it is, the query is being put in the TABLE section of the datalinq.

2. What if we have multiple queries for different datalinq objects? Really we would want that "vari" to be the same for every datalinq object, since we want the data to pull from the same record in the database for every field we want to populate. But, we're pulling from two different databases, one is the one above, "dbo.ENPSElectionRaceResults" and the other is "dbo.ENPSElectionRaceProperties" Also, I've done different queries for each datalinq object so the only thing being queried is the one column of the database that the datalinq object is concerned with.


#XPression

My awesome engineers figured it out. We don't have OnBeforeOnline, we are on V5.7. But they figured out how to make a data key. Thanks for all your help!!
#XPression