Searching Between two Values

[xXProjectXx]

Hi all,

If it's possible I need some help about the program I'm creating in vb.net.. I have a DataGridView on my form and it's got a bindingsource to my MSSQL Database where I store all my values, on the form I also Have 2 TextBox because all I want to do is this:

If a person insert for example: 1.500 in the 1st TextBox and 1.700 in the second I want The DataGrid to show me the results between those numbers in the column named "Money".. My code is this (but when I insert the values the DataGrid just goes blank and I tried all type of syntax):

Thank you for the Help, I wish you all the best!


MoneyBindingSource.Filter = String.Format ("Money" >= TextBox1.Text And "Money" <= TextBox2.Text)

 

[jmcilhinney]

I think that you should do a bit more reading on the String.Format method and how it works. The first argument should be the whole text containing place-holders for the variables. The rest of the arguments should be the variables. I could just write this code for you but it's important that you learn how String.Format works so you should do that using the many examples that are already out there and then use that understanding to write this code yourself.

 

[xXProjectXx]

Thank you, yes I don't want the "Ready" code, I also used a code for searhcing all Museums starting with a letter and it did work, then i passed the syntax to that code I posted above in first place and tried to work on it but it keeps getting blank! I'll read more about it then

 

[jmcilhinney]

Here's the logic. The "SQL" code that you want to assign to the Filter property would look something like this:

Money >= MinValue AND Money <= MaxValue

You need to put that "SQL" into a String and a String gets wrapped completely in double quotes:

MoneyBindingSource.Filter = "Money >= MinValue AND Money <= MaxValue"

Now you need to replace MinValue and MaxValue with the actual values, which you can do with String.Format. As I said and as you should have read elsewhere, the first argument is the template String containing place-holders where the subsequent arguments get inserted:

MoneyBindingSource.Filter = String.Format("Money >= {0} AND Money <= {1}", minValue, maxValue)

If you're working with money then those 'minValue' and 'maxValue' variables should be type Decimal and their values should have been validated and converted from your TextBoxes.

 

[xXProjectXx]

Thank You very much,

I was able to build up that syntax after reading the MSDN but now I'm fighting from few hours with the error: "cannot perform >= operation on system.string and system.int32" it's driving me crazy!

 

[xXProjectXx]

Thank You very much,

I was able to build up that syntax after reading the MSDN but now I'm fighting from few hours with the error: "cannot perform >= operation on system.string and system.int32" it's driving me crazy!

I solved it someone changed the value in the column Money.. Thank you very much for your help, you're a very good person!!!!!!

 

[xXProjectXx]

Sorry If i keep bothering you.. I'm about to finish my program I'm getting my last problem with the searching between time, on my MSSQL I have 2 columns one for the enter time in the museum and another for the exit time, I built up this code:

Dim EnterT, ExitT As TimeSpan
Dim Success As Boolean
Success = TimeSpan.TryParse(Enter.Text, EnterT)
Success = TimeSpan.TryParse(Exit.Text, ExitT)
MoneyBindingSource.Filter=String.Format("[Enter Time] = {0:HH:mm:ss} AND [Exit Time] = {1:HH:mm:ss}", EnterT, ExitT)

but "Input string format was no correct" is the error that I keep getting..

 

[jmcilhinney]

That formatting is definitely not going to work regardless but what is the data type of your Enter Time and Exit Time columns?

 

[xXProjectXx]

Goodmorning and thank you for helping me, anyways the type of my columns is: Time. I used timespan because it should handle only time values better than datetime or am I wrong?

 

[jmcilhinney]

As far as I'm aware, you can't represent a TimeSpan directly in a filter expression. You need to use a String and convert it to a TimeSpan internally, e.g.

Dim start = "1:00:00"
Dim end = "10:00:00"

myBindingSource.Filter = String.Format("Time >= CONVERT('{0}', 'System.TimeSpan') AND Time <= CONVERT('{1}', 'System.TimeSpan')", start, end)

I haven't tested that but I believe that those time formats should work.

 

[xXProjectXx]

I tried but it says "String "09:00:00" was not recognized as a valid TimeSpan" obivously when I change time in my textbox the error changes also the time so I guess It recognizes what I write in my TextBoxes but read it as string although I used the timespan.tryparse(Enter.Text, EnterT) and timespan.tryparse(Exit.Text, ExitT)..

 

[jmcilhinney]

I just did a bit of experimentation and found that a rather odd format is used when converting between Strings and TimeSpans in a DataTable. Try this code to see for yourself what it looks like:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim table As New DataTable

    table.Columns.Add("Time", GetType(TimeSpan))
    table.Columns.Add("Text", GetType(String), "CONVERT(Time, 'System.String')")

    For i = 1 To 30
        table.Rows.Add(TimeSpan.FromHours(i))
    Next

    Me.BindingSource1.DataSource = table
    Me.DataGridView1.DataSource = Me.BindingSource1
End Sub

That will show you how you have to format your limits for your filter.

 

[xXProjectXx]

It keeps giving me syntaxes error, I read that timespan its hard to adapt.. I thank that There must be need to find another solution..

 

[jmcilhinney]

It keeps giving me syntaxes error, I read that timespan its hard to adapt.. I thank that There must be need to find another solution..

If it's not working then you're doing it wrong. Did you do as I suggested and view the result of my code from post #12? What did you deduce about the required format? What did you then try? It's an odd format but not hard to reproduce.

 

[xXProjectXx]

I've been away for those days, anyway stil thank you because you always answer to me very fast. I was told to put the column exit time and enter time as Datetime, but is there anyway to separate date and time? I tried Datetime.Now.ToString "HH:mm:ss" but when I get data back in my program it says "column is type timespan" even if I updated to datetime my sql table.