asked on

sqlqueries .net vb.net HELP

I'VE post quite a few sql queries lately and every EE GUY WAS helpful. i HAVE A PROJECT that is massive VB6 300 FORMS AND 100 Classes and 7 Global ModulesI just got a project due Wednesday I'm a scatter brain cause I jump from nfl to the Draftwell I use 3 comboxes to allow user got back and `customize the query like round number what sport and year. So haven't made hardly any progress in 3 days.
here is myy getdata for a NFL dRAFT

 Public Sub GetNflRound()
        Dim sql As String
        Dim ds As New DataSet
        ' Dim mySQLDATAAdapter As New SqlDataAdapter
        Dim SelectCommand As New SqlCommand
        ' Dim da As New SqlDataAdapter
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)

        ' sql += "Select PK,PlayerID, draftyear,round,DraftPos, OverallPos, DraftTeamID, OrigTeamID, sportcode FROM Draft "
        ' sql += " WHERE  &  sportcode= CStr(spcode) &  And round=CInt(roundnumbers) &  And  draftyear= & CStr(tscmbyear.Text) "
        sql = "SELECT np.PlayERID, np.FirstName, np.LastName, np.PlayerID, np.TeamID, 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 Join(Draft)"
        sql += "ON np.PlayerID = Draft.PlayerID  WHERE Draft.round AND Draft.draftyear AND Draft.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", draftyear)
            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

eRROR MESSAGE

WHAT DRAFT LOOKS LIKE

it_saige

You are missing spaces in your query:

Instead of:

        sql = "SELECT np.PlayERID, np.FirstName, np.LastName, np.PlayerID, np.TeamID, 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 Join(Draft)"
        sql += "ON np.PlayerID = Draft.PlayerID  WHERE Draft.round AND Draft.draftyear AND Draft.sportcode ORDER BY  Draft.draftyear DESC"

Open in new window

Use:

        sql = "SELECT np.PlayERID, np.FirstName, np.LastName, np.PlayerID, np.TeamID, 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 Join(Draft)"
        sql += " ON np.PlayerID = Draft.PlayerID  WHERE Draft.round AND Draft.draftyear AND Draft.sportcode ORDER BY  Draft.draftyear DESC"

Open in new window

This code has it fixed:

 Public Sub GetNflRound()
        Dim sql As String
        Dim ds As New DataSet
        ' Dim mySQLDATAAdapter As New SqlDataAdapter
        Dim SelectCommand As New SqlCommand
        ' Dim da As New SqlDataAdapter
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)

        ' sql += "Select PK,PlayerID, draftyear,round,DraftPos, OverallPos, DraftTeamID, OrigTeamID, sportcode FROM Draft "
        ' sql += " WHERE  &  sportcode= CStr(spcode) &  And round=CInt(roundnumbers) &  And  draftyear= & CStr(tscmbyear.Text) "
        sql = "SELECT np.PlayERID, np.FirstName, np.LastName, np.PlayerID, np.TeamID, 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 Join(Draft)"
        sql += " ON np.PlayerID = Draft.PlayerID  WHERE Draft.round AND Draft.draftyear AND Draft.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", draftyear)
            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

Copy the above and try it again.

-saige-

Tom Powers

ASKER

saige thanks for effort I posted entire source code I tok the brackets from around draft cause Im got a)syntax error AnyHelp would be sos so apprecited getting super stressed.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class Form1
    Public Sportcode As String
    Dim TableName As String
    Public roundnumbers As Integer
    Public connetionString As String
    Dim connection As SqlConnection
    Dim adapter As SqlDataAdapter
    Dim PlayerID2 As Integer
    Dim spcode As String
    Dim draftyear As String
  
    Public Sub GetNflRound()
        Dim sql As String
        Dim ds As New DataSet
        ' Dim mySQLDATAAdapter As New SqlDataAdapter
        Dim SelectCommand As New SqlCommand
        ' Dim da As New SqlDataAdapter
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)

        ' sql += "Select PK,PlayerID, draftyear,round,DraftPos, OverallPos, DraftTeamID, OrigTeamID, sportcode FROM Draft "
        ' sql += " WHERE  &  sportcode= CStr(spcode) &  And round=CInt(roundnumbers) &  And  draftyear= & CStr(tscmbyear.Text) "
        sql = "SELECT np.PlayERID, np.FirstName, np.LastName, np.PlayerID, np.TeamID, 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 Join Draft"
        sql += " ON np.PlayerID = Draft.PlayerID  WHERE Draft.round AND Draft.draftyear AND Draft.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", draftyear)
            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






    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        Application.Exit()
    End Sub

    Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
        Application.Exit()
    End Sub

    Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
        If WebBrowser1.Visible = True Then
            WebBrowser1.Visible = False
            Exit Sub
        End If
        If WebBrowser1.Visible = False Then
            WebBrowser1.Visible = True
            Exit Sub
        End If



    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 Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        WebBrowser1.Navigate("HTTP://SPORTSNETWORK.COM/TEAMS.ASP")
        '2 GetNflRound()
    End Sub
    Public Sub Comboxquery()
        If tscmbRound.Text = "" Or tscmbsport.Text = "" Or tscmbyear.Text = "" Then
            MessageBox.Show("Select all three categories Stupid")
            Exit Sub
        End If
        If tscmbRound.SelectedText <> "" Or tscmbsport.SelectedText <> "" Or tscmbyear.SelectedText <> "" Then

            spcode1()
            roundtonumbers()
            GetNflRound()

        End If
    End Sub
    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        Comboxquery()

    End Sub

    Dim time As DateTime = DateTime.Now

    Dim s As String = time.Year.ToString("yyyy")

    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        Dim time As DateTime = DateTime.Now

        Dim s As String = time.Year.ToString()
        TextBox1.Text = s
    End Sub
End Class

Open in new window

latest errormessage

it_saige

This error is basically stating that:

Draft.round AND Draft.draftyear AND Draft.sportcode

Open in new window

Are not boolean (true/false) values .

You need to compare Draft.round, Draft.draftyear and Draft.sportcode to something.

i.e., making up entries -

 sql = "SELECT np.PlayERID, np.FirstName, np.LastName, np.PlayerID, np.TeamID, 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 Join Draft"
        sql += " ON np.PlayerID = Draft.PlayerID  WHERE Draft.round = 7 AND Draft.draftyear = 2013 AND Draft.sportcode = 12 ORDER BY  Draft.draftyear DESC"

Open in new window

-saige-

it_saige

I reviewed your code a little more. Here is ultimately what I think you might be after:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms
Public Class Form1
	Public Sportcode As String
	Dim TableName As String
	Public roundnumbers As Integer
	Public connetionString As String
	Dim connection As SqlConnection
	Dim adapter As SqlDataAdapter
	Dim PlayerID2 As Integer
	Dim spcode As String
	Dim draftyear As String

	Public Sub GetNflRound()
		Dim sql As String
		Dim ds As New DataSet
		' Dim mySQLDATAAdapter As New SqlDataAdapter
		Dim SelectCommand As New SqlCommand
		' Dim da As New SqlDataAdapter
		connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
		connection = New SqlConnection(connetionString)

		' sql += "Select PK,PlayerID, draftyear,round,DraftPos, OverallPos, DraftTeamID, OrigTeamID, sportcode FROM Draft "
		' sql += " WHERE  &  sportcode= CStr(spcode) &  And round=CInt(roundnumbers) &  And  draftyear= & CStr(tscmbyear.Text) "
		sql = "SELECT np.PlayERID, np.FirstName, np.LastName, np.PlayerID, np.TeamID, 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 Join Draft"
		sql += " ON np.PlayerID = Draft.PlayerID  WHERE"
		sql += " Draft.round = " & roundtonumbers() & " AND"
		sql += " Draft.draftyear = " & tscmbYear.Text & " AND"
		sql += " Draft.sportcode = " & spcode1() & " ORDER BY Draft.draftyear DESC"

		Try
			connection.Open()
			adapter = New SqlDataAdapter(sql, connection)
			adapter.SelectCommand.Parameters.AddWithValue("@round", roundnumbers)
			adapter.SelectCommand.Parameters.AddWithValue("@draftyear", draftyear)
			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

	Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
		Application.Exit()
	End Sub

	Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
		Application.Exit()
	End Sub

	Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
		If WebBrowser1.Visible = True Then
			WebBrowser1.Visible = False
			Exit Sub
		End If

		If WebBrowser1.Visible = False Then
			WebBrowser1.Visible = True
			Exit Sub
		End If
	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 Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
		WebBrowser1.Navigate("HTTP://SPORTSNETWORK.COM/TEAMS.ASP")
		'2 GetNflRound()
	End Sub

	Public Sub Comboxquery()
		If tscmbRound.Text = "" Or tscmbsport.Text = "" Or tscmbyear.Text = "" Then
			MessageBox.Show("Select all three categories Stupid")
			Exit Sub
		Else
			GetNflRound()
		End If
	End Sub

	Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
		Comboxquery()
	End Sub

	Dim time As DateTime = DateTime.Now
	Dim s As String = time.Year.ToString("yyyy")

	Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
		Dim time As DateTime = DateTime.Now
		Dim s As String = time.Year.ToString()
		TextBox1.Text = s
	End Sub
End Class

Open in new window

-saige-

Tom Powers

ASKER

saige thanks for your help i used your code and got

This is why i drink I feel like we are progressing different error everytime

Tom Powers

ASKER

what you wrote is what i'm after but just a quick
we are u
sing this

 sql += " Draft.round = " & roundnumbers & " AND"
        sql += " Draft.draftyear = " & tscmbyear.Text & " AND"
        sql += " Draft.sportcode = " & spcode & "ORDER BY Draft.draftyear DESC"

Open in new window

so is that better then

adapter = New SqlDataAdapter(sql, connection)

            ' adapter.SelectCommand.Parameters.AddWithValue("@round", roundnumbers)
            ' adapter.SelectCommand.Parameters.AddWithValue("@draftyear", draftyear)
            ' adapter.SelectCommand.Parameters.AddWithValue("@sportcode", spcode)
            adapter.Fill(ds)

Open in new window

What do you think

ASKER CERTIFIED SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Powers

ASKER

HEY I entered

PlayerID, np.TeamID, 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 Join Draft"
        sql += " ON np.PlayerID = Draft.PlayerID  WHERE Draft.round = 7 AND Draft.draftyear = 2013 AND Draft.sportcode = 12 ORDER BY  Draft.draftyear DESC"

Open in new window

I got

it_saige

Your join on draft is specified twice:

PlayerID, np.TeamID, 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 = 7 AND Draft.draftyear = 2013 AND Draft.sportcode = 12 ORDER BY  Draft.draftyear DESC"

Open in new window

It is legal to have multiple joins to the same table but each join has to use a different identifier.

-saige-

Tom Powers

ASKER

SAIGE THANK SO MUCH MY STRESS LEVEL WAS THROUGH THE ROOF BUT NOE i CAN HANDLE THE REST. HAVE A GREAT WEEKEND GO EAGLES. SAIGE YOUR THE HERO OF THE DAY

it_saige

Glad I was able to help.

-saige-