Solved

concatination of two rows of a datagridview in vb.net

Posted on 2014-09-24
14
196 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
  • 9
  • 5
14 Comments
 
LVL 39

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
 

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 39

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 39

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 39

Accepted Solution

by:
Kyle Abrahams earned 500 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 39

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now