Wednesday, April 16, 2008

Problem Numeric check in SQL Server

In my datase i had a table which contains both numeric and characters and the table datatype was Varchar, and i have to find the min of the of the numeric value. In sql server the function ISNUMERIC(expr) was the solution to get rid of the character value.
The ISNUMERIC function determines whether an expression is a valid numeric type. returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. So i used the query like below
Select min(ColName) from demo where ISNUMERIC(ColName)=1
And i was getting my desired value.
But one day it started an exception in my asp.net page. And i could not find the reason.
After a long debugging i found that it was the problem with ISNUMERIC function of sql server. The ISNUMERIC function is returning 1 for some non-numeric character like '-' i.e
Select Isnumeric('-') returns 1 not 0.
And it was returning '-' from the query and error in asp.net page.
Then i tried with casting the result to int as.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1
And in this way the error was solved but the min value was always 0 as Cast('-')=0
Then i identified this abnormal characters as excluded them through the where clause.
Select Cast(min(ColName) as int) from demo where ISNUMERIC(ColName)=1 and eadr not in('+','-','$').
This behaviour occurs for these three charactes('+','-','$') i have found so far.
For more information check this link

Wednesday, April 9, 2008

XML Encoding Problem In SQL Server

In a project i was sending an XML file to a SP which was written by someone. It was insering values to a master table and some details table. The SP was taking some TEXT datatype as input for the XML. Now the problem was one day we found some
"XML parsing error: An invalid character was found in text content. sql server xml parse" error and i come to know that some datatype is having some non-ascii characters like "Ö","«" for which the error was generated.
We were told to just avoid throughing error so that the user can continue their work. The fastest solution i came accross was to format the string before puttin into the XML.
So i wrote a function which will take the String as input and return the ASCII string as output as below.
public static string getUTF8String(string str) {
char[] ch = str.ToCharArray();
byte[] by = new byte[ch.Length * 2]; //If all char is UNicode then max byte is 2 X length System.Text.ASCIIEncoding enc = new ASCIIEncoding();
int charUsed, byteUsed;
bool completed;
enc.GetEncoder().Convert(ch, 0, ch.Length, by, 0, by.Length, true, out charUsed, out byteUsed, out completed);
string res=enc.GetString(by, 0, byteUsed);
return res;
}
So the Non-Ascii character may change, but the workflow wsa not hampered.
But the best solution is to change the SP and the xml sent to the SP. In the SP you should make the input parameter as NTEXT rather than TEXT and in the XML mention the encoding as UTF-16 - not UTF-8. In this way it will accept all unicode characters.

Tuesday, April 1, 2008

Ajax Like File Uploading

Place a hiddenIFrame into the page and submit the form containing the file uploader (input type='file') to the hidden iframe by changing the target property of the form to the name of
the IFrame. Here i have made the target of form (id='formUpload') to "iframeHidden"
as my iframe name is "ifameHidden". For uploading the file you have to make the'
enctype' and 'method' attributes values as it is in the example.

I have made the action attribute of the page to UploadFile.aspx page. This is a
page where it counts the Files Collection of the Request Object and if it finds
any file in the Collection (Request.Files.Count>0), it saves the file in the same
folder with the same name as file. The saving code is very trivial, for a real world
applicatio you have to be carefull while saving like name overwrite,different path
ect.



<script type="text/javascript">
function uploadFile(id)
{
document.getElementById("formUpload").submit();
}
</script>



<iframe id="iframeHiden1" style="height:0px;width:0px;border:0"
name="iframeHiden" src="UploadFile.aspx"></iframe>

<form id="formUpload" target="iframeHiden" enctype="multipart/form-data"
action="UploadFile.aspx" method="post">


Ajax Upload File In HTML Form:<input type="file" onchange="uploadFile('file1')"
id="file1" name="fileUploadHTML" />


</form>


UploadFile.aspx Page Load Event


protected
void Page_Load(object sender, EventArgs e)

{



int noOfFile = Request.Files.Count;


if (noOfFile > 0)


{


string path = Request.PhysicalPath.Substring(0, Request.PhysicalPath.LastIndexOf("\\")
+ 1) + Request.Files[0].FileName;


Request.Files[0].SaveAs(path);


}
}