Number input translation to SQL

[Fredrik Melin]

I want to allow my users to enter filter's on a SQL sentence for a numeric
field.

I would like to allow them to write for sample "0-10, 101" which should give
all from 0-10 and 101, in other words allow - for range and , to separate
several numbers.

Is there a built in function to decode this into a number sequence that I
can use in a IN SQL statement?
Or is that something I need to write my self?

The output I would like is from the sample above 0,1,2,3,4,5,6,7,8,9,10,101

- Fredrik

 

[Cor Ligthert [MVP]]

Frederik,

This is a newsgroup about dotnet VB language problems, not about problems by
using VBNet.

In my opinion is for your question a SQL language newsgroup the better
choice.

Just my opinion.

Cor

 

[Fredrik Melin]

Im talking about VB.NET...

form textbox input, enter 0-10,101 into a textbox...

Question is how to translate that into SQL using VB.net, if there is
something already made for it maybe..

 

[Herfried K. Wagner [MVP]]

I want to allow my users to enter filter's on a SQL sentence for a numeric
field.

I suggest to post the question to one of the groups in the
"microsoft.public.sqlserver.*" groups hierarchy.

 

[Fredrik Melin]

Im talking about the input into a VB.NET form...

I suggest to post the question to one of the groups in the
"microsoft.public.sqlserver.*" groups hierarchy.

 

[Armin Zingler]

Frederik,

This is a newsgroup about dotnet VB language problems, not about
problems by using VBNet.

Luckily *I* did not write this because it might be misunderstood. Of
course we handle problems about using VB.Net.

In my opinion is for your question a SQL language newsgroup the
better choice.

Just my opinion.

Sorry, not mine. It's a "how-to" that is probably best placed in a "general
programming" (language unrelated) group but "how-to"s have also always been
handled here.


Armin

 

[Armin Zingler]

I want to allow my users to enter filter's on a SQL sentence for a
numeric field.

I would like to allow them to write for sample "0-10, 101" which
should give all from 0-10 and 101, in other words allow - for range
and , to separate several numbers.

Is there a built in function to decode this into a number sequence
that I can use in a IN SQL statement?
Or is that something I need to write my self?

The output I would like is from the sample above
0,1,2,3,4,5,6,7,8,9,10,101

No, there is no such function. You have to parse the string on your own.
Maybe regular expressions can handle this but I don't know regexp to be able
to give a specific suggestion. Once I wrote a "ValueAreas" class, that's a
collection of ValueArea objects. Each has a from and to property. You can
add a parse function to analyse the string. Afterwards, you can build an SQL
string from the result. I recommend the "field between .. and ..." syntax
instead of building a comma seperated list - could get pretty long.


Armin

 

[Cor Ligthert [MVP]]

Frederik,

There is nothing build in, however you can use parameters.

How to make logical that string is your problem.

It can be by using Regex or using standard VB.Net with methods as SubString
and Replace.

To see how to use parameters you can look at this page if it is simple
OleDB. Showed are datetimes because that is almost the most complicated to
instert in a SQL string. If you want the sample with SQL server than is for
that an even simpler sample as well on the site.

http://www.windowsformsdatagridhelp.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

I hope this helps,

Cor
..

 

[Cor Ligthert [MVP]]

 

[Armin Zingler]

You're feeling fine?


Armin