none
LINQ Filtering issue RRS feed

  • Question

  • Hello,

    I am having trouble filtering a linq table based text string.  When I leave out the where clause it all works fine, but when I put it in I get an empty table or data set.  I have a label on the form wich im am using as a test to see weather the string is coming over to the form, which it is.

    Regards,
    Phil

    Imports

     

    System.Data.Linq

    Imports

     

    System.Windows.Forms

    Imports

     

    DataAccess

    Public

     

    Class frmContacts

     

    Private db As New ClientDataContext

     

    Sub New()

    InitializeComponent()

     

    Dim conQuery = From c In db.Contacts Select c Where c.ClientCode.Equals(Text)

     

    Me.ContactsBindingSource.DataSource = conQuery

     

    End Sub

     

    Private Sub frmContacts_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

     

    End Sub

     

    Public Sub InsertText(ByVal Text As String)

    Label1.Text = Text

     

    End Sub

     

    Private Sub ContactsBindingSource_AddingNew(ByVal sender As System.Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles ContactsBindingSource.AddingNew

    e.NewObject =

    New Contact

     

    End Sub

     

    Private Sub butSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles butSave.Click

     

    Try

     

    Me.Validate()

    db.SubmitChanges()

    MessageBox.Show(

    "Records Updated Successfully", "", MessageBoxButtons.OK, MessageBoxIcon.Information)

     

    Catch

    MessageBox.Show(

    "Records Not Updated", "", MessageBoxButtons.OK, MessageBoxIcon.Error)

     

    End Try

     

    End Sub

    End

     

    Class


    Tuesday, September 8, 2009 12:18 AM

Answers

  • Hi LuckyPhil78,

    I simulate your situation you presented. However, the Linq statement, with the where clause, works fine. Here's part of my test code based on the Northwind Sample database. It retrieves a string from the TextBox1 control and uses the string in the LINQ where clause. The test code can correctly find records whose 'city' field matches the Text string of the TextBox1 control:

     

    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            Dim Text As String = TextBox1.Text
    
            ' Use a connection string.
            Dim db As New DataContext _
                ("......")
    
            ' Get a typed table to run queries.
            Dim Customers As Table(Of Customer) = _
                db.GetTable(Of Customer)()
    
            ' Query for customers in London.
            Dim custQuery = _
                From cust In Customers _
                Select cust _
                Where cust.City.Equals(Text)
    
            '    Where cust.City = "London" _
            '   Select cust
    
            ' Format the message box.
            Dim msg As String = "", title As String = "customers:", _
                response As MsgBoxResult, style As MsgBoxStyle = _
                MsgBoxStyle.Information
    
            ' Execute the query.
            For Each custObj In custQuery
                msg &= String.Format(custObj.CustomerID & vbCrLf)
            Next
    
            ' Display the results.
            response = MsgBox(msg, style, title)
    
    
            '   MessageBox.Show(Text)
        End Sub
    End Class

    So the problem is not caused by the Where clause in the Linq statement. In order to solve problem, you have following choices:

    1. Check you database if records which match the 'Text' do exist.
    2. Replace the 'Text' variable with a string constant. Test whether your code work well under such condition.
    3. Set breakpoints to trace the value of the 'Text' variable. Make sure it's not null or any value unexpected.

    If your code still doesn't work, please provide more key details about your program. For example, how's the window form composed of, which controls you have used or how you set the value of the 'Text' variable.


    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Zhipeng Lee Monday, September 14, 2009 7:12 AM
    Wednesday, September 9, 2009 9:55 AM

All replies

  • Please check, possibly you try to find contacts with the code that is written in the form's caption.
    Please note that value of Text property is taken at the moment when conQuery exetuting at

    Me.ContactsBindingSource.DataSource = conQuery 


    Possibly Text property has a wrong value at this moment

     

     


    Pavel Kolesnikov, Devart Team
    http://www.devart.com/dotconnect
    ADO.NET data providers for Oracle, MySQL, PostgreSQL, SQLite with Entity Framework and LINQ to SQL support
    Wednesday, September 9, 2009 8:47 AM
  • Hi LuckyPhil78,

    I simulate your situation you presented. However, the Linq statement, with the where clause, works fine. Here's part of my test code based on the Northwind Sample database. It retrieves a string from the TextBox1 control and uses the string in the LINQ where clause. The test code can correctly find records whose 'city' field matches the Text string of the TextBox1 control:

     

    Public Class Form1
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
            Dim Text As String = TextBox1.Text
    
            ' Use a connection string.
            Dim db As New DataContext _
                ("......")
    
            ' Get a typed table to run queries.
            Dim Customers As Table(Of Customer) = _
                db.GetTable(Of Customer)()
    
            ' Query for customers in London.
            Dim custQuery = _
                From cust In Customers _
                Select cust _
                Where cust.City.Equals(Text)
    
            '    Where cust.City = "London" _
            '   Select cust
    
            ' Format the message box.
            Dim msg As String = "", title As String = "customers:", _
                response As MsgBoxResult, style As MsgBoxStyle = _
                MsgBoxStyle.Information
    
            ' Execute the query.
            For Each custObj In custQuery
                msg &= String.Format(custObj.CustomerID & vbCrLf)
            Next
    
            ' Display the results.
            response = MsgBox(msg, style, title)
    
    
            '   MessageBox.Show(Text)
        End Sub
    End Class

    So the problem is not caused by the Where clause in the Linq statement. In order to solve problem, you have following choices:

    1. Check you database if records which match the 'Text' do exist.
    2. Replace the 'Text' variable with a string constant. Test whether your code work well under such condition.
    3. Set breakpoints to trace the value of the 'Text' variable. Make sure it's not null or any value unexpected.

    If your code still doesn't work, please provide more key details about your program. For example, how's the window form composed of, which controls you have used or how you set the value of the 'Text' variable.


    Best regards,
    Charlie Lee

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Zhipeng Lee Monday, September 14, 2009 7:12 AM
    Wednesday, September 9, 2009 9:55 AM