Solved

QUERY WILL NOT WORK DIFFERENT ERROR EACH TIME

Posted on 2014-11-14
4
235 Views
Last Modified: 2014-11-14
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
0
Comment
Question by:powerztom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
ste5an earned 500 total points
ID: 40443647
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
 
LVL 9

Expert Comment

by:rawinnlnx9
ID: 40443756
 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
 

Author Closing Comment

by:powerztom
ID: 40443766
Dude awesome debugging trick I never used messagebox to see what I was missing.Thank You
0
 

Author Comment

by:powerztom
ID: 40443773
rawinnlnx9 thanks for the tips.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

624 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