Solved

3 join query on a report viewer in vb

Posted on 2014-10-07
13
117 Views
Last Modified: 2014-10-27
I have finally narrowed it down first table Team just need to match TeamID  with second table called NFLPLAYERS TeamID HAVE NFLPLAYERS JOIN TABLE CALLED DRAFT join on PlayerID. ON REPORT VIEWER HOPEFULLY LOCALLY.
Public spcode As String
    Public roundnumbers As Integer
    Public connetionString As String
    Dim connection As SqlConnection
    Dim adapter As SqlDataAdapter
    Public Sub GetNBADraft()
        Dim sql As String
        Dim ds As New DataSet
        Dim SelectCommand As New SqlCommand
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=xxxxxxx"
        connection = New SqlConnection(connetionString)


        sql = "SELECT np.PlayerID, np.FirstName, np.LastName, np.Position,np.TeamID, Draft.draftyear, Draft.round,Team.Fullname"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID, Draft.Sportcode"
        sql += " FROM NBAPlayers np Join Draft ON np.PlayerID = Draft.PlayerID AND Join Draft ON Team.ID = Draft.TeamID WHERE"
        sql += " Draft.round = @round AND Draft.draftyear = @draftyear AND Draft.sportcode = @sportcode ORDER BY Draft.draftyear DESC"

        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@round", roundnumbers)
            adapter.SelectCommand.Parameters.AddWithValue("@draftyear", tscmbyear.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@sportcode", spcode)
            adapter.Fill(ds)
            connection.Close()
            ReportViewer1.DataBindings = ds.Tables(0)
        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

       
    End Sub

    Public Sub spcode1()
        If tscmbsport.Text = "NFL" Then
            spcode = "AB"
            ' Exit Sub
        End If

        If tscmbsport.Text = "NBA" Then
            spcode = "AC"
            '  Exit Sub
        End If

        If tscmbsport.Text = "NHL" Then
            spcode = "AD"
            '   Exit Sub
        End If
    End Sub

    Public Sub roundtonumbers()
        If tscmbRound.Text = "None" Then
            roundnumbers = "0"
            Exit Sub
        End If

        If tscmbRound.Text = "One" Then
            roundnumbers = "1"
            Exit Sub
        End If

        If tscmbRound.Text = "Two" Then
            roundnumbers = "2"
            Exit Sub
        End If

        If tscmbRound.Text = "Three" Then
            roundnumbers = "3"
            Exit Sub
        End If

        If tscmbRound.Text = "Four" Then
            roundnumbers = "4"
            Exit Sub
        End If

        If tscmbRound.Text = "Five" Then
            roundnumbers = "5"
            Exit Sub
        End If

        If tscmbRound.Text = "Six" Then
            roundnumbers = "6"
            Exit Sub
        End If

        If tscmbRound.Text = "Seven" Then
            roundnumbers = "7"
            Exit Sub
        End If

        If tscmbRound.Text = "Eight" Then
            roundnumbers = "8"
            Exit Sub
        End If

        If tscmbRound.Text = "Nine" Then
            roundnumbers = "9"
            Exit Sub
        End If
    End Sub
    Private Sub DraftReport_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Me.ReportViewer1.RefreshReport()
    End Sub

Open in new window


APP
APP VARIABLE 1
APP
APP VARIABLE 2
APP
APPVARABLE3
Can I do that Join team fullname on Reportviewer Please jump start me and report viewer
0
Comment
Question by:powerztom
  • 11
  • 2
13 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40367303
        sql = "SELECT np.PlayerID, np.FirstName, np.LastName, np.Position,np.TeamID, Draft.draftyear, Draft.round,Team.Fullname"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID, Draft.Sportcode"
        sql += " FROM NBAPlayers np Join Draft ON np.PlayerID = Draft.PlayerID AND Join Draft ON Team.ID = Draft.TeamID WHERE"
        sql += " Draft.round = @round AND Draft.draftyear = @draftyear AND Draft.sportcode = @sportcode ORDER BY Draft.draftyear DESC"

Open in new window

Line 1 has a missing comma at the end (or lne 2 has a leading comma missing)
Line 3, don't use "AND JOIN" the and is not needed

This is what the existing query should look like when formatted
SELECT
      np.PlayerID
    , np.FirstName
    , np.LastName
    , np.Position
    , np.TeamID
    , Draft.draftyear
    , Draft.[round]
    , Team.Fullname
    , Draft.Draftpos
    , Draft.OverAllPos
    , Draft.DraftteamID
    , Draft.OrigTeamID
    , Draft.Sportcode
FROM NBAPlayers np
      JOIN Draft
                  ON np.PlayerID = Draft.PlayerID
      JOIN Draft
                  ON Team.ID = Draft.TeamID
WHERE Draft.round = @round
      AND Draft.draftyear = @draftyear
      AND Draft.sportcode = @sportcode
ORDER BY
      Draft.draftyear DESC

Open in new window

I would prefer to see you use INNER JOIN, but JOIN is allowed.
-------------------

Now, what is the question?
What information is available about this other table ?
0
 

Author Comment

by:powerztom
ID: 40368335
That looks good well the question is Report viewer control I create an empty dataset and then set your query  to the dataset on  report viewer. I tried local report property on reportviewer control but not sure about it cause I have yet to have success with displaying any data
0
 

Author Comment

by:powerztom
ID: 40368414
Got this error in SQL EXPRESS The objects "Draft" and "Draft" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
SELECT
      np.PlayerID
    , np.FirstName
    , np.LastName
    , np.Position
    , np.TeamID
    , Draft.draftyear
    , Draft.round
    , Team.Fullname
    , Draft.Draftpos
    , Draft.OverAllPos
    , Draft.DraftteamID
    , Draft.OrigTeamID
    , Draft.Sportcode
FROM NBAPlayers np
      JOIN Draft
                  ON np.PlayerID = Draft.PlayerID
      JOIN Draft
                  ON Team.TeamID = Draft.TeamID
WHERE Draft.round = '1'
      AND Draft.draftyear = '2013'
      AND Draft.sportcode = 'AB'
ORDER BY
      Draft.draftyear DESC

Open in new window


wHAT DOES THIS MEAN Use correlation names to distinguish them.
0
 

Author Comment

by:powerztom
ID: 40368733
PortletPaul I could use your help with query issue and hopefully you know Reportviewer control.; i'm gonna create a dataset and see if I can get it up and humming
0
 

Author Comment

by:powerztom
ID: 40368764
When I run query in vb project I get An unhandled exception of type 'System.NullReferenceException' occurred in Draft.exe
0
 

Author Comment

by:powerztom
ID: 40369092
sql error message
hERE IS THE FUNCTION
 Public Sub GetNflDraft()
        Dim sql As String
        Dim ds As New DataSet
        Dim SelectCommand As New SqlCommand
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)


        ' sql = "SELECT np.PlayerID, np.FirstName, np.LastName, np.Position, np.TeamID, Team.Fullname Draft.draftyear, Draft.round,"
        ' sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID, Draft.Sportcode"
        ' sql += " FROM NflPlayers np Join Draft ON np.PlayerID = Draft.PlayerID WHERE"
        ' sql += " Draft.round = @round AND Draft.draftyear = @draftyear AND Draft.sportcode = @sportcode ORDER BY Draft.draftyear DESC"
        sql = "SELECT np.PlayerID, np.FirstName, np.LastName, np.Position,np.TeamID, Draft.draftyear, Draft.round,Team.Fullname"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID, Draft.Sportcode"
        sql += " FROM NFLPlayers np InnerJoin(Draft) ON np.PlayerID = Draft.PlayerID InnerJoin(Draft As d) ON Team.TeamID = d.TeamID WHERE"
        sql += " Draft.round = @round AND d.draftyear = @draftyear AND Draft.sportcode = @sportcode ORDER BY d.draftyear DESC"
        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@round", roundnumbers)
            adapter.SelectCommand.Parameters.AddWithValue("@draftyear", tscmbyear.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@sportcode", spcode)
            adapter.Fill(ds)
            connection.Close()
            dvgDraft.DataSource = ds.Tables(0)
        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

        Me.dvgDraft.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgDraft.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgDraft.ColumnHeadersHeight = 55
        dvgDraft.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.dvgDraft.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgDraft.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgDraft.ColumnHeadersHeight = 55
        dvgDraft.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        dvgDraft.Columns("Lastname").Frozen = True
    End Sub

Open in new window

SAYING M NULL ON THIS LINE  dvgDraft.Columns("Lastname").Frozen = True
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:powerztom
ID: 40369422
PortletPaul throw me a lifepreserver I 'm trying made a tableadapter for the reportviewer

does it work like the gentleman says on his website can I get a Hell no something is off on the 3 join query I changed a few things image  sql error

an reportviewer says
 With ReportViewer1
            .LocalReport.ReportPath = "nfl.rdlc"
            Dim lpar As New ReportParameter("@round", roundnumbers)
            Dim lpar1(0) As ReportParameter
            lpar1(1) = lpar
            .LocalReport.SetParameters(lpar1)
            .LocalReport.Refresh()
        End With Any help would be appreciated.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40369514
I cannot believe I missed that.
sql += " FROM NBAPlayers np Join Draft ON np.PlayerID = Draft.PlayerID AND Join Draft ON Team.ID = Draft.TeamID WHERE"

Open in new window


That line references DRAFT twice! (see bold below) and the Team table isn't joined but is referenced (see italics)

FROM NBAPlayers np
      JOIN Draft
                  ON np.PlayerID = Draft.PlayerID
      JOIN Draft
                  ON Team.TeamID = Draft.TeamID
WHERE


Please try this instead
SELECT
      np.PlayerID
    , np.FirstName
    , np.LastName
    , np.Position
    , np.TeamID
    , Draft.draftyear
    , Draft.[round]
    , Team.Fullname
    , Draft.Draftpos
    , Draft.OverAllPos
    , Draft.DraftteamID
    , Draft.OrigTeamID
    , Draft.Sportcode
FROM Draft
      JOIN NBAPlayers np
                  ON np.PlayerID = Draft.PlayerID
      JOIN Team
                  ON Team.ID = Draft.TeamID
WHERE Draft.round = @round
      AND Draft.draftyear = @draftyear
      AND Draft.sportcode = @sportcode
ORDER BY
      Draft.draftyear DESC

Open in new window

0
 

Author Comment

by:powerztom
ID: 40370617
I'm having a hard time pinpointing where the error is coming from in SQL Express I get
this error message when i hardcode some values to see it work. Maybe it's something simple that I overlooked
sql error
when I run it as a vb app I get this

vb sql error
When I clickon the OK Button for the above sql error message I get this
Vbnull
I just wanted you to see if any of this you can Identify where the problem is

here is the sub I'm using maybe something in there Thanks for your help on this by the way
 Public Sub GetNflDraft()
        Dim sql As String
        Dim ds As New DataSet
        Dim SelectCommand As New SqlCommand
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)


      
        sql = "SELECT np.PlayerID, np.FirstName, np.LastName, np.Position,np.TeamID, Draft.draftyear, Draft.round,Team.Fullname"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID, Draft.Sportcode"
        sql += " FROM NFLPlayers np Join Draft ON np.PlayerID = Draft.PlayerID Join Team ON Team.TeamID = Draft.TeamID WHERE"
        sql += " Draft.round = @round AND Draft.draftyear = @draftyear AND Draft.sportcode = @sportcode ORDER BY Draft.draftyear DESC"
        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@round", roundnumbers)
            adapter.SelectCommand.Parameters.AddWithValue("@draftyear", tscmbyear.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@sportcode", spcode)
            adapter.Fill(ds)
            connection.Close()
            dvgDraft.DataSource = ds.Tables(0)
        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

        Me.dvgDraft.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgDraft.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgDraft.ColumnHeadersHeight = 55
        dvgDraft.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.dvgDraft.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgDraft.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgDraft.ColumnHeadersHeight = 55
        dvgDraft.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        dvgDraft.Columns("Lastname").Frozen = True
    End Sub

Open in new window


Thanks Bro
0
 

Author Comment

by:powerztom
ID: 40370893
Not to make this any longer I noticed a missing comma.Now I get I think there are null values that may be causing all this grief. Is there anyway to bulletproof the sql query
0
 

Author Comment

by:powerztom
ID: 40371152
OK I royally fucked up the table is teams not team it's Draft.TeamID THAT MATCHED Team.TEAMID IT was Draft.DraftTeamID iT ALL WORKS I AM SORRY FROM SCREEN SHOT AFTER SCREENSHOT i OWE YOU A SHOT OR TWO.
0
 

Author Closing Comment

by:powerztom
ID: 40371166
PortletPaul HELPED ME AFTER I was screwing up on my side giving him the wrong info but he politely answered my questions I finally found it out the  PortletPaul was correct and I never doubted his skill I knew something on my side was wrong cause of all the screen shots. Well God bless this dude. There is alot of knowledge these EE GUYS HAVE TO OFFER.
0
 

Author Comment

by:powerztom
ID: 40406773
PortletPaul,

Could you if you get a chance chec on http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28544250.html It's the same thing you did here and you post some code or sql but I'm getting annoying errors I tried narrowing down to just using one textbox and still getting error. I'm gona be hacking a way at it but if you could look at my set parameters maybe you'll see something that I don't .Thanks Man
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now