Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: Using SQL joins in asp.net

    Hi all,

    I'm trying to do bind a simple dataset from a database to a gridview in my application.

    I can manage this fine when it's just a simple one line select statement, but I seem to have trouble when I bring joining tables in to the equation.

    This is the statement I wrote out in SQL Server, and it works fine.
    Code:
    select race.recorded_time, race.date_set, comp.competition_name, swimmer.First_Name, rt.race_distance, rt.stroke  from tbl_racetimes race
    left outer join tbl_competition comp
    on race.competition_id = comp.competition_id
    left outer join tbl_swimmer swimmer
    on race.swimmer_id = swimmer.swimmer_id
    left outer join tbl_racetype rt
    on race.racetype_id = rt.racetype_id
    Order By swimmer.Last_Name
    This is the code I'm using in my application:

    Code:
    Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim dtr As SqlDataReader
        Dim strSql As String
        Dim dad As SqlDataAdapter
        Dim dst As DataSet
        Dim StrConn As String = ConfigurationManager.ConnectionStrings("myconn").ConnectionString
    
    Sub displayTimes()
    
            strSql = ("select race.recorded_time, race.date_set, comp.competition_name, swimmer.First_Name, rt.race_distance, rt.stroke  from tbl_racetimes race" &
            "left outer join tbl_competition comp on race.competition_id = comp.competition_id" &
            "left outer join tbl_swimmer swimmer on race.swimmer_id = swimmer.swimmer_id" &
            "left outer join tbl_racetype rt on race.racetype_id = rt.racetype_id" &
            "Order By swimmer.Last_Name")
    
            conn = New SqlConnection(StrConn)
            cmd = New SqlCommand(strSql, conn)
            conn.Open()
            dtr = cmd.ExecuteReader()
            GridView1.DataSource = dtr
            GridView1.DataBind()
            conn.Close()
        End Sub
    The error that it brings back is attached at the bottom.

    Any help would be appreciated.
    Attached Thumbnails Attached Thumbnails error.png  

  2. #2
    Join Date
    Apr 2012
    Posts
    4
    I've just been playing around with it a bit more, I don't know why I didn't try this before, but the SQL statement seems to work correctly if I just shove it all on one long single line. It doesn't look very tidy, but atleast it works now.

    If anyone could show me how to break it down on to multiple lines in my code properly that would be great.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You need spaces either at the beginning or end of each line. Write strSql out to response (or set a breakpoint and check it out in locals/immediate window) and you'll see what I mean.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Mar 2013
    Posts
    1
    thank you

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •