Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display the results of search in a DataGridView Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 7:26 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 81, 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


  Post Attachments 
searchResult.JPG (3 views, 40.82 KB)
Post #1497617
Posted Monday, September 23, 2013 9:06 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:41 PM
Points: 571, Visits: 3,728
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.
Post #1497628
Posted Monday, September 23, 2013 10:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 81, 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
Post #1497641
Posted Tuesday, September 24, 2013 8:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
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 Moden's 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)
Post #1497889
Posted Tuesday, September 24, 2013 8:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 11,949, Visits: 10,982
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 Moden's 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)
Post #1497890
Posted Tuesday, September 24, 2013 6:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 21, 2013 3:10 AM
Points: 81, 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
Post #1498096
Posted Wednesday, September 25, 2013 6:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 31, 2013 3:44 AM
Points: 314, Visits: 4,128
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.

Post #1498296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse