HOWTO? Customize Error Message when querying Database

[Lidschi]

Hi Newsgroup,

I have the following problem:
I query an SQL Server database. My users have to fill in two text box before
they can query the database. If they enter the information in one text box
incorrect and then press the Query Button, some standard (default)
information is displayed. Is there any way to customize this message.

Thanks in advance for your help.

Kind regards
Lidschi.

 

[KjellSJ]

Double click the 'Query' button and change Action to 'Rules and custom code',
set the button ID to 'btnQuery', then click 'Edit form code'.

Enter this code to perform the query operation, then add your validation and
custom message code:

function btnQuery::OnClick(eventObj)
{
// Write your code here
try
{
var loadDataSource = "Main query";

//call the Query method of the primary data source to load the data
XDocument.DataAdapters(loadDataSource).Query();

eventObj.ReturnStatus = true;
}
catch(ex)
{
XDocument.UI.Alert("Failed while loading from web service.\r\n" +
ex.number + " - " + ex.description);
eventObj.ReturnStatus = false;
}
}

 

[Lidschi]

Hi KjellSJ,

thanks for your help very much. There is still one problem that exists.

I did enter the code and everything works fine so far. But the problem is:
If my users enter their username and password (supposing the password is
wrong) the Query works just fine but returns no values. Before with the
Submit button it showed "No records can be returned). What I want to do is to
customize this error message and state instead of "no records..." "Username
or Password incorrect".

Regards
Lidschi

 

[KjellSJ]

If zero records returned always means that the user name or password is
wrong, then count the number of rows in your table after the query and show
your message:

//query completed, count number of rows returned
var nodeList =
XDocument.DOM.selectNodes("/dfs:myfields//s1:YourTableRowDataSourceElement");
if(nodeList.length==0)
{
XDocument.UI.Alert("No results, user name or password is wrong!");
}

KjellSJ
http://kjellsj.blogspot.com

 

[Lidschi]

Hi KjellSJ,

thanks so much. I think we are very close to my solution. The code now looks
like this ( I have to admit that I am not too much into coding yet)

Now I get the message every time a query is executed.

Thanks in advance for helping me again.

Kind regards
Lidschi.

function btnQuery::OnClick(eventObj)
{
// Write your code here
try
{
var loadDataSource = "Primäre Verbindung";

//call the Query method of the primary data source to load the data
XDocument.DataAdapters(loadDataSource).Query();

eventObj.ReturnStatus = true;
}
catch(ex)
{
XDocument.UI.Alert("Failed while loading from web service.\r\n");
eventObj.ReturnStatus = false;
}
//query completed, count number of rows returned
var nodeList =
XDocument.DOM.selectNodes("/dfs:myfields//d:q_view_resources_europe");
if(nodeList.length==0)
{
XDocument.UI.Alert("No results, user name or password is wrong!");
}
}

 

[KjellSJ]

First of all, move the code below the try-catch block to just after the
..Query() line.

Then insert the debugger statement before the .selectNodes() line:

//call the Query method of the primary data source to load the data
XDocument.DataAdapters(loadDataSource).Query();

//BREAK INTO THE DEBUGGER, INSPECT XML USING IMMEDIATE WINDOW
debugger

//query completed, count number of rows returned
var nodeList =
XDocument.DOM.selectNodes("/dfs:myfields//d:q_view_resources_europe");
if(nodeList.length==0)
{
XDocument.UI.Alert("No results, user name or password is wrong!");
}

eventObj.ReturnStatus = true;



Save the script. Run the form and click your query button. Use the immediate
window in the debugger to check the content of the returned XML:

? XDocument.DOM.xml

Check carefully that your XPath has the correct namespace prefixes.

Also note that my example code queries a web service, check the InfoPath SDK
for details about querying a SQL Server datasource.

KjellSJ