Return A Recordset From An Sql Query
Hi,
I am trying to create a VBScript that returns more than one record, how is this possible?
I need the user to select a value based on a dropdown list, on the data entry form. This value is then stored in a named data source.
I then have a text field, the data source for this is a VBScript that uses the previously populated named data source to perform an SQL query. I can get the query to work, however it only ever returns one record. I can see the other records by using the MoveNext method on the recordset.
I want bartender to treat each record in the recordset as a new label. I am using the OnPostPrompt event.
Here is the code
'Called after the data entry form is closed either at the beginning of print job or once
'every record, depending on the defined frequency options.
columnName = "flavour"
server = <removed>
db = <removed>
userId = <removed>
password = <removed>
dsn = "Driver={MySQL ODBC 5.2 UNICODE Driver};Server=" & server & ";Database=" & db & ";User=" & userId & ";Password=" & password & ";"
profile = Format.NamedSubStrings("SelectedProfile").Value
'Create connection
Set objCon = CreateObject("ADODB.Connection")
objCon.ConnectionString = dsn
objCon.Open
'generate SQL
strCon = "SELECT `labels`.`" & columnName & "` FROM `labels`, `profiles` WHERE `profile_id` = `id` AND `name` = '" & profile & "'"
'run query
Set rs = CreateObject("ADODB.Recordset")
Set rs = objCon.Execute(strCon)
options = ""
Do While NOT rs.Eof
options = options + rs(columnName).value + vbCR
rs.MoveNext
Loop
'MsgBox(options)
'Close connection
objCon.Close
Set objCon = Nothing
'set options
Value = options
I can get what I want to achieve working by using the database prompt, but being limited to manual text entry is not appealing, I would much prefer the user to be able to select the profile from a pre-determined list stored in the database.
Please help, this is driving me mad!
-
Hello Rickerman,
Thanks for your email.
I'm not exactly sure of what you are requesting, do you wish the user to be able to select a single record from a database using a dropdown list, and then make sure other objects source information from different fields of that selected record?
I don't fully understand what you meant when saying "I want bartender to treat each record in the recordset as a new label.". Isn't BarTender printing a different label for each record the user selects at print time?
*Not that if you are working with a custom database connection (through VB script) the supported BarTender connection won't work (you need to choose one or the other).
In any case, I can recognize the code you've provided from one of our examples, I'm attaching what it's probably a more advanced one for this same purpose that the ones we'be attached so far (that could probably be more helpful). Please take a look at it and let me know if it helps.
If you need further help please explain in more detail what exactly do you wish to achieve.
Regards.
0 -
Hello Rickerman,
Thanks for your email.
I'm not exactly sure of what you are requesting, do you wish the user to be able to select a single record from a database using a dropdown list, and then make sure other objects source information from different fields of that selected record?
I don't fully understand what you meant when saying "I want bartender to treat each record in the recordset as a new label.". Isn't BarTender printing a different label for each record the user selects at print time?
*Not that if you are working with a custom database connection (through VB script) the supported BarTender connection won't work (you need to choose one or the other).
In any case, I can recognize the code you've provided from one of our examples, I'm attaching what it's probably a more advanced one for this same purpose that the ones we'be attached so far (that could probably be more helpful). Please take a look at it and let me know if it helps.
If you need further help please explain in more detail what exactly do you wish to achieve.
Regards.
Hello Fernando,
Please see attached sample that I remade from yours, I added "distinct" to Downlist box and added a record "Apple" to fruits.xls, so 2 results will be returned when I search "Apple", I want to print these 2 records out, can I do?
Thanks!
MHYX
0 -
@mhyx: Try this: Put the code that is now in OnNewRecord into OnPostPrompt (maybe it also works in OnNewRecord - not sure. Try whatever location is most logical for your application ;)). Add the "Value = rs.Fields("xx")" line to OnIdenticalCopies. The script should pull the matching records (from the user entry selection) into a recordset, then print a label/record for all matching records. But at this stage, you still have to tell BarTender manually, how many labels you will be printing. To solve this, assign a named data source to the "Copies" entry in the print dialog. Then use rs.RecordCount after the recordset has been created in the script and assign its value to the "copies" data source.
0
Please sign in to leave a comment.
Comments
3 comments