asked on

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.

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.

Kyle Abrahams, PMP

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

Tom Powers

ASKER

I only have one table I'm dealing with however I see want you are trying to do I'm gonna try it.

Tom Powers

ASKER

cAN YOU WRITE THAT IN VB CODE NOT ALL BUT JUST ENOUGH TO GET ME ROCKING.

Tom Powers

ASKER

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

Tom Powers

ASKER

forget about the extra where

Tom Powers

ASKER

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

Kyle Abrahams, PMP

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

Tom Powers

ASKER

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

Tom Powers

ASKER

i'M JUST GOING WITH THE DATATYPES IN THE SQL SERVER SHOULD iM CHANGE VARCHAR TO CHAR?

Kyle Abrahams, PMP

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.

Tom Powers

ASKER

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.

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Powers

ASKER

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

Kyle Abrahams, PMP

No problem. We've all been there. Going through the fire is the way to learn.