I was facing an issue with AJAX for last two-three days. Let me explain the issue:
I have a search screen. On this screen I have a Search “TextBox” and Search “Button”. While user is inputting any text and click on Search button, it is going to search into the database and retrieve the records.
Now I have SQL Express 2008 installed in my machine. I have to search in database table “Test” and this table contain columns named “textID” as integer & “testXml” as XML data type.
Now here I have to made search on this column “testXML” in “test” table. If during search records will find, we have to retrieve those records and display them on search screen. I have written the following query to retrieve records where keyword should match the “Name” element in “testXML” datatype.
SELECT
[TestID],
[TestXML]
FROM
Test
WHERE
[TestXML].exist(‘/Test/Name()[contains(fn:lower-case(.),fn:lower-case("aaa"))]‘) = 1
Note: In above query we have used XQuery to check the “Name” element contains “aaa” text. Format of the “TestXml” is as follows:
Suppose we have following entries in “Test” table:
| testID | testXML |
| 1 | <NewTest>
<Test><Name>aaa</Name></Test> <Test><Name>bbb</Name></Test> </NewTest> |
| 2 | <NewTest>
<Test><Name>ccc</Name></Test> <Test><Name>ddd</Name></Test> </NewTest> |
| 3 | <NewTest>
<Test><Name>aaa</Name></Test> <Test><Name>eee</Name></Test> </NewTest> |
| 4 | <NewTest>
<Test><Name>AAA</Name></Test> <Test><Name>ggg</Name></Test> </NewTest> |
Now, when we are running following query, it is retrieving row with testID=1 and 4.
SELECT
[TestID],
[TestXML]
FROM
Test
WHERE
[TestXML].exist(‘/Test/name()[contains(fn:lower-case(.),fn:lower-case("aaa"))]‘) = 1
So when I am doing search in my search screen, it is working fine and retrieving two records with testID=1 & 4.
Now I have sent same code to client. I have sent all updated files to client. During testing we have found that it is not working on client’s server and giving following Ajax error:
Line: 4723
Error: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500
I tried to understand the issue. However did not success as I have not access of ftp and check files on server. I have updated the code in files and sent them to client. Then I have tested the solution. I have made the same thing many time, however did not understand the issue with the above AJAX issue.
At last to understand the issue, I have commented the code in code-behind file of that search scree. Then I uncommented some code and sent back to client. He has uploaded the source and I have tested the solution. I have repeated the same process many times and at last I have found the issue which is generating the AJAX error.
So now I have understood the issue. Do you know where the issue was?
The issue was in the SQL Query, which we have created to retrieve records from “test” table.
SELECT
[TestID],
[TestXML]
FROM
Test
WHERE
[TestXML].exist(‘/Test/name()[contains(fn:lower-case(.),fn:lower-case("aaa"))]‘) = 1
In this query, the issue is in the where condition. Note here we have user fn:lower-case() function in XQuery. Now as I have written above I have MS SQL 2008 on my machine. So I hve tested this query in MS SQL 2008 and it is working fine.
However the same query is not working on client’s server as he has “MS SQL 2005”.
I have searched on google for the same and I have found that the MS SQL 2005 is not supporting fn:lower-case(), while MS SQL 2008 is supporting this function.
At last I have updated this function and functionality is working fine.
I have learn one more stuff of “Khoda Kua Aur Nikla Chuha”:)
Recent Comments