Query the database using ADO to retreve a single value - InfoPath Dev

InfoPath Dev

Use our Google Custom Search for best site search results.

Query the database using ADO to retreve a single value

Last post 09-15-2008 12:19 PM by aesalazar. 0 replies.
Page 1 of 1 (1 items)
Sort Posts: Previous Next
  • 09-15-2008 12:19 PM

    Query the database using ADO to retreve a single value

    Hey guys.  I thought this would be a simpler task then it is turning out to be but maybe I am missing something.  I have done alot of VIsual Studio .NET programming over the years and am use to using ADO to build connections to the back end database to retrieve specific values when randomly needed.  But I cant seen to pull it of in IP using jscript.  In this project, things are sitting in an MS ACCESS database.

    What I want to do is create a data entry form that the user fill out a bunch of data on.  Most of if will be completely "freehand" but there is one item, the location ID field, that has to be not only unique but incremental as well based on a qualifying type.  So, for example, they may enter a series of locations like:







    The location types are fixed (Type1 and Type2 in this example) that they choose from a dropdown.  So the important number is that last one which will be sequential based on what is in the database (based on the next available number AND the location type).

    So the trick is to find out what was the last number entered in the database for that particular location type.  Normally, I would just create a ADO connection and recordset/dataset and run a query off the table that stores it.   So something like this (I realize this is SQL Server but it was the quickest I example could find):

    // command and recordset variables
    var Connect = "Provider='sqloledb';Data Source=" + Request.ServerVariables("SERVER_NAME") + ";" +
    "Initial Catalog='pubs';Integrated Security='SSPI';";
    var Cnxn = Server.CreateObject("ADODB.Connection");
    var cmdAuthor = Server.CreateObject("ADODB.Command");
    var rsAuthor = Server.CreateObject("ADODB.Recordset");
    var rsAuthor2 = Server.CreateObject("ADODB.Recordset");
    var SQLAuthor2, strMessage, strMessage2;

    Then I would create a SQL string for the query to get the last number used, build the command and recordset, and get the value. But it seems IP will not work with the server objects. I get an error whenever I try declaring them that it is not an option. I saw that IP has its own implementation of ADO objects, Application.NewADODBConnection for example, but I get the same error when I use it.

    I am trying to use dataobjects which seems to get me close but I do not see anything in its interface that will let me work with the data virtually. I am able to use it by creating a secondary data connection to the table that has the locations and can edit the command string to query out the last number but I cant figure out how to retrieve that last number without actually creating controls on the form to reference when pulling out the data. I can hide the section I suppose but I would like to find a cleaner way to do it.


    Filed under: , ,
Page 1 of 1 (1 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.