How to create a simpler proper Exception Handler message for "Error converting data type varchar to bigint" on textbox's value if it's not number?

Question

In my C# (webform), i have implemented a sql query. The UPC field (varchar type) only contains numbers. My textboxUPC.Text is passed into this UPC field in SQL of my SELECT query.

i.e.

SELECT UPC
WHERE (convert(bigint, UPC) BETWEEN '2529300168' AND '5529300168')

If letters are inputted in the textboxUPC instead of numbers, then this error will occur: enter image description here

Can I just use some if statement maybe in JavaScript that says IF textboxUPC.Text is not number, Then a popup message alerts the user have to enter only numbers?

Simply, Yes you can do. you can use `isNan()` function of JavaScript. — Syed Ali Taqi, Aug 28 '14 at 19:12
How to do it if want to set it up in c# instead of in JavaScript? — user3486647, Aug 28 '14 at 19:20
Use `long.TryParse` in your web form to see if the TextBox has `bigint` (Int64/long) value. Only pass it to your query if `long.TryParse` returns true. — Habib, Aug 28 '14 at 19:22

Syed Ali Taqi · Accepted Answer · 2014-08-28T20:03:48.720

2

HTML:

<input type="text" ID="upc" value=" " />

JS:

  function func() {
  var upc = document.getElementById('upc').value;
  if (isNaN(upc)) {
  alert("Invalid");
  return false;
  }
  else{
  return true;
  }
  }

If you want to check it in code behind C# use Try Parse:

string Str = textBoxUPC.Text;
long Num;
bool isNum = long.TryParse(Str, out Num);
if (isNum)
{
 //your code here...
}
else
{
//display error here...
}

edited Aug 28 '14 at 20:03

answered Aug 28 '14 at 19:22

Syed Ali Taqi

4,898
3
34
44

1

Just a minor suggestion, `BigInt` maps to `long/Int64` so use `long.TryParse` instead. – Habib Aug 28 '14 at 19:50

score 2 · Answer 2 · edited May 23 '17 at 12:27

If you want to write your own Javascript validation, then this post might be useful. Client side validation is great for building a more responsive application, but you should always validate input on the server side also.

ASP.NET webforms provides a quick way to do both with the built in validation controls. Here is a great resource for learning how to use the built in validation controls. You might be able to use a regular expression validator similar to this:

    <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server"
        ControlToValidate="TextBox1" ErrorMessage="Numbers Only" Display="Dynamic"
        ValidationExpression="^\d+$" ValidationGroup="group">
    </asp:RegularExpressionValidator>

score 1 · Answer 3 · answered Aug 28 '14 at 19:24

You should never trust the user input blindly and akways do validation checks before using it anywhere.

Use Integer.TryParse , or Long.TryParse to check whether the text in textbox is a nnumber or not, and convert to numeric type. Show a message if this function returns False, otherwise continue with executing your query.

How to create a simpler proper Exception Handler message for "Error converting data type varchar to bigint" on textbox's value if it's not number?

3 Answers3