Solved

sql query that uses parameters but it must be ready to handle a null value

Posted on 2014-10-24
13
141 Views
Last Modified: 2014-10-28
Hey Guys,

I have a issue I need to create a sql query based on users input here is code I wrote I think it's has mutiple issues
 Public Sub InsaneQuery()
        Dim sql As String
        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  np.FirstName, np.LastName, np.Position, Teams.Fullname, np.College, Draft.draftyear, Draft.round,"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID,np.TeamID, np.PlayerID"
        sql += " FROM NflPlayers np Join Draft ON np.PlayerID = Draft.PlayerID Join Teams ON Teams.TeamID = Draft.DraftTeamID WHERE"
        sql += " np.Team = @np.Team AND np.College = @np.College AND @np.Position = @np.Position AND @np.Status = @np.Status AND @np.Lastname = @np.Lastname AND @np.PlayerID = @np.PlayerID"
        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Team", txtTeamBox.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.College", txtCollege2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Position", cmbPosition2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Status", cmbStatus.Text)
            If cmbPosition2.Text = "Lastname" Then

                adapter.SelectCommand.Parameters.AddWithValue("@np.Lastname", txtSearch.Text)
            End If
            If cmbPosition2.Text = "PlayerID" Then
                adapter.SelectCommand.Parameters.AddWithValue("@np.PlayerID", txtSearch.Text)
            End If
            adapter.Fill(ds)
            connection.Close()
            DataGridView1.DataSource = ds.Tables(0)
        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

        Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ColumnHeadersHeight = 55
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ColumnHeadersHeight = 55
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        DataGridView1.Columns("Lastname").Frozen = True
    End Sub

Open in new window


here is a screenshot I need it to handle null value if a  user input doesn't not use all textboxes or comboboxes
you click View and magic is supposed to happen
One more so you know I'm asking for help on
Another screen shot The toolbar is a sql query builder
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
  • 8
  • 4
13 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40402985
I usually use ISNULL() function to set the value equal to itself if the parameter is null in this situation.  In general:

np.Team = ISNULL(@np.Team, np.Team) and so on ...
0
 

Author Comment

by:powerztom
ID: 40403032
Chris I'm getting
Nullexception
here's your code Did I write it correctly I'm not an expert like the EE Guys I do appreciate your help

 Public Sub InsaneQuery()
        Dim sql As String

        Dim SelectCommand As New SqlCommand
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus"
        connection = New SqlConnection(connetionString)
        DataGridView1.DataSource = Nothing
        DataGridView1.ClearSelection()


        sql = "SELECT  np.FirstName, np.LastName, np.Position, Teams.Fullname, np.College, Draft.draftyear, Draft.round,"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID,np.TeamID, np.PlayerID"
        sql += " FROM NflPlayers np Join Draft ON np.PlayerID = Draft.PlayerID Join Teams ON Teams.TeamID = Draft.DraftTeamID WHERE"
        sql += " np.Team = ISNULL(@np.Team, np.Team)  AND np.Team = ISNULL(@College.Team, College.Team)  AND ISNULL(@np.Position = @np.Position) AND ISNULL(@np.Status = @np.Status) AND ISNULL(@np.Lastname = @np.Lastname) AND ISNULL(@np.PlayerID = @np.PlayerID)"
        Try
            Dim ds As New DataSet
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Team", txtTeamBox.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.College", txtCollege2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Position", cmbPosition2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Status", cmbStatus.Text)
            If cmbPosition2.Text = "Lastname" Then

                adapter.SelectCommand.Parameters.AddWithValue("@np.Lastname", txtSearch.Text)
            End If
            If cmbPosition2.Text = "PlayerID" Then
                adapter.SelectCommand.Parameters.AddWithValue("@np.PlayerID", txtSearch.Text)
            End If
            adapter.Fill(ds)
            connection.Close()
            DataGridView1.DataSource = ds.Tables(0)
        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

        Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ColumnHeadersHeight = 55
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ColumnHeadersHeight = 55
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        DataGridView1.Columns("Lastname").Frozen = True
    End Sub

Open in new window

0
 

Author Comment

by:powerztom
ID: 40403117
I screwed up the syntax I changed
 sql = "SELECT  np.FirstName, np.LastName, np.Position, Teams.Fullname, np.College, Draft.draftyear, Draft.round,"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID,np.TeamID, np.PlayerID"
        sql += " FROM NflPlayers np Join Draft ON np.PlayerID = Draft.PlayerID Join Teams ON Teams.TeamID = Draft.DraftTeamID WHERE"
        sql += " np.Team = ISNULL(@np.Team, np.Team)  AND np.College = ISNULL(@College.Team, College.Team)  AND np.Position = ISNULL(@np.Position = @np.Position) AND Status ISNULL(@np.Status = @np.Status) AND Lastname =ISNULL(@np.Lastname = @np.Lastname) AND np.PlayerID = ISNULL(@np.PlayerID = @np.PlayerID)"
        Try

Open in new window

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:powerztom
ID: 40403118
But still getting same error. Help me out of this issue please
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40403351
There are a few problems with the sql in ID: 40403117
without changing that code, just pushing it about with some spaces to align stuff, the where clause looks like this:
WHERE np.Team     = ISNULL(@np.Team     , np.Team)  
AND   np.College  = ISNULL(@College.Team, College.Team)  
AND   np.Position = ISNULL(@np.Position = @np.Position) 
AND   Status        ISNULL(@np.Status   = @np.Status) 
AND   Lastname    =ISNULL(@np.Lastname  = @np.Lastname) 
AND   np.PlayerID = ISNULL(@np.PlayerID = @np.PlayerID)

Open in new window

Notice how only the first 2 ISNULL() references use a comma (the others contain an =) they all should use a comma
also
there is no alias in front of Status or Lastname
row 2, where is the table College in this  query?
row 4, there's an equal sign missing

The best I could come up with is this:
WHERE np.Team   = ISNULL(@np.Team     , np.Team)  
AND np.College  = ISNULL(@College.Team, College.Team)  
AND np.Position = ISNULL(@np.Position , np.Position) 
AND Status      = ISNULL(@np.Status   , np.Status) 
AND Lastname    = ISNULL(@np.Lastname , np.Lastname) 
AND np.PlayerID = ISNULL(@np.PlayerID , np.PlayerID)

Open in new window

But there is no College table in the from clause so I don't know what that should be.

Another way to handle those parameters is this:
WHERE (np.Team   = @np.Team      or @np.Team IS NULL)  
AND (np.College  = @College.Team or @College.Team IS NULL)  
AND (np.Position = @np.Position  or @np.Position IS NULL)  
AND (Status      = @np.Status    or @np.Status IS NULL)  
AND (Lastname    = @np.Lastname  or @np.Lastname IS NULL)  
AND (np.PlayerID = @np.PlayerID  or @np.PlayerID IS NULL)  

Open in new window


Although I hope it's obvious I do suggest working out your where clause as one condition per line, it really helps identify inconsistencies.
0
 

Author Comment

by:powerztom
ID: 40406389
Hey Guys  thanks for your help I'm still having an issue I get
error 1
This is where the compiler stops.
error2here is query code
Public Sub InsaneQuery()
        Dim sql As String

        Dim SelectCommand As New SqlCommand
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=xxxxxxx"
        connection = New SqlConnection(connetionString)
        DataGridView1.DataSource = Nothing
        DataGridView1.ClearSelection()


        sql = "SELECT  np.FirstName, np.LastName, np.Position, Teams.Fullname, np.College, Draft.draftyear, Draft.round,"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID,np.TeamID, np.PlayerID"
        sql += " FROM NflPlayers np Join Draft ON np.PlayerID = Draft.PlayerID Join Teams ON Teams.TeamID = Draft.DraftTeamID WHERE"
        sql += " (np.TeamID = @np.TeamID or @np.TeamID IS NULL) AND (np.College = @np.College or @np.College IS NULL) AND"
        sql += " (np.Position = @np.Position or @np.Position IS NULL) AND (np.Status = @np.Status or @np.Status IS NULL) AND"
        sql += " (np.Lastname = @np.Lastname or @np.Lastname IS NULL) AND (np.PlayerID = @np.PlayerID or @np.PlayerID IS NULL)"


        Try
            Dim ds As New DataSet
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Team", txtTeamBox.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.College", txtCollege2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Position", cmbPosition2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Status", cmbStatus.Text)
            If cmbPosition2.Text = "Lastname" Then

                adapter.SelectCommand.Parameters.AddWithValue("@np.Lastname", txtSearch.Text)
            End If
            If cmbPosition2.Text = "PlayerID" Then
                adapter.SelectCommand.Parameters.AddWithValue("@np.PlayerID", txtSearch.Text)
            End If
            adapter.Fill(ds)
            connection.Close()
            DataGridView1.DataSource = ds.Tables(0)
        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

        Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ColumnHeadersHeight = 55
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ColumnHeadersHeight = 55
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        DataGridView1.Columns("Lastname").Frozen = True
    End Sub

Open in new window

0
 

Author Comment

by:powerztom
ID: 40406421
Could it be this part I also had TEAM Listed when it really is np.TeamID AND College is definitely np.College
 Try
            Dim ds As New DataSet
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@np.TeamID", txtTeamBox.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.College", txtCollege2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Position", cmbPosition2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@np.Status", cmbStatus.Text)
            If cmbPosition2.Text = "Lastname" Then

                adapter.SelectCommand.Parameters.AddWithValue("@np.Lastname", txtSearch.Text)
            End If
            If cmbPosition2.Text = "PlayerID" Then
                adapter.SelectCommand.Parameters.AddWithValue("@np.PlayerID", txtSearch.Text)
            End If
            adapter.Fill(ds)
            connection.Close()
            DataGridView1.DataSource = ds.Tables(0)

Open in new window

0
 

Author Comment

by:powerztom
ID: 40406425
I removed then put back the np.
Dim ds As New DataSet
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.SelectCommand.Parameters.AddWithValue("@TeamID", txtTeamBox.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@College", txtCollege2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@Position", cmbPosition2.Text)
            adapter.SelectCommand.Parameters.AddWithValue("@Status", cmbStatus.Text)
            If cmbPosition2.Text = "Lastname" Then

                adapter.SelectCommand.Parameters.AddWithValue("@Lastname", txtSearch.Text)
            End If
            If cmbPosition2.Text = "PlayerID" Then
                adapter.SelectCommand.Parameters.AddWithValue("@PlayerID", txtSearch.Text)
            End If
            adapter.Fill(ds)
            connection.Close()
            DataGridView1.DataSource = ds.Tables(0)
        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40407570
try it after replacing

@np.

with

@

        sql = "SELECT  np.FirstName, np.LastName, np.Position, Teams.Fullname, np.College, Draft.draftyear, Draft.round,"
        sql += " Draft.Draftpos, Draft.OverAllPos, Draft.DraftteamID, Draft.OrigTeamID,np.TeamID, np.PlayerID"
        sql += " FROM NflPlayers np Join Draft ON np.PlayerID = Draft.PlayerID Join Teams ON Teams.TeamID = Draft.DraftTeamID WHERE"
        sql += " (np.TeamID = @TeamID or @TeamID IS NULL) AND (np.College = @College or @College IS NULL) AND"
        sql += " (np.Position = @Position or @Position IS NULL) AND (np.Status = @Status or @Status IS NULL) AND"
        sql += " (np.Lastname = @Lastname or @Lastname IS NULL) AND (np.PlayerID = @PlayerID or @PlayerID IS NULL)"

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40407576
But you must declare any @xyz parameters in TSQL before you can use them, e.g.

DECLARE @Team INT

And set their values (or they will all be null) , e.g.

SET @Team = 1

In the code you have provided there is no evidence you are doing either
0
 

Author Closing Comment

by:powerztom
ID: 40409111
You deserve points which lead me down a path in which I made it so simple Check it
0
 

Author Comment

by:powerztom
ID: 40409113
public void TomsSQL()
		{
			sql = "";
			sql = "SELECT np.FirstName, np.LastName, np.Position,Teams.Fullname, np.College,np.Status,np.Jersey, np.Height,";
			sql += " np.Weight, np.Born,np.BioHtml, np.HighSchool,np.TeamID, np.PlayerID, np.draftyear, np.round,np.DraftPos, np.OverallPos, np.YrColl, np.DraftTeamID, np.CollTeamID";
			sql += " FROM NFLPlayers np Join Teams ON Teams.TeamID = np.TeamID WHERE";
			sql += " np.Status =\'A\' AND np.Position<> \'\'";
			if (txtTeamBox.Text.Length != 0)
			{
				TeamID();
			}
			if (cmbCollege.Text != "")
			{
				College();
			}
			if (cmbPosition2.Text.Length != 0)
			{
				Position();
			}
			if (cmbStatus.Text.Length != 0)
			{
				statuscheck();
			}
			if (cmbSearch2.Text.Length != 0 && cmbSearch2.Text == "Lastname")
			{
				Lastname();
			}
			if (cmbSearch2.Text.Length != 0 && cmbSearch2.Text == "PlayerID")
			{
				PlayerID();
			}
		}

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40409645
Yes, that makes a great deal more sense .... and will be more efficient in SQL terms as well.

Well done.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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