Solved

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

Posted on 2014-10-24
13
131 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
  • 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
 

Author Comment

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

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 48

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 48

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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

930 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