Tom Powers
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
eRROR MESSAGE
WHAT DRAFT LOOKS LIKE
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
eRROR MESSAGE
WHAT DRAFT LOOKS LIKE
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.
latest errormessage
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
latest errormessage
This error is basically stating that:
Are not boolean (true/false) values .
You need to compare Draft.round, Draft.draftyear and Draft.sportcode to something.
i.e., making up entries -
-saige-
Draft.round AND Draft.draftyear AND Draft.sportcode
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"
-saige-
I reviewed your code a little more. Here is ultimately what I think you might be after:
-saige-
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
-saige-
ASKER
ASKER
what you wrote is what i'm after but just a quick
we are u
sing this
What do you think
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"
so is that better thenadapter = 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)
What do you think
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
I got
Your join on draft is specified twice:
It is legal to have multiple joins to the same table but each join has to use a different identifier.
-saige-
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"
It is legal to have multiple joins to the same table but each join has to use a different identifier.
-saige-
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
Glad I was able to help.
-saige-
-saige-
Instead of:
Open in new window
Use:Open in new window
This code has it fixed:
Open in new window
Copy the above and try it again.
-saige-