concatination of two rows of a datagridview in vb.net

I need to create a sql statement that can be customized with values from dropdown boxes and more.
There are several values that can make up a sql statement on the fly. LIKE TEAMID , POSITION LIKE WR"Wide Reciever", college, playerID, OR last name Could so I use this piece of code as an example
 Public Sub Position()
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim strPosition As String = tscmb.Text
        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=xxxxxxx"

        connection = New SqlConnection(connetionString)
        ' txtsql.Text = ""

        DataGridView1.DataSource = Nothing
        DataGridView1.ClearSelection()
        refreshdatagrid()
        ds4 = New DataSet
        sql = sql & " AND " & "Position='" & strPosition & "'"


        txtsql.Text = sql
        Try
           
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.Fill(ds4)
            connection.Close()
            DataGridView1.DataSource = ds4.Tables(0)
            DataGridView1.Refresh()
        Catch ex As SqlException
            MsgBox(ex.ToString)
        End Try
    End Sub

Open in new window


That is the position query but I want to combine

 Public Sub TeamID()
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim Teamx As String = txtIDTeam.Text
        ' 
        ' txtsql.Text = ""
        txtTeamID.Text = ""
        DataGridView1.DataSource = Nothing
        DataGridView1.ClearSelection()
        refreshdatagrid()
        ds1 = New DataSet

        connetionString = "Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=xxxxxxx"
        sql = sql & " AND" & " " & "TeamID='" & Teamx & "'"
        connection = New SqlConnection(connetionString)
        txtsql.Text = sql

        Try
            connection.Open()
            adapter = New SqlDataAdapter(sql, connection)
            adapter.Fill(ds1)
            connection.Close()
            DataGridView1.DataSource = ds1.Tables(0)
            DataGridView1.Refresh()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        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


I would like to build a query concatination my worker calls it.
example of query i need to create
Select PlayerID,FirstName,LastName,TeamID,Jersey,Position,Status,Height,Weight,Born,College,BioHTML,NCAAID, DraftYear, Round, DraftPos, OverallPos,Slug,DraftTeamID,CollTeamID,YrColl, HighSchool FROM NFLPLAYERS WHERE Status ='A' AND Position<> '' AND TeamID='089' AND Position='WR' AND College='Purdue'

The thing is for me to create this sql query I can't write it out like I just did I have to have user select team then insert a AND then select Position here are pictures that I hope clarify if I'm not making things clear.

This would be a selection for position WR

This would be team selection and i'd like for wr to stay in query.
this is a screen of just the team selected the Eagles soon to be Super Bowl Champs
This is the sql box I use to show me what sql query looks like
I just need a way to combine these diffferent datafields to be made into a sql query. Sorry for length
EE U HAVEN'T LET ME DOWN YET.
powerztomAsked:
Who is Participating?
 
Kyle AbrahamsSenior .Net DeveloperCommented:
(@LastName is null or LastName  like '%'+ @LastName'%') AND

Like or = . . . no both
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
What I normally do in a search field like that:

1)  Create a stored procedure.

2)  Add parameters for each possible filter

3)  Set all params default to null

eg:
create procedure myExampleSearch
@SomeID int = null
@PlayerName varchar(500) = null
as

select * from table t1
join anothertable t2 on t1.PrimaryKey = t2.ForeignKey
where
(@SomeID is null or t1.SomeID = @SomeID) AND
(@playerName is null or t2.PlayerName like '%' + @playerName'%')  -- AND repeat as needed.

Open in new window

0
 
powerztomAuthor Commented:
I only have one table I'm dealing with however I see want you are trying to do I'm gonna try it.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
powerztomAuthor Commented:
cAN YOU WRITE THAT IN VB CODE NOT ALL BUT JUST ENOUGH TO  GET ME ROCKING.
0
 
powerztomAuthor Commented:
Kyle Abrahams

I made this does it look right and what do I do Execute it then call it from vb Program and any tips of what the vb code would look like.
create procedure NFLMultisearch
@TeamID char(3) = null
@PlayerID int = null
@Position varchar(15) = null
@Status char(2) = null
@LastName char(25) = null
@College char(20)
as

Select PlayerID,FirstName,LastName,TeamID,Jersey,Position,Status,Height,Weight,Born,College,BioHTML,NCAAID, DraftYear, Round, DraftPos, OverallPos,Slug,DraftTeamID,CollTeamID,YrColl, HighSchool FROM NFLPLAYERS WHERE Status ='A' AND Position<> ''
WHERE
(@TeamID is null or TeamID = @TeamID) AND
(@PlayerID is null or PlayerID = @PlayerID) AND
(@LastName is null or LastName = like '%' + @@LastName'%') AND
(@College is null or College = @College) AND
(Position is null or Position = @Position) AND
(Status is null or Status = @Status)

Open in new window


Please HELP I know sql queries but stored procedures I get but I'm not always sure how to call them and pulling data in. Looks cool though
0
 
powerztomAuthor Commented:
forget about the extra where
0
 
powerztomAuthor Commented:
Dude I got
Msg 102, Level 15, State 1, Procedure NFLMultisearch, Line 3
Incorrect syntax near '@PlayerID'.
Msg 102, Level 15, State 1, Procedure NFLMultisearch, Line 12
Incorrect syntax near '@TeamID'.
 not sure
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You need commas after each param
and you char variable says it will always be X characters.  Varchar says it can be any length up to X characters.

create procedure NFLMultisearch
@TeamID varchar(3) = null,
@PlayerID int = null,
@Position varchar(15) = null,
@Status varchar(2) = null,
@LastName varchar(25) = null,
@College varchar(20) = null
as


Select PlayerID,FirstName,LastName,TeamID,Jersey,Position,Status,Height,Weight,Born,College,BioHTML,NCAAID, DraftYear, Round, DraftPos, OverallPos,Slug,DraftTeamID,CollTeamID,YrColl, HighSchool FROM NFLPLAYERS WHERE Status ='A' AND Position<> ''
WHERE
(@TeamID is null or TeamID = @TeamID) AND
(@PlayerID is null or PlayerID = @PlayerID) AND
(@LastName is null or LastName = like '%' + @LastName'%') AND
(@College is null or College = @College) AND
(Position is null or Position = @Position) AND
(Status is null or Status = @Status)

Open in new window


To call it from vb.Net:
http://www.codeguru.com/columns/vb/using-sql-stored-procedures-with-vb.net.htm
0
 
powerztomAuthor Commented:
Thanks for link I'm getting Msg 102, Level 15, State 1, Procedure NFLMultisearch, Line 13
Incorrect syntax near '@TeamID'. AM I missing something I'm not senior sql guy but I do APPRECIATE YOUR HELP


create procedure NFLMultisearch
@TeamID varchar(3) = null,
@PlayerID int = null,
@Position varchar(15) = null,
@Status varchar(2) = null,
@LastName varchar(25) = null,
@College varchar(20) = null
as


Select PlayerID,FirstName,LastName,TeamID,Jersey,Position,Status,Height,Weight,Born,College,BioHTML,NCAAID, DraftYear, Round, DraftPos, OverallPos,Slug,DraftTeamID,CollTeamID,YrColl, HighSchool FROM NFLPLAYERS WHERE Status ='A' AND Position<> ''

(@TeamID is null or TeamID = @TeamID) AND
(@PlayerID is null or PlayerID = @PlayerID) AND
(@LastName is null or LastName = like '%' + @LastName'%') AND
(@College is null or College = @College) AND
(Position is null or Position = @Position) AND
(Status is null or Status = @Status)

Open in new window

0
 
powerztomAuthor Commented:
i'M JUST GOING WITH THE DATATYPES IN THE SQL SERVER SHOULD iM CHANGE VARCHAR TO CHAR?
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you're missing an "AND":  I left this out of the code block so I could bold it

==================================================================

Select PlayerID,FirstName,LastName,TeamID,Jersey,Position,Status,Height,Weight,Born,College,BioHTML,NCAAID, DraftYear, Round, DraftPos, OverallPos,Slug,DraftTeamID,CollTeamID,YrColl, HighSchool FROM NFLPLAYERS WHERE Status ='A' AND Position<> '' AND

(@TeamID is null or TeamID = @TeamID) AND
(@PlayerID is null or PlayerID = @PlayerID) AND
(@LastName is null or LastName = like '%' + @LastName'%') AND
(@College is null or College = @College) AND
(Position is null or Position = @Position) AND
(Status is null or Status = @Status)


=========================================================

If that's the way they are in the database I would match the DB.  My apologies on changing them, not used to seeing chars.
0
 
powerztomAuthor Commented:
kYLE,
Good Morning I want to thank you for helping get this it looks like I can do this in vb.net except for some unknown reason to me everything looks good with this Stored Procedure but here's is what I got
create procedure NFLMultisearch
@TeamID varchar(3) = null,
@PlayerID int = null,
@Position varchar(15) = null,
@Status varchar(2) = null,
@LastName varchar(25) = null,
@College varchar(20) = null
as


Select PlayerID,FirstName,LastName,TeamID,Jersey,Position,Status,Height,Weight,Born,College,BioHTML,NCAAID, DraftYear, Round, DraftPos, OverallPos,Slug,DraftTeamID,CollTeamID,YrColl, HighSchool FROM NFLPLAYERS WHERE Status ='A' AND Position<> ''
AND
(@TeamID is null or TeamID = @TeamID) AND
(@PlayerID is null or PlayerID = @PlayerID) AND
(@LastName is null or LastName = like '%'+ @LastName'%') AND
(@College is null or College = @College) AND
(@Position is null or Position = @Position) AND
(@Status is null or Status = @Status)

Open in new window


when I execute the stored procedure i get

Msg 156, Level 15, State 1, Procedure NFLMultisearch, Line 15
Incorrect syntax near the keyword 'like'.

I can write basic queries but I'm more a vb guy this is great cause I'm learning but any ideas.
0
 
powerztomAuthor Commented:
Yeah Kyle my new friend Thanks for sticking we me and showing me something new Now It's my turn to code the vb part.Thanks for your patience with all of this I'm learning sql and .net everyday
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
No problem.  We've all been there.  Going through the fire is the way to learn.
0
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.

All Courses

From novice to tech pro — start learning today.