SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display the results of search in a DataGridView


Display the results of search in a DataGridView

Author
Message
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 191
Hi to all especially to those who always answer my questions wholeheartedly :-)


I Have another question..and this is my Code.....

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


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SearchBiography]

@firstname varchar(50),
@middlename varchar(50),
@lastname varchar(50),
@sex varchar(50),
@status varchar(50)
-- @bioID int


AS
BEGIN

SET NOCOUNT ON;
DECLARE @SqlQuery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQueryStatus varchar(max), @SqlQueryFullName varchar(max)

SET @SqlQuery = ''

IF LEN(@firstname) > 0

SET @SqlQueryFirstName = ' AND firstname like ''%' + @firstname + '%'''

ELSE

SET @SqlQueryFirstName = ''



IF LEN(@middlename) > 0

SET @SqlQueryMiddleName = ' AND middlename like ''%' + @middlename + '&'''

ELSE

SET @SqlQueryMiddleName = ''



IF LEN(@lastname) > 0

SET @SqlQueryLastName =' AND lastname like ''%' + @lastname + '%'''

ELSE

SET @SqlQueryLastName = ''



SET @SqlQuery = 'SELECT * FROM TestMyView WHERE ' + ' sex like ''%' + @sex + '%''' + ' AND status like ''%' +@status + '%'''
SET @SqlQuery = @SqlQuery + @SqlQueryFirstName + @SqlQueryMiddleName + @SqlQueryLastName + @SqlQueryStatus


EXEC(@SqlQuery)
PRINT(@SqlQuery)

END

===========================================================I

I already have a class in my front-end to call the SP(Stored Procedure)..but how can i display the result of the search on my dataGridView

I can simply do this ( TestMyViewTableAdapter.FillByAll(Me.BiographyDataBIOGRAPHY.TestMyView, SexBindingSource.Current("sex"), StatusBindingSource.Current("status"), txtbxFname.Text, txtbxMname.Text, txtbxLname.Text)) using dataset to display the result without using SP.... but i like to use SP, i want to code it since i am a newbie, so that i can appreciate on what is instant...

Help me please :-( Thank you :-)

See my UI Attachment
Attachments
searchResult.JPG (9 views, 40.00 KB)
pietlinden
pietlinden
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4838 Visits: 13160
Just say NO to dynamic SQL!!! That opens doors to all kinds of REALLY bad things! I should probably go find Kevin Kline's "SQL Server Low-Hanging Fruit" presentation - Wherever possible, you want to us the smallest and most restrictive data type possible in your stored procedures. Dynamic SQL in my opinion is a tool of last resort.

What if you have a simple stored procedure like you have with huge parameters and someone passes "; INSERT INTO..." and then mails himself the results using DBMail? You're in serious trouble. Not good at all.

I would suggest a more more restrictive approach. Gender can be (usually) one of 3 options: M, F, [U]nknown/Null. So make the field a CHAR, and then it's impossible to stuff any junk in there that doesn't belong.
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 191
pietlinden (9/23/2013)
Just say NO to dynamic SQL!!! That opens doors to all kinds of REALLY bad things! I should probably go find Kevin Kline's "SQL Server Low-Hanging Fruit" presentation - Wherever possible, you want to us the smallest and most restrictive data type possible in your stored procedures. Dynamic SQL in my opinion is a tool of last resort.

What if you have a simple stored procedure like you have with huge parameters and someone passes "; INSERT INTO..." and then mails himself the results using DBMail? You're in serious trouble. Not good at all.

I would suggest a more more restrictive approach. Gender can be (usually) one of 3 options: M, F, [U]nknown/Null. So make the field a CHAR, and then it's impossible to stuff any junk in there that doesn't belong.



Hi pietlinden :-)

Thank you for answering my question my friend :-)

But can you elaborate your answer...i am really intersted with your answer.. :-) thanks ;-)

Here Hoping ;-)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25952 Visits: 17519
pietlinden (9/23/2013)
Just say NO to dynamic SQL!!! That opens doors to all kinds of REALLY bad things! I should probably go find Kevin Kline's "SQL Server Low-Hanging Fruit" presentation - Wherever possible, you want to us the smallest and most restrictive data type possible in your stored procedures. Dynamic SQL in my opinion is a tool of last resort.



I completely disagree here. Dynamic sql is incredibly powerful and is absolutely the right tool for this type of query. The problem is the approach is completely wrong. This code is wide open to sql injection attack.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25952 Visits: 17519
PLEASE do yourself a favor and read this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

It has been suggested to you at least a couple of other times in the various threads you have started all on basically the same topic.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
enriquezreyjoseph
enriquezreyjoseph
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 191
Sean Lange (9/24/2013)
PLEASE do yourself a favor and read this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

It has been suggested to you at least a couple of other times in the various threads you have started all on basically the same topic.



Hi Sean

Yeah i just read all the articles that is given to me(catch all queries)

but, i'm confuse because some other post says that dynamic sql is a no no, can you enlighten me sean??? :-(

Thanks Champion :-)
OTF
OTF
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 4128
enriquezreyjoseph (9/24/2013)
Sean Lange (9/24/2013)
PLEASE do yourself a favor and read this article. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

It has been suggested to you at least a couple of other times in the various threads you have started all on basically the same topic.



Hi Sean

Yeah i just read all the articles that is given to me(catch all queries)

but, i'm confuse because some other post says that dynamic sql is a no no, can you enlighten me sean??? :-(

Thanks Champion :-)


You'll have to learn as you go along not to take everything you read online as gospel (including this :-)).
There is nothing inherently wrong with Dynamic Sql as long as its the right tool for the job and you take the
time to ensure its known weaknesses can't be exploited, such as, Sql Injection.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search