Custom Recordset's

Posted by: county on 4 August 2017, 3:22 pm EST

  • Posted 4 August 2017, 3:22 pm EST

    Hi all,



    I'm trying to create a custom ADO recordset in memory, I already have a recordset pulled from a DB which is working correctly.



    For this second recordset (in memory) I'm not entirely sure if I have the syntax quite right. What I have so far is below, with a little bit of script for testing.



    I'm getting an error on line 17 (CustomRS.Fields.Append "name", adinteger) which is below as well. I've tried this line with and without setting the field type and length.



    I'm fairly new to VB script but any help would be great.



    ERROR: Line number: 17 Description:Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.



    SCRIPT SO FAR:



    Sub OnReportStart



    Dim Connection

    Dim RS

    Dim CustomRS



    Set Connection = CreateObject("ADODB.connection")

    Set RS = CreateObject("ADODB.Recordset")





    Connection.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data source=C:\Program Files\Insite\Cranstar\Server Shares\Insite Data\site1park.mdb; persist security Info=False"



    RS.open "SELECT * FROM Booking_header", Connection



    Set CustomRS =CreateObject("ADODB.Recordset")

    CustomRS.Fields.Append "name", adinteger

    CustomRS.open



    CustomRS("name") = 123



    rpt.sections("Pageheader").controls("CustomRS").datavalue = CustomRS("name")





    rpt.sections("detail").controls("DC").recordset = RS



    End Sub



  • Replied 4 August 2017, 3:22 pm EST

    Hello,

    You will need to use code as following to add a row of data to the recordset. Please refer to this help article regarding recordsets.

    CustomRS.AddNew "name", 123

    Further, I would like to inform you can have a single datasource for the report. From your code,it seems that you want to set only the text of a control in the PageHeader Section. If so, you can do it from its Text property

    Regards,
    Prantik
  • Replied 4 August 2017, 3:22 pm EST

    Hi,



    Thanks for the article, it looks helpful.



    I was aiming to create a new field and leave it blank for now (will populate it later), I believe the AddNew command just populates a row? The Text "123" was simply just an attempt to test the code works.



    I'm not just trying to set the text properties, I was just using this as a test to make sure I could print something to screen.



    My end goal is to create a custom RS from the original RS after I have done calculations from it.



  • Replied 4 August 2017, 3:22 pm EST

    Just another quick point. Some of the syntax in that article doesnt work in the report builder. Setting the connections and recordsets is a good example.



    For example, I've found that this:



    Dim conConnection As New ADODB.Connection

    Dim cmdCommand As New ADODB.Command

    Dim rstRecordSet As New ADODB.Recordset



    Should be written like this:



    Dim conConnection

    Dim cmdCommand



    Set conConnection = CreateObject("ADODB.connection")

    Set cmdCommand = CreateObject("ADODB.Recordset")





    Also the report builder really doesnt like me setting the cursor place and gives me the same error as in my first post?
  • Replied 4 August 2017, 3:22 pm EST

    Hello,

    I would like to inform that in order to use the scripts in the reports, you have to use VBScript and not VB6. Please take a look at the attached file. It contains a .mdb file and an RPX with the required script to create ADODB recordsets, modify it and then use the modified recordset in the report. The connection string for the recordset has been set to pull the .mdb file from the C: drive of your machine.

    Regards,
    Prantik

    2012/05/Report.zip
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels