none
Dataset filering for numeric fields RRS feed

  • Question

  • I am struggling with filtering a dataset for a numeric field value:

    The table has a numeric field : REGION ( that is the primary key)

    I am trying :

         MsgBox(mregion.Text)
            Me.RegmastBindingSource.Filter = "convert([region],string)  like '%" & Me.mregion.Text & "%'"
            Me.REG_NAMETextBox.Focus()
       
    But REG_NAMETextBox does not show the value of REG_NAME of the record with REGION = mregion.text.

    I am a bit unclear about the language and syntax to be used for the filter: SQL or VB.

    Request correct me.

    Mohan
      


    MohanSQL

    Friday, August 1, 2014 11:17 AM

Answers

  • Hi!

    No: no error was thrown up when the project was executed.

    I tried changing System.string to System.String: but there was no change: the effect was like the filter was throwing up zero records. I also tried hard-=coding the filter : .filter='REGION = 2', but even that showed zero records.

    There must have been something wrong/corruption in the dataset itself: I deleted the dataset and started a new dataset and new form: which ran as required.

    I wish I could understand what my mistake was!

    Thanks again


    MohanSQL

    Tuesday, August 5, 2014 7:44 AM

All replies

  • >>I am a bit unclear about the language and syntax to be used for the filter: SQL or VB.

    It should be theCLR syntax, while it is a little some different from yours, you could try below syntax

    bindingSource1.Filter = "CONVERT(OrderID,'System.String') like '%2%'";

    If this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, August 4, 2014 2:13 AM
    Moderator
  • Fred,

    Thank you!

    It appears I am making some error.

    The situation is:

    REGMAST is a table in REGMASTDataSet.

    Column REGION in REGMAST is type INT

    It is the primary key.

    mregion is a text box: where I will enter a region number whose details I wish to modify

    I have the following code:

        Private Sub mregion_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mregion.Leave
            Me.RegmastBindingSource.Filter = "CONVERT(REGION,'System.string') like '%" & Me.mregion.Text & "%'"
            Me.REG_NAMETextBox.Focus()
        End Sub
    

    If I enter the number 1 in the textbox mregion, I expect to see NORTH in REG_NAMETextBox when I exit out of mregion.

    This does not happen.

    I am erring somewhere in setting the filter: I checked the table: and the row exists.

    I have successfully implemented a filter for varchar columns, where CONVERT(REGION,'System.string') is replaced by the column name.

    Can you correct me?

    Thanks

    Mohan


    MohanSQL

    Monday, August 4, 2014 10:19 AM
  • I am not sure what the problem was: I found that the bindingsource.filter was not working at all. I finally created a new form: and used the following

        Private Sub TextBox1_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.Leave
            Me.RegmastBindingSource.Filter = "REGION = " & Me.TextBox1.Text
    
        End Sub
    

    This now worked. I do remember trying it earlier and failing: but I am not now sure

    In the first form: the filter could not get any records at all.

    I must have made some error which I cannot replicate: my apologies.

    Mohan


    MohanSQL

    Tuesday, August 5, 2014 6:15 AM
  • Hello,

    The operation “like” is used for string type. The “=” would be ok whether it is integer or string.

    And when you execute the project, does it throw any error message? If not, then I am confused because it would say he 'System.string' is invalid, it should be ‘System.String’ instead. Please try it.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 5, 2014 7:19 AM
    Moderator
  • Hi!

    No: no error was thrown up when the project was executed.

    I tried changing System.string to System.String: but there was no change: the effect was like the filter was throwing up zero records. I also tried hard-=coding the filter : .filter='REGION = 2', but even that showed zero records.

    There must have been something wrong/corruption in the dataset itself: I deleted the dataset and started a new dataset and new form: which ran as required.

    I wish I could understand what my mistake was!

    Thanks again


    MohanSQL

    Tuesday, August 5, 2014 7:44 AM
  • Hello,

    It seems there is something wrong in the original DataSet.

    Anyway, it is glad to hear that you get it working.

    Regards

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 12, 2014 8:19 AM
    Moderator