Linq to Sql query fails with "X is not a member of 'String'"

Database Representation: Table 3

Account Number     |Name      |Address        |Movies Rented   |Date               
00001              |John      |dbfdgjfkhgj    |fhdfhjfgjhk     |11/17/10
00002              |Michael   |gefjkyuthjs    |dgshythety      |10/12/10 
00003              |Maverick  |fgfshbsfgjk    |asdjbinn        |12/01/10 
00003              |Maverick  |gdsffgbhgfhg   |dfdfhfh         |12/02/10 
00004              |Joel      |dsgdsffh       |gdsfhfdh        |11/14/10
00004              |Joel      |hdfgjgfhgfj    |gsdhfdhjty      |11/14/10  
00003              |Maverick  |ertgrjrjtypo   |dsgdgrhtyth     |12/03/10

I encountered something like a logical error which I could hardly resolve since I'm new to this technology. I'm using visual basic 2008 express edition with its built-in linq-to-sql technology. I was able to retrieve, add, edit, and delete such record using the linq-to-sql method, but one thing I could not get is something like relational database operation.

Here is my problem. As you can see the table above wh开发者_运维知识库ich represents the records on my database. You can notice there some account numbers that were duplicated which I used as the primary key or identifier. What I want is when I'd like to query such account number it should display all the records that have the same account numbers on it. Say for example, if I query the account number 00003. It should only pull up and display three records with the name Maverick and their corresponding address, movie rented and date.

What is happening in my end is when I query such account number which are being duplicated in the database I encountered two things, its either it would display all the records in the database with different account numbers associated or it gives me an error message if I change my code from For Each memrec In db.Table3s to For Each memrec In retrn statement. What I've been trying to do here is if you enter the account number on the search box, it should only display the records on the combo box associated with the account number being entered. Like if you enter 00003 on the search box, it should only display the three movies rented by Maverick no more no less on the combo box.

Here are the codes I used:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

    Dim db As New memrecDataContext()

    Dim ret As String
    ret = InputBox("Enter Account#", "Search")

    Dim retrn = _
    From memrec In db.Table3s _
    Where memrec.Account_ = ret _
    Select memrec.Movies_Rented()

    For Each memrec In db.Table3s
        If memrec.Account_ = ret Then
            Form4.ComboBox5.Text = memrec.Account_
            Form4.ComboBox4.Text = memrec.Name
            Form4.DateTimePicker1.Value = memrec.Date
            Form4.ComboBox2.Text = memrec.Movies_Rented
            Form4.ComboBox1.Text = memrec.Address     
            Form4.Show()

        End If
    Next

End Sub

that code above won't filter the records associated with the account number being entered on the searchbox because it would still pull up all the records in the database and if I also use this block of codes below:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

    Dim db As New memrecDataContext()

    Dim ret As String
    ret = InputBox("Enter Account#", "Search")


    Dim retrn = _
    From memrec In db.Table3s _
    Where memrec.Account_ = ret _
    Select memrec.Movies_Rented()

    For Each memrec In retrn
        If memrec.Account_ = ret Then
            Form4.ComboBox5.Text = memrec.Account_
            Form4.ComboBox4.Text = memrec.Name
            Form4.DateTimePicker1.Value = memrec.Date
            Form4.ComboBox2.Text = memrec.Movies_Rented
            Form4.ComboBox1.Text = memrec.Address
            Form4.Show()

        End If
    Next

End Sub

it will still give me an error saying the following

'Account_' is not a member of 'String'.
'Name' is not a member of 'String'.
'Address' is not a member of 'String'.
'Movies_Rented' is not a member of 'String'.
'Date' is not a member of 'String'.

Thanks for your response. I have already tried that set of codes before but it's working the same thing. I tried many times modifying my codes before I posted my comment in this forum thinking that i can resolve it on my own but I wasn't able to. That code won't still filter the records in the database. I would like to verify that the table above is only representation but the real column on the table on my database is 'Account#' not Account Number and visual basic would detect the code as 'Account_'. May I know why Movies_Rented column on my table is not a member of string? Why is it the code you gave would still display all the records on the combo box?

In your example you are selecting memrec.Movies_Rented, which is going to return just the column Movies_Rented, which would be just an array of strings, hence the "such and such is not a member of String" errors. You want to just select memrec.

Dim retrn = _
    From memrec In db.Table3s _
    Where memrec.Account_ = ret _
    Select memrec

As far as it not filtering correctly, it looks like you are trying to compare using Account_, which isn't part of your table. You may need to filter on Account_Number. (This all depends on how the LINQ mapping is going on, that part of your code could be just fine).

The filtering looks fine to me. It could be because you are typing in "3" into the text box and not "000003". (I'm assuming that your account numbers are stored as strings in the database.)

Also, I see that you are setting the text of the combo boxes. This may or may not be intended, but I suspect that you want to add an item to their list of options.

Once you fix the selecting problem, it should be easier to see if you are getting the right results (and if not, how to fix them).