HOWTO? Customize Error Message when querying Database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lidschi - 28 Apr 2005 19:42 GMT

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.

Reply to this Message

KjellSJ - 29 Apr 2005 11:24 GMT

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;
    }
}

> Hi Newsgroup,
>
[quoted text clipped - 8 lines]
> Kind regards
> Lidschi.

Reply to this Message

Lidschi - 30 Apr 2005 07:31 GMT

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

> Double click the 'Query' button and change Action to 'Rules and custom code',
> set the button ID to 'btnQuery', then click 'Edit form code'.
[quoted text clipped - 34 lines]
> > Kind regards
> > Lidschi.

Reply to this Message

KjellSJ - 30 Apr 2005 08:28 GMT

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

> Hi KjellSJ,
>
[quoted text clipped - 48 lines]
> > > Kind regards
> > > Lidschi.

Reply to this Message

Lidschi - 01 May 2005 07:01 GMT

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!");
    }
}

> 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
[quoted text clipped - 63 lines]
> > > > Kind regards
> > > > Lidschi.

Reply to this Message

KjellSJ - 01 May 2005 09:41 GMT

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

> Hi KjellSJ,
>
[quoted text clipped - 101 lines]
> > > > > Kind regards
> > > > > Lidschi.

Reply to this Message