Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

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

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
powerztom
Asked:
powerztom
  • 8
  • 4
1 Solution
 
Christopher GordonSenior Developer AnalystCommented:
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
 
powerztomAuthor Commented:
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
 
powerztomAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
powerztomAuthor Commented:
But still getting same error. Help me out of this issue please
0
 
PortletPaulCommented:
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
 
powerztomAuthor Commented:
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
 
powerztomAuthor Commented:
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
 
powerztomAuthor Commented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
powerztomAuthor Commented:
You deserve points which lead me down a path in which I made it so simple Check it
0
 
powerztomAuthor Commented:
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
 
PortletPaulCommented:
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!

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now