Solved

QUERY WILL NOT WORK DIFFERENT ERROR EACH TIME

Posted on 2014-11-14
4
197 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
  • 2
4 Comments
 
LVL 32

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

747 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

12 Experts available now in Live!

Get 1:1 Help Now