QUERY WILL NOT WORK DIFFERENT ERROR EACH TIME

I can't get this sql query to load the datagridview.First off I have a table called nflplayerspass it has the most important field called Season with this I can filter a season Now look
season errorIn SQLEXPRESS MANAGEMENT Studio I can write queries no problem However when I load my query I get ambitious column Season so If I takeaway season from the query and combobox I can retrieve data. I am angry and frustrated How can Season be in database but in my winform I can't pull any data based on year Without Season(year) I'm dead in the water Here is queryMaybe I missed something.  
Here is the code
 Public Sub GetpassingNfl()

        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 PK_NFLPLAYERSPASS,np.FirstName,np.LastName, t.Fullname,np.PlayerID,np.TeamID, npp.SeasonType,npp.Season,npp.Conference,npp.Att,npp.Comp,npp.Yards,npp.Long,npp.TD,npp.Inter,npp.Sacks,npp.SKYds,npp.Rating FROM NFLPlayersPass npp "
        sql += " join NflPlayers np on np.PlayerID = npp.PlayerID join Teams t on t.TeamID = npp.team WHERE"
        sql += " npp.Season = " & cmbseason.SelectedText & "Order By np.Lastname"

       
        If cmbseason.Text = "" Then
            Exit Sub
        End If
        

        connection = New SqlConnection(connetionString)

        Try

            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.Fill(ds)
            connection.Close()
            dvgPass.DataSource = ds.Tables(0)

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


        
        Me.dvgPass.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgPass.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgPass.ColumnHeadersHeight = 55
        dvgPass.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
       

        Me.dvgPass.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.dvgPass.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        dvgPass.ColumnHeadersHeight = 55
        dvgPass.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        ' Add the image column to the grid.
        ' DataGridView1.Columns.Add(imageCol)

        dvgPass.Columns("Lastname").Frozen = True

    End Sub

Open in new window



Latest error then what app such do I'm not a great DB Person learning

latest errror
vbnet app
powerztomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
The problem is will be obvious when you use MessageBox.Show(sql) before you use the variable sql with the adapter. There IS e.g. a space missing before ORDER BY. You can use ctrl-c to copy the message box output to test it in SSMS.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rawinnlnx9Commented:
 sql = " Select PK_NFLPLAYERSPASS,np.FirstName,np.LastName, t.Fullname,np.PlayerID,np.TeamID, npp.SeasonType,npp.Season,npp.Conference,npp.Att,npp.Comp,npp.Yards,npp.Long,npp.TD,npp.Inter,npp.Sacks,npp.SKYds,npp.Rating FROM NFLPlayersPass npp "
        sql += " join NflPlayers np on np.PlayerID = npp.PlayerID join Teams t on t.TeamID = npp.team WHERE"
        sql += " npp.Season = '" & cmbseason.SelectedText & "' Order By np.Lastname"

Open in new window


You have to use the debugger to see what's in sql before you send it. If you look at that you'll see that your SQL lacks white space and the single quotes necessary to indicate the data value.

You have to use escape characters at times or get familiar with using the @ when concattenating strings.

sql += npp.Season & "='" & cmbseason.SelectedText & "' Order By " & np.Lastname 

Open in new window

 

Hover the cursor over sql once you step over that last += and then copy your entire SQL text out of the debugger. Or in the immediate window type "sql" and hit enter. To get the contents. Either way your concatenation is terrible.

Not only that this SQL screams SQL Injection. This is not how you get data. You build stored procedures and pass the data as parameters and you enforce length.


Below is an example of how you do this correctly. You will see that length is enforced I would also suggest you look into properly encoding string types for use in queries, html, etc...

SqlCommand scCommand = new SqlCommand("usp_CheckEmailMobile", sqlCon);
scCommand.CommandType = CommandType.StoredProcedure;
scCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtName.Text;
scCommand.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = txtEmailAddress.Text;
scCommand.Parameters.Add("@Password ", SqlDbType.NVarChar, 50).Value = txtPassword.Text;
scCommand.Parameters.Add("@CountryCode", SqlDbType.VarChar.50).Value =ddlCountryCode.SelectedText;
scCommand.Parameters.Add("@Mobile", SqlDbType.NVarChar, 50).Value = txtMobileNumber.Text;
scCommand.Parameters.Add("@Result ", SqlDbType.Bit).Direction = ParameterDirection.Output;
try

Open in new window

0
powerztomAuthor Commented:
Dude awesome debugging trick I never used messagebox to see what I was missing.Thank You
0
powerztomAuthor Commented:
rawinnlnx9 thanks for the tips.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.