Question Linq to Sql query issue using VB 2008 express edition...pls. help

[timmack]

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
________________________________________________________________________

Good day to everyone. I need some help and I hope somebody could help me about linq-to-sql in applying it into the actual database operations on my program. 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 which 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. Hope you got it.

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_Number = ret _
        Select memrec.Movies_Rented()

        For Each memrec In db.Table3s
            If memrec.Account_ = ret Then
                Form4.ComboBox5.Text = memrec.Account_Number
                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
cause 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_Number = ret _
        Select memrec.Movies_Rented()

        For Each memrec In retrn
            If memrec.Account_ = ret Then
                Form4.ComboBox5.Text = memrec.Account_Number
                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_Number' 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'.


Would you please double check and investigate it carefully all the possibilities that could have done to resolve this issue. Your great response is valued and be greatly appreciated. Thank you.

 

[jmcilhinney]

I answered this question at another forum about 7 minutes before you posted here. I win! There I suggested some ways to make your posts more likely to get replies. Here I have the power to make the changes myself.

 

[kittu007]

timmack
Hi timmack,
As i observe from above code the data is retriving from the database .let db may it is object reference to database. So, the data that should be come from it obj reference to database. I thought it may use. Please try this code. I am beginner to learn. If this is correct. I hope this is my firststep. Instead of using this
For Each memrec In retrn replace with
For Each memrec In db.retrn (use this code) in the sample. It may be return the data. So please can u retrieve back. If it is correct .
send me reply Friend.