Creating a record set

The Sage CRM API allows easy access to the database to select and update data. The CreateQueryObj(SQL, Database) method returns a record set that can be viewed and manipulated in the same way as, for example, an ADO record set.

The following example uses the CRMQuery object to display companies from the software sector that are ranked as prospects.

1
2
3
4
5
6
7
8
9
10
11
<!-- #include file ="sagecrm.js"-->
<%
Query = CRM.CreateQueryObj("SELECT * FROM Company WHERE Comp_Deleted IS NULL AND Comp_Type = 'Prospect' AND Comp_Sector = 'Finance'");
Query.SelectSql();
while (!Query.QueryEof)
{
    CRM.AddContent(Query.FieldValue("comp_name")+'<br>');
    Query.NextRecord();
}
Response.Write(CRM.GetPage());
%>

In this example, the CreateQueryObj(SQL, Database) method returns a CRMQuery object by specifying a valid SQL statement as a parameter. The default database is used, but you can specify another database by adding a second parameter to the CreateQueryObj(SQL, Database) method call.

You can expand the scope of your query by using relational database features such as joins and views.
To examine or copy system and custom views, go to<My Profile> | Administration | Customization | <Entity> | Views. Alternatively, scan the list of views in a database management tool such as SQL Server Enterprise Manager.

In this example, the returned query object is called Query. The SelectSql() method executes the Select query. You can use the ExecSql() method to run queries that don't return records, such as Delete, Update, and Insert.

In addition to encapsulating components to access and update the database, the CRMQuery object stores returned data.

This example uses a JavaScript while statement to iterate through the returned records until an end-of-file marker is found. Within the loop, the values stored for each company's name and email address are retrieved using the FieldValue property.

The AddContent(Content) method builds up a block of HTML that's displayed when the output of the GetPage() method is written to the ASP page using the JavaScript Response.Write method.