Can't select multiple records in SQL with ASP.net

Question

I have problem and I've been looking for a week now on how to solve it. Yesterday I thought I solved but apparently I didn't when I tried the query with new data added in my database.

Here's my query

Public Function getTicketsByBeginstation(ByVal station As String) As Array

    Dim r As Array

    r = (From u In treinDataContext.Tickets Where u.ritId =
         (From v In treinDataContext.Rittens Where v.trajectId =
          (From s In treinDataContext.Trajectens Where s.beginstationId =
           ((From t In treinDataContext.Stations Where (t.naam = station) Select t.id).First) Select s.id).First Select v.id).First Select u.datumAankoop, u.betaalmethodeId, u.tijdAankoop, u.klasseId, u.ritId Order By datumAankoop Ascending).ToArray

    Return r

End Function

The problem I have is: when I have a station selected there are more than 1 trajecten (connections in English) but I had to use the .first else Visual Studio would give the error "the '=' operator is not defined for 'integers' and 'system.linq.IQueryable(of integer)".

So I give the name of a station -> I have to select all trajectens with the given station as beginstation -> I have to select all ritten which have the given trajecten -> I have to select all tickets which have the given ritten

So: I have to show all tickets for a certain station.

Can anyone help me?

You should definitely remove "sql" and substitute "linq" in your tags. Q: Have you considered re-writing your query in SQL (instead of LINQ)? It might actually be easier... — paulsm4
– paulsm4, Commented Nov 18, 2011 at 22:29

Conrad Frix · Accepted Answer · 2011-11-18 22:37:43Z

1

Could be that you're looking for join instead

r = (From u In treinDataContext.Tickets 
       Join v In treinDataContext.Rittens 
       On u.ritId = v.id
       Join s In treinDataContext.Trajectens 
       On v.trajectId equals v.id
       JOIN t In treinDataContex 
       On s.beginstationId equals t.id 
       Where (t.naam = station)
       Select u.datumAankoop, 
               u.betaalmethodeId, 
               u.tijdAankoop,
               u.klasseId, 
               u.ritId 
       Order By  datumAankoop Ascending ).ToArray()

For more on joins see MSDN Article How to: Combine Data with LINQ by Using Joins (Visual Basic)

answered Nov 18, 2011 at 22:37

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ann L. · Accepted Answer · 2011-11-18 22:52:20Z

0

This is from memory and is not guaranteed to be syntactically correct. And it's using the extension method style of LINQ. But something like this might work:

Dim a = treinDataContext.Stations.Where(Function(t) t.naam = station) _
       .Join(treinDataContext.Trajectens, Function(s) t.id = s.beginstationId) _
       .Join(treinDataContext.Rittens, Function(v) v.trajectId = s.Id) _
       .[Select](Function(v) v.Id)
Dim b = treinDataContext.Tickets.Where(Function(u) a.Any(a.Id = u.ritId))

My original code was in C#; I used an automatic translator to turn it into VB. My apologies for any syntax errors.

edited Nov 18, 2011 at 22:52

answered Nov 18, 2011 at 22:33

Ann L.

14k5 gold badges39 silver badges67 bronze badges

4 Comments

Ann L. Over a year ago

@user1054677: Nope: I wrote it before I saw your VB.Net tag. But if you like, I'll translate it into VB.

Confituur Over a year ago

that would be awesome :) tx for the help btw

Ann L. Over a year ago

@user1054677 OK, I changed the answer to be written in VB. I used the extention method style because that's what I'm familiar with. As I said above, this was from memory, so I can't guarantee the syntax is correct. But it ought to be fairly easy for you to fix any errors I made.

Confituur Over a year ago

it's okay, I get the concept of using JOINS now, thank you so much for ur help!

Collectives™ on Stack Overflow

Can't select multiple records in SQL with ASP.net

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related