Database Discussion Boards

Re: how to get record by positioning - SQL 2005

leoinfo25-Jul-08 2:31

leoinfo

25-Jul-08 2:31

Well ... if you are using SQL 2005 ... I think this is what you are looking for Smile | :)

/* TEST DATA */
/****************************/
CREATE TABLE #T (id INT IDENTITY(1,1), aField NVARCHAR(10) ) ; 
INSERT INTO #T (aField) SELECT 'one' ; 
INSERT INTO #T (aField) SELECT 'two' ; 
INSERT INTO #T (aField) SELECT 'three' ; 
INSERT INTO #T (aField) SELECT 'four' ; 
INSERT INTO #T (aField) SELECT 'five' ; 
INSERT INTO #T (aField) SELECT 'six' ; 
INSERT INTO #T (aField) SELECT 'seven' ; 

/* SORTED LIST */
/****************************/
SELECT * FROM #T ORDER BY aField DESC 

/* EXTRACT FROM SORTED LIST */
/****************************/
;WITH myTable AS (
    SELECT 
          ROW_NUMBER() OVER( ORDER BY aField DESC ) AS RowNo
        , id
        , aField
    FROM #T
    )
SELECT * 
FROM myTable 
WHERE RowNo IN ( 2 , 4 ) ; 
/****************************/

DROP TABLE #T ;

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

modified on Friday, July 25, 2008 8:38 AM

Re: how to get record by positioning - SQL 2000

leoinfo25-Jul-08 2:41

leoinfo

25-Jul-08 2:41

This is what I would use in SQL 2000 :

/* TEST DATA */
/****************************/
CREATE TABLE #T (id INT IDENTITY(1,1), aField NVARCHAR(10) ) ; 
INSERT INTO #T (aField) SELECT 'one' ; 
INSERT INTO #T (aField) SELECT 'two' ; 
INSERT INTO #T (aField) SELECT 'three' ; 
INSERT INTO #T (aField) SELECT 'four' ; 
INSERT INTO #T (aField) SELECT 'five' ; 
INSERT INTO #T (aField) SELECT 'six' ; 
INSERT INTO #T (aField) SELECT 'seven' ; 

/* TEST LIST */
/****************************/
SELECT IDENTITY(INT, 1 , 1) AS RowNo, 0+id AS id, aField 
INTO #Z 
FROM #T 
ORDER BY aField DESC 

/* SORTED LIST */
/****************************/
SELECT * FROM #Z

/* EXTRACT FROM SORTED LIST */
/****************************/
SELECT * FROM #Z WHERE RowNo IN ( 2 , 4 ) ;
/* */
DROP TABLE #T ; 
DROP TABLE #Z ;

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Why not use an array ?

David Mujica25-Jul-08 3:18

David Mujica

25-Jul-08 3:18

If you really only have 20 or so records, why wouldn't you load all of the records into an array, then access the Nth element whenever you want. If this is lookup type data you could load this array once and save yourself lots of headaches with complex SQL. I'm a big fan of keeping things simple. Just a thought. Smile | :)

Re: Why not use an array ?

leoinfo25-Jul-08 3:28

leoinfo

25-Jul-08 3:28

David Mujica wrote:
If you really only have 20 or so records

If you have 20 records and you only need 1, why load other 19 records if you know that you'll throw them away anyway ?
What if you have 10000 users loading extra 19 records everytime ?

David Mujica wrote:
save yourself lots of headaches with complex SQL

I cannot agree with this ... I prefer the headaches Smile | :)

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Re: how to get record by positioning

Blue_Boy25-Jul-08 6:17

Blue_Boy

25-Jul-08 6:17

select top 2 rowposition from tablename where rowposition > 1

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Re: how to get record by positioning

Ashfield25-Jul-08 9:19

Ashfield

25-Jul-08 9:19

Regardless of all the fine answers you have been given, why do you need to read all the records if you know you need one of them?

What I mean is, if you know you want the nth record there must be something that makes it the one you want, so why not put that criteria in the original select? Confused | :confused:

Bob
Ashfield Consultants Ltd

Re: how to get record by positioning

Mycroft Holmes25-Jul-08 22:43

Mycroft Holmes

25-Jul-08 22:43

Actually we have this requirement when calculating VAR, get the 500 scenarios, sort by your worst exposure and get 5th worst exposure of 500, rowposition does this.

Never underestimate the power of human stupidity
RAH

connection string

Zafar Sultan24-Jul-08 19:47

Zafar Sultan

24-Jul-08 19:47

Hi Everybody,
I have Visual Studio 2005 and SQL Server 2005 installed on my PC which runs on Windows XP PE.The problem is when I finished the setup of SQL Srver 2005 it was not getting connected using SQL Server Authentication, so I used windows authentication.Now I developed an application it is running on my PC but whenever I am running it using IIS the connection can not be established with SQL Server.What can be wrong? how can i create a user with password on SQL Server? Also if i create this application fully fuctionable on my machine then at the time of deployment will it create any problem on the remote server?

One last point:Can anyone give me a sample connection string for remote server.I know it for my PC only but what parameters i have to give when connecting to a remote database and how?

Re: connection string

Abdul Sami X24-Jul-08 20:30

Abdul Sami X

24-Jul-08 20:30

if u have user id and pwd then use the following:

Data Source=urServerAddress;Initial Catalog=urDataBase;User Id=myUsername;Password=myPassword;

if u r using windows authentication then use the following:

Data Source=urServerAddress;Initial Catalog=urDataBase;Integrated Security=SSPI;

Abdul Sami,
Systems Analyst

Re: connection string

Mycroft Holmes25-Jul-08 23:15

Mycroft Holmes

25-Jul-08 23:15

Clickety[^]

You will need to change the connection types allowed to both integrated & sql standard.
Create an application specific standard identity in SQL server, give it adequate rights to your database (not sa and probably not DBO). We have a script that grants execute on all procs and select on all tables & views.
Use the standard creds in the connection string you decide to use!

Never underestimate the power of human stupidity
RAH

How to install dao db app?

includeh1024-Jul-08 15:17

includeh10

24-Jul-08 15:17

I used CDaoDatabase and its sibling classes in my app, but I can not install the app in customers' computer.
I guess that some dlls or registers are missing, because all are fine if I install VC++ on customers PC.

If I change database calsses from CDaoDatabase to CDatabase, there is no installation problem, so I think all troubles are from dao's issues.

I am using Visual studio 6.0 on Windows XP platform.

I do like dao database because it is much more powerful, but I can not install my app for customers.

how do you install your dao app for customers?

thanks

Re: How to install dao db app?

Wendelius24-Jul-08 18:35

Wendelius

24-Jul-08 18:35

This should give you a starting point:

http://support.microsoft.com/kb/233002[^]

Mika

Re: How to install dao db app?

Mycroft Holmes25-Jul-08 23:19

Mycroft Holmes

25-Jul-08 23:19

I had never heard of CDaoDatabase, however this seem to indicate there are plenty of resources out there to research try.
clickety[^]

Never underestimate the power of human stupidity
RAH

Simple Database Connectivity!!!!

vamseeraja24-Jul-08 5:40

vamseeraja

24-Jul-08 5:40

Hi guys,
I am pretty new to using MS Access data base and its connectivity.I have a webpage which is made up of HTML.This webpage has some fields in it.I have developed this webpage using Dreamweaver MX technology.
As soon as the users type the information and submit the values,I have designed the webpage to be in such a manner that the information is sent to my email.

Now I am trying to develop a way ,where the user values are stored in my MS Access database.Can anyone help me out in this regard.
What actually needs to be done.How can I establish the connectivity.I am pretty much confused as to what connection strings I should use.Should I change the HTML page to .aspx page????Kindly let me know in detail.Thanks a lot.

Regards,
Vamsee.

Re: Simple Database Connectivity!!!!

Vimalsoft(Pty) Ltd24-Jul-08 8:14

Vimalsoft(Pty) Ltd

24-Jul-08 8:14

You are posting in the wrong Forum, you should have used ASP.NET Forum, but now to answer your question you have to write a data layer class that will interact with your page and create a Stored Procedure for insert. So that means when a user clicks Submit the data layer wil take the Parameters passed in your form and pass it in the Stored procedure and your records will be in the Database. like at this article and it can be done in Web too

http://www.codeproject.com/KB/cs/NTier.aspx[^]

Vuyiswa Maseko,

Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding

VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za

Re: Simple Database Connectivity!!!!

vamseeraja25-Jul-08 4:22

vamseeraja

25-Jul-08 4:22

Sorry,
That I have posted in the wrong Place.Your explanation was very good.I will try this and get back to you if I have any doubts.Thanks a lot.

Re: Simple Database Connectivity!!!!

Vimalsoft(Pty) Ltd25-Jul-08 6:04

Vimalsoft(Pty) Ltd

25-Jul-08 6:04

Your are welcome, am glad i helped. Smile | :)

Re: Simple Database Connectivity!!!!

nelsonpaixao24-Jul-08 12:48

nelsonpaixao

24-Jul-08 12:48

hi vamseeraja,

why don´t you became pretty new using SQL Server database, and by the way, became also pretty new using aspx too, instead of html!!! Laugh | :laugh:

Re: Simple Database Connectivity!!!!

vamseeraja25-Jul-08 4:53

vamseeraja

25-Jul-08 4:53

Ya I can do that...
But the client I work for are not Computer geeks.so my Boss who also works along with me wants it to be simple technology.So I thought that.Also she wants the database to be in MS Access.
Thanks a lot for suggesting me.

Select Into Statement Clarity

Vimalsoft(Pty) Ltd24-Jul-08 5:31

Vimalsoft(Pty) Ltd

24-Jul-08 5:31

Good Evening All

I undestand that if i write a Select into statement like this

SELECT * INTO NEW_TABLE FROM OLD_TABLE

This will create a table named New_Table and Copy the Data from the Old table to the New table. Now Every time this kind of statement runs , there should not be a table like New_TABLE? am i right?, am asking this because i have a process that is misbehaving written by a previous developer, if am wrong i think he thought that Select into statement Appends the records if the table New_Table is found.

i just need Clarity

Thanks

Re: Select Into Statement Clarity

Tim Carmichael24-Jul-08 5:44

Tim Carmichael

24-Jul-08 5:44

First: what database is this associated with? Different systems will have different SQL syntax.

Re: Select Into Statement Clarity

Vimalsoft(Pty) Ltd24-Jul-08 8:06

Vimalsoft(Pty) Ltd

24-Jul-08 8:06

I dont Understand your Question. Am talking about SQL

Re: Select Into Statement Clarity

TheFM23424-Jul-08 8:30

TheFM234

24-Jul-08 8:30

You are correct, Select Into syntax does not append.

Re: Select Into Statement Clarity

Vimalsoft(Pty) Ltd24-Jul-08 8:34

Vimalsoft(Pty) Ltd

24-Jul-08 8:34

Thank you , i understand

Re: Select Into Statement Clarity

Ashfield25-Jul-08 9:24

Ashfield

25-Jul-08 9:24

..and it locks the system tables such as syscolumns for the duration of the select, potentially locking out other users - use with care. I prefer

Select * into New_Table from Old_Table where 1 = 2

insert into New_table select * from Old_Table

as this greatly reduces the lock time.

If necessary you could check for the existence of the new table before doing the select into and drop it if required.

Bob
Ashfield Consultants Ltd

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.