|
Hey Guys I know this question has probably been asked a thousand times on this forumm, but I had a look and been searching on the net for an answer, and no one answer suits my situation.
I have a Stored proc that handles paging on the server side
everything works fine, until I get to the dynamic sorting.
[code]SET NOCOUNT ON;
Declare @StartIndex int;
Set @PropCount=(SELECT COUNT(property_id ) FROM [dbo].[vwResidentialProperty]
WHERE (@Area is null or parentid = @Area)
and (@locality is null or CharIndex( [dbo].[vwResidentialProperty].locid, @Locality)>0)
and askprice between isnull(@MinPrice,0) and isnull(@MaxPrice ,5000000)
and bedrooms between isnull(@MinBed,0) and isnull(@MaxBed ,10)
and (@PropType is null or charindex( [dbo].[vwResidentialProperty].propertytype,@proptype)>0)
and floorarea between isnull(@MinFloor,0) and isnull(@MaxFloor,5000000));
/*Check to see if the Number of Properties returned is less than actual page size if it is then set the start Index
to the Property Count */
if @PropCount < @Numrow
begin
set @StartIndex = 1;
end;
else
begin
set @StartIndex = (@PageIndex * @Numrow) + 1;
end;
with Property As
(
SELECT top 100 percent
case @SortOrder
when 0 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].askprice Asc)
when 1 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].askprice desc)
when 2 then ROW_NUMBER() OVER (order by [dbo].[vwResidentialProperty].dateadded desc)
end as row,
[dbo].[vwResidentialProperty].property_id , [dbo].[vwResidentialProperty].Address, [dbo].[vwResidentialProperty].askprice
, [dbo].[vwResidentialProperty].dateadded, [dbo].[vwResidentialProperty].Saleterm ,[dbo].[vwResidentialProperty].locid,
[dbo].[property].adbrief
FROM [dbo].[vwResidentialProperty]
inner join [dbo].[property] on [dbo].[property].property_id = [dbo].[vwResidentialProperty].property_id
WHERE (@Area is null or parentid = @Area)
and (@locality is null or CharIndex( [dbo].[vwResidentialProperty].locid, @Locality)>0)
and [dbo].[vwResidentialProperty].askprice between isnull(@MinPrice,0) and isnull(@MaxPrice ,5000000)
and [dbo].[vwResidentialProperty].bedrooms between isnull(@MinBed,0) and isnull(@MaxBed ,10)
and (@PropType is null or charindex( [dbo].[vwResidentialProperty].propertytype,@proptype)>0)
and [dbo].[vwResidentialProperty].floorarea between isnull(@MinFloor,0) and isnull(@MaxFloor,5000000)
)
Select Property_ID, Address as 'Address', askprice as 'Price', adbrief as 'Description', Saleterm, dateadded
FROM Property
WHERE Row BETWEEN
@Startindex and (@startindex + @Numrow )-1
order by case @SortOrder
when 0 then askprice
when 1 then askprice -1
--when 2 then dateadded -1
end;[/code]
I need to be able to provide 3 different sorting options to the user.
1 . Price Asc
2. Price Desc
3. Date added Asc
Now I can't find away of providing this criteria.
At the moment the options are available to the user on screen via option buttons. I don't really want supply SQL code in the HTML of the page i.e
I would prefer to pass an integer value through to the stored proc, and let the case clause handle it.
I have tried puttin Price Asc in the case clause, but SQL complains.
is there any other way of doing this?
|
|
|
|
|
Hi
The following example shows how to sort using case .. when clause:
Use Northwind
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Spe_Invoices_Sort]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Spe_Invoices_Sort]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].Spe_Invoices_Sort
@SortColumn nvarchar(25),
@SortDirn nvarchar(25)
AS
BEGIN
SELECT * FROM Invoices
ORDER BY
CASE WHEN @SortColumn = 'ShipCity' AND @SortDirn = 'DESC' THEN ShipCity END DESC,
CASE WHEN @SortColumn = 'ShipCity' AND @SortDirn = 'ASC' THEN ShipCity END ASC
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Excute this SP in Northwind database. Try this
exec Spe_Invoices_Sort 'ShipCity','asc
Replace ASC or DESC as 1 or 2 as your wish
Hope this helps your problem
Harini
|
|
|
|
|
hello to all,
i have developed a application using vb.net and sql server 2000. Everything is fine but now i have to install it on client's machine.
should i install sql server in that machine?
In other words " Is it necessary to install sql server on client machine , if application use sql server as back end." if not please tell me solution.
with regards ,
sachin jangir
|
|
|
|
|
dotnethunk wrote: should i install sql server in that machine?
If your application is using the same system as database server you have to. If not you can specify remote server address where SQL server resides.
|
|
|
|
|
Thanx for reply ,
But i want that my client have to click on Setup Icon & software is ready to run like when we use Access as database.Is there any way to get such functionality .If Yes please tell me .
With regards ,
Sachin jangir
|
|
|
|
|
dotnethunk wrote: But i want that my client have to click on Setup Icon & software is ready to run like when we use Access as database.Is there any way to get such functionality
Yes You can.... Download install creator. Using this you can create packages. Package your application along with sql server setup. In install maker you can configure the setup to wait untill the installation of SQL server finishes before starting installing your program. After that Use SQL scripts to create database and tables.
|
|
|
|
|
Sir,
Can we update or insert into a table by updating or inserting a view on that table. If we can, then how and if we cant then whats the remedy for that and reason for that. Thank you.
Regards,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
yes we can update table thru views if the view is not readonly
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
|
YES. (in continuation to sylvesterg)
It is possible, if view contains all not null / foreign key columns of base table.
View is like a filter/window by which data in a tbale is viewed.
Regards
KP
|
|
|
|
|
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
Hi
I need to implement search facility. How can I do that ? How can I make a search index table ? How google is maintaining their search index ? Will they store all contents from the pages what they crawled ?
|
|
|
|
|
Hi
You can use a SELECT statement that it contions a CONTIONS keyword simply
|
|
|
|
|
give me the what is locks....
where we r using....
whick purpose....
give me detailed please....
|
|
|
|
|
|
hello there,i know its not the righ place to ask some query,im cannot access my database in a query pane..i made a database where the username column has value and the password has none for e.g Username:93838383 and password:?..i just want to update the password column inputed by the user
I make the query in the stored procedure but the error said: "Incomplete SET clause..unable to parse query text"
i just want someone to check out the query and pls tell me what wrong or incomplete to my query
UPDATE UserTable
SET ((UserTable.[password] = [@password])
WHERE ((UserTable.username = [@username])
another thing is whenever the user visit the site,when the user type the password,it could not be updated anymore..unless i just want to use "select clause" because password column have now values...
here is the query
SELECT COUNT(*) AS Num_of_User
FROM UserTable
WHERE (((UserTable.username)=[@username]) AND ((UserTable.pasword)=[@password]));
error:unable to parse query text
it is correct query for accessing the values from the database?
Im using a visual web developer IDE, i dont think if im using a sqlserver 2000 or 2005
thanx for helping
|
|
|
|
|
blitz2bleach wrote: UPDATE UserTable
SET ((UserTable.[password] = [@password])
WHERE ((UserTable.username = [@username])
The SET clause has two sets of opening brackets and only one closing.
The same goes for the WHERE clause.
Also, you encapsulate the parameters in square brackets which mean they will be interpreted as column names.
I would have written your SELECT query without the profusion of brackets. You also encapsulated the parameters in square brackets which mean they will be interpreted as column names.
Try this:
SELECT COUNT(*) AS Num_of_User
FROM UserTable
WHERE username=@username AND pasword=@password;
|
|
|
|
|
Hi
Use square brackets if you have spaces in column or table name or if column name is a SQL Keyword
For eg.,
SELECT [ID],[Product Name],Price From [Products of ABC]
Harini
|
|
|
|
|
Hi all
What are the advantages of sqlserver2005 over sqlserver2000?
Thanks in advance
|
|
|
|
|
Better datatypes
“Some have an idea that the reason we in this country discard things so readily is because we have so much. The facts are exactly opposite - the reason we have so much is simply because we discard things so readily. We replace the old in return for something that will serve us better.”--Alfred P. Sloan
|
|
|
|
|
More satisfyingly expensive.
|
|
|
|
|
Hello Friends,
I develop a project and i use mssql 2005(express) and i want to use project in another computer that has not have sql server. (actually i mean i want to use sql server mdf file like access) Is it possible?
If it is, how can it be?
Thanks for your helps?
Best Regards
Eray
|
|
|
|
|
It's kind of possible, but the only way to make it work is if the machine has SQL Server Express installed on it. You simply cannot get around that requirement.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
|
The whole purpose of Sql Server is to have data developement projects move out of the amateurish world of Access. What you do is the following:
The application would go on any machine but you create a connection string
in the app.config that contains all of the connection data.
Now in your connection string you specify some url "\\DevelopmentServer" for example.
Whatever you do -- eliminate all existance of localmachine from your connections!!!
Now on the development machines you modify the hosts file as follows
your machine : DevelopmentServer 127.0.0.1
others machine: DevelopmentServer {your ip address}
The only downside to this approach is that any time your IP is reissued via DHCP you need to have the others update their hosts file. The other possibility is you have your engineers give your machine a fixed address until you can deploy a real database server with a fixed address.
|
|
|
|
|
i have 1 labels, 1 text box and one check box
suppose label contains name,
in textbox user have to enter its age
and checkbox is checkd if he is married.
i have given conneection string using oledb in access sheet
and its storing the values of label, textbox and checkbox succesfully
but nw i have problem when i have many number of labels like thereare more than one name and correspondin textboxes and checkboxes.
when i enter all the values i want them to get store on one button click in my acccess table.
m uanble to implement any logic.
can smbody help.... please tell the stored procedure
u can tell on either acces or SQL server 2000.
thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
