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
powerztomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

powerztomAuthor Commented:
But still getting same error. Help me out of this issue please
0
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
Yes, that makes a great deal more sense .... and will be more efficient in SQL terms as well.

Well done.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.