C# - Filter Sql Server Table Rows Using Many Filters

Mar 23, 2011

I have a table called Student with a lot foreign keys that will be used after to filter students(table rows).The structure of this table is like:

StudentId GenderId DegreeId NatioanlityId
1 2 2 3
....

As a student can speak one or more language,the Sudent Table is linked to language table like this

StudentId LangaugeId
1 1
1 2
1 3

And a student can chose one or many subjects for exam:

StudentId ExamId
1 1
1 2
....

In my asp.net page I would like to filter students via checkbox using ajax for example, student having female and male gender with Degree 1,speaking language 1,2... I filter rows in a stored proc using user defined table,and I have to use a lot of IF statements like this

if(EXISTS(SELECT GenderId FROM @GenderTable))
if(EXISTS(SELECT DegreeId FROM @DegreeTable))
if(....)
else
if(...)

How can I avoid all the IF statements? I have more than 5 filters.It's boring.

View 1 Replies


Similar Messages:

Grid View Filters / How To Set Filter Two Or More Than Two Columns

Feb 21, 2011

Am running asp.net application with gridview.Am using filters to filter only one column.Now i want to filter two or more than two columns.How to i set the filters for that..if i give && or|| operators it is showing syntax error..

i given the code as;
"
ProviderName="<%$ ConnectionStrings:booksConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [book]"
FilterExpression="UserName Like '{0}%' || Password Like '{0}%'" >

View 1 Replies

Forms Data Controls :: Filters To A Gridview Control Like Filters In Excel Sheet?

May 26, 2010

How to apply filters to a gridview control like filters in excel sheet?

View 2 Replies

SQL Server :: Delete Objects From Table Using Another Table To Select Rows?

Sep 14, 2010

I have one table named 'dbo.ac_Products' Within this table there is a column named 'ProductId' There is also another table named 'dbo.ac_CatalogNodes' Within this table there is a column named 'CatalogNodeId' and a column named 'CatalogNodeType'

I need to delete all the rows from 'dbo.ac_Products' and 'dbo.ac_CatalogNodes' where 'ProductId' = 'CatalogNodeId' and 'CatalogNodeType' = 1

View 4 Replies

SQL Server :: How To Update Table Value From Another Table With Limit Rows

Jan 20, 2011

I have table a and would like update table a whose values from table b, but not update all rows at table a. Only update record ID in table a match record ID in tableC.

Here is my query

update a
set a.Desc = b.Desc,
a.Value = b.Value
from TableA a, TableB b
where a.name = b.name
and a.ID = b.ID
and a.ID in ( select ID from TableC)

I only have 12 a.ID match TableC, but above query update whole TableA.

How to only update 12 record at TableA from TableB?

View 2 Replies

SQL Server :: How To Filter The Updated Data From The Table To Display In The Report

Oct 18, 2010

I have a table which captures the updated data. I need to generate a report on updated fields.

how to filter the updated data from the table to display in the report.

I'm new to this so i need the sql code or stored procedure to filter and show only the updated data.

Well I have time stamp as well as a column for last changed date.

i need to produce all the fields pertaining to the updated cell in a single cell of the report.

for instance i change the last name of the person - i need to produce the persons firsname , updated lastname, his address and other details in a single cell of a report.

i also need to show the records in both ascending and decending way - like most recent update of the day and first update of the day.

View 3 Replies

SQL Server :: How To Get The Top N Rows From SQL Table

Aug 30, 2010

Recently i have come across one question in sql query

Question: Which SQL Query helps to view the TOP N rows From the particular SQL table?

View 3 Replies

SQL Server :: How To Create A Stored Procedure That Filters Data From Sql Server By Date

Oct 29, 2010

I am trying to create a stored procedure that filters data from sql server by date but i get this error:

Conversion failed when converting datetime from character string.

my stored procedure is like:

[Code]....

the parameters are passed to the stored procedures as dateTime variables from c# code...

View 10 Replies

SQL Server :: Comparing Rows In A Table?

Mar 11, 2011

I have a large database of user profile data. I need to be able to compare 1 row against all of the other rows, for all columns to find the best match.There are around 30 columns of data for each row. I need to be able to find the 5 best matching rows.

I.E.
Row 1 matches on 2 column's
Row 2 matches on 12 column's
Row 3 matches on 7 column's
Row 4 matches on 4 column's
Row 5 matches on 9 column's
Row 6 matches on 23 column's
Row 7 matches on 29 column's

In the end it will show me the data for rows 7, 6, 2, 5, 3 Now to make it even harder, Some of the columns are bit, some are varchar.Some of them may be a direct match, some will need to search if the column for row2 contains the text of row 1.

View 6 Replies

SQL Server :: Columns To Rows - Table

Nov 13, 2010

I have a table think as table1, with 3 columns

col1 col2 col3 -- Columns
Y N 1 -- Data

but i want

colname val
col1 Y
col2 N
col3 1

View 3 Replies

SQL Server :: Count How Many Rows Are In Data Table

Mar 5, 2011

I have a data table called Songs It has SongId as Primary Key of type INT SongName of Type VarChar UserId of Type Int. I simply want to check how many entries are in the Table already by The UserId before they insert more. So If there are already 3 songs by that User, I want to stop the insert process and warn them they have the maxium already. I think i am halfway there already using code below but problem is that UserId is of Type GUID, and not Int so I am missing something. On my Aspx, Page, I have a DetailsView for the Database Insert. I also have the following code using BusLogic Layer and DAL

SongsBLL
public int ScalarQuery(Guid ArtistId)
{
int returnValue = ScalarQuery(ArtistId);
return returnValue;
}
Code Behind ASPX page
This in in DetailsView_ Item Inserting Event
SongsBLL number = new SongsBLL();
currentCount = Convert.ToInt32(number.ScalarQuery(currentCount).ToString());..........

View 9 Replies

SQL Server :: Emailing Contents Of Table (Only Rows)

Dec 15, 2010

Let's say I have a table (Table1) with three columns (Col1, Col2, Col3) and two rows. Let's say I have a button on a page. I want to send the contents of those two rows in an email in the Click event handler of the button. Here's the SQL framework I've got:
[Code]....

View 11 Replies

SQL Server :: How To Iterate Through Rows Of A Temp Table

Mar 21, 2011

Suppose I have a temporary table (Shown below). The table is defined and populated in my stored procedure. After it is populated, I need to go through every row in the table. Here is what I need to do :

Check if Amount2 > Amount1 If Amount2 > Amount1 then set Amount1 = Amount2

It feels a little akward for me to use the temp table.

[code]....

View 2 Replies

SQL Server Deadlock While Inserting Rows In A Table

Oct 15, 2010

The topic of sql server deadlock has been discussed many times, however, I was unsure that even two simultaneous inserts on a table can end up in a deadlock situation. Scenario: While testing our application (SQL Server 2005 as backend, ASP.net 3.5) we inserted records into a table simultaneously (simplified overview) and that resulted into a deadlock for more than 70% of users.

I could not get a hang of this as how an insert is being deadlock as this is not a case of multiple resources. After a detailed analysis (of reproducing the bug by two users) I found that both the processes were holding a RangeS-S lock on the primary key index of the table and were trying to convert this into RangeI-N lock, that resulted into a deadlock and one transaction being killed.

Question: Can we avoid or reduce these kind of deadlocks as this is not a case of change in order of access of resources? Cant' we force the transaction to get exclusive lock initially so that it blocks the other process and avoid deadlock? What (adverse) effects that may have? Also could some one explain more about RangeI-N lock.

Isolation Level for this was "Serializable".

View 2 Replies

SQL Server :: Find Out Distinct Rows In Table

Sep 29, 2010

I have a one sql table, it contain 5 column id, ipaddress, status, datetime, value. I need to find distinct rows as per ipaddress column basic..

View 7 Replies

Reordering Table Rows On Server Side?

Feb 22, 2011

I have a table which contains asp radiobuttonlists (see below) for a survey. Once submitted the results are written to a database.I would like to use this same table for a similar survey, but the trouble is the question order needs to be different. The questions are the same, just in a different order. Is there a way (server side) to change the order of the table rows?

<table cellpadding="0" cellspacing="0" border="0">
<tr>
<td> </td>

[code]...

View 1 Replies

SQL Server :: Merge Two Rows In A Table With Same Date Time?

Mar 25, 2011

write the query for merging two rows in a table with same date and time into one row...

2009/12/23 13:46:50 -0.43546 0.02395 -7.1621 0.45529
2009/12/23 13:46:50 0 0 0 0
2009/12/23 13:47:00 -0.47114 -0.00515 -7.1779 0.42751
2009/12/23 13:47:00 0 0 0 0

i want to merge this same datetime rows into one row ..

View 4 Replies

SQL Server :: How To Concatenate Multiple Rows Of Single Table

Mar 8, 2011

I've a table where one person teaches multiple classes. It looks like this.

Table1

Id Name Class Subject
----------------------------------------
2 Sam 12 English
2 Sam 10 Maths
2 Sam 10 English
2 Sam 8 History
2 Sam 12 Economics

Here Sam teaches Class 12 - English & Economics, Class 10 - Maths & English, Class 8 - History

I want to display the result like this -

Id Name Class Subject
---------------------------------------------------------------
2 Sam 12 English, Economics
2 Sam 10 Maths, English
2 Sam 8 History

How to concatenate multiple Rows?

View 6 Replies

SQL Server :: Copying Few Rows With Selected Columns From One Table To Another

Mar 29, 2011

Table1

t1c1 t1c2 t1c3
1 1 jim
2 2 ruth
3 4 paul
4 8 sam
5 16 NA

Table2 ( Need this)

t2c1 t2c2 t2c3 t2c4 t2c5
10 1 1 jim 2010-02-03
10 2 1 ruth 2010-02-03
10 4 1 paul 2010-02-03
10 8 1 sam 2010-02-03

The tricky part I have to perform is to copy rows( only t1c2 -> t2c2, t1c3->t2c4 columns) untill I see NA(Not applicable string) in t1c3 to Table2 putting (10 in t2c1, 1 in t2c3, current date in t2c5 columns for all rows).

View 1 Replies

Web Forms :: Accessing Client-side Table Rows On Server?

Jul 23, 2010

I have a table that the user can add rows to through javascript by clicking on a "add rows button". In each row there are two columns one with a dropdown list and the other a text box. The dropdown list is populated with data through a sub on server side code.I need to be able to insert the data from each row into a table but I'm unable to do this. I clicked on my "add rows button" to add several rows and then did a test to see how many rows the table was returning on the server side and it did not include the five rows I added .. only the header row.If there is a better way to allow users to dynamically add rows to a table and then access the dynamically created table on the server then I'm all ears!

View 3 Replies

Custom Server Controls :: FilterExpression Not Working With Other Filters

Dec 21, 2010

I have a radgrid with multiple filters including DateTimePicker for From DateTime To DateTime.

[Code]....

View 2 Replies

SQL Server :: Insert Large No Of Excel Sheet Rows Into Database Table?

Aug 17, 2010

I have an excel sheet that contains around 30,000 rows and 18 colmns. These no of rows and columns may increase in future. I need to read all these records from excel sheet and insert into a table in sql database. For reading the excel book I am using Oledbconnections. The possible solutions I have known as per my knowledge, to insert the data are

1. To insert one record at a time which makes 30,000 database hits. How will this affect the performance?
2. To use liked servers - bu this is not working for me.I do not have database permissions to use linked servers. So, the only option i have is the first one.

View 2 Replies

SQL Server :: XML With PrimaryKey Values To Delete Rows In Database Table At A Time?

Oct 25, 2010

I'm having a gridview in my aspx page with checkbox, User can select one or more than one record(s) at a time and clicks on delete button, Then i'm constructing an object of my DTO and adding all the checked row's Primary Key (DataKeyNames - GUID's in case) to a serializable class and generating an XML and sending it as parameter to the Stored Procedure.

My Doubt is, can i bulk delete all the rows from database table those are present in my XML document??

If it works, I can do bulk update with XML only, i already did bulk insertion by generating an XML..

My Another Question is, Will it affect the performance? Each time when i construct an DTO object and adding it to the Generic List??

WHich one is better, I mean ObjectDataSource or SQLDataSource or my XML method?

My XML will be like this

<root>
<Customer>
<CustomerID>GUID1</CustomerID>
<CustomerID>GUID2</CustomerID>
<CustomerID>GUID3</CustomerID>.....

View 4 Replies

Forms Data Controls :: Filter A Xml Document And Only Get The Rows ?

Jan 15, 2010

How do i filter an xml document and only get the rows I need. example in VB.NET with xml file and aspx page.

I want the filter rows binded to a gridview control.

View 2 Replies

Data Controls :: Filter GridView Rows Alphabetically

Apr 28, 2014

LinkButton A
LinkButton B

A          B           C         D          E

-----------------------------------------

EmpID       EmpName   

1              Aakash
2              Ajit

View 1 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved