?
Solved

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

Posted on 2014-10-24
13
Medium Priority
?
143 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

765 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