Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

concatination of two rows of a datagridview in vb.net

Posted on 2014-09-24
14
Medium Priority
?
217 Views
Last Modified: 2014-09-25
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.
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
  • 9
  • 5
14 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40342242
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
 

Author Comment

by:powerztom
ID: 40342289
I only have one table I'm dealing with however I see want you are trying to do I'm gonna try it.
0
 

Author Comment

by:powerztom
ID: 40342331
cAN YOU WRITE THAT IN VB CODE NOT ALL BUT JUST ENOUGH TO  GET ME ROCKING.
0
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 

Author Comment

by:powerztom
ID: 40342409
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
 

Author Comment

by:powerztom
ID: 40342415
forget about the extra where
0
 

Author Comment

by:powerztom
ID: 40342427
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40342528
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
 

Author Comment

by:powerztom
ID: 40342612
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
 

Author Comment

by:powerztom
ID: 40342636
i'M JUST GOING WITH THE DATATYPES IN THE SQL SERVER SHOULD iM CHANGE VARCHAR TO CHAR?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40342673
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
 

Author Comment

by:powerztom
ID: 40343886
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
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 40344098
(@LastName is null or LastName  like '%'+ @LastName'%') AND

Like or = . . . no both
0
 

Author Closing Comment

by:powerztom
ID: 40344183
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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40344221
No problem.  We've all been there.  Going through the fire is the way to learn.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

722 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