14 Replies - 6112 Views - Last Post: 28 May 2008 - 12:04 PM Rate Topic: -----

#1 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,221
  • Joined: 07-April 08

using select statement to grab index of item in sql database

Posted 27 May 2008 - 12:36 PM

ok so here is the situation:

I have a Table of countries (238 + Please Select Country) what i need is to be able to get the index of the Please Select Country using a select statement in C#. i've tried getting row num:

SELECT ROW_NUM() FROM dbo.TableName WHERE Country_Code = '0';// 0 is the value set for Please_Select_Country)



any suggestions on the correct command?

Is This A Good Question/Topic? 0
  • +

Replies To: using select statement to grab index of item in sql database

#2 zakary   User is offline

  • D.I.C Regular
  • member icon

Reputation: 27
  • View blog
  • Posts: 427
  • Joined: 15-February 05

Re: using select statement to grab index of item in sql database

Posted 27 May 2008 - 12:56 PM

SELECT ROW_NUM FROM dbo.TableName WHERE Country_Code = '0';//


Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: using select statement to grab index of item in sql database

Posted 27 May 2008 - 02:07 PM

If CountryCode is an INT data type there is no need for the single quotes


SELECT ROW_NUM FROM dbo.TableName WHERE Country_Code = 0


Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,221
  • Joined: 07-April 08

Re: using select statement to grab index of item in sql database

Posted 27 May 2008 - 02:23 PM

View PostPsychoCoder, on 27 May, 2008 - 02:07 PM, said:

If CountryCode is an INT data type there is no need for the single quotes


SELECT ROW_NUM FROM dbo.TableName WHERE Country_Code = 0




ahh, see i knew i was at least on the right track. All the examples i saw of ROW_NUM had it like i do. I'll fix it tomorrow when i get back to work. Thanks guys :)
Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: using select statement to grab index of item in sql database

Posted 27 May 2008 - 02:38 PM

No problem glad we could help. Now tell all your friends about us or I will stalk you until you do :P
Was This Post Helpful? 0
  • +
  • -

#6 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,221
  • Joined: 07-April 08

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 06:05 AM

View PostPsychoCoder, on 27 May, 2008 - 02:38 PM, said:

No problem glad we could help. Now tell all your friends about us or I will stalk you until you do :P


lol well i tell everyone I'm able to. You guys are like my super secret Code ninjas.
Was This Post Helpful? 0
  • +
  • -

#7 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,221
  • Joined: 07-April 08

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 08:16 AM

View Postrgfirefly24, on 28 May, 2008 - 06:05 AM, said:

View PostPsychoCoder, on 27 May, 2008 - 02:38 PM, said:

No problem glad we could help. Now tell all your friends about us or I will stalk you until you do :P


lol well i tell everyone I'm able to. You guys are like my super secret Code ninjas.



now its coming back and yelling at me saying that ROW_NUM is not a valid colum name....... wonder if this is a SQL Server issue

to combat this i just created a PK column named ROW_NUM. Now its wierd that websites would say that you can use ROW_NUM to get the index number of an item in a database, but when used it shot back at me saying it wasn't a valid column name.

This post has been edited by rgfirefly24: 28 May 2008 - 08:38 AM

Was This Post Helpful? 0
  • +
  • -

#8 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 08:25 AM

You're getting that error because that table doesn't contain a column named ROW_NUM
Was This Post Helpful? 0
  • +
  • -

#9 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,221
  • Joined: 07-April 08

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 08:40 AM

View PostPsychoCoder, on 28 May, 2008 - 08:25 AM, said:

You're getting that error because that table doesn't contain a column named ROW_NUM


aye, see edit above :), From what i've read though ROW_NUM() is a t-SQL or SQL function that returns an int value from 0-infinate based on what row it found the data in the WHERE clause.
Was This Post Helpful? 0
  • +
  • -

#10 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7507
  • View blog
  • Posts: 15,558
  • Joined: 16-October 07

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 09:06 AM

What is the primary key of your table? That's presumably what you should be selecting for.
Was This Post Helpful? 0
  • +
  • -

#11 Jayman   User is offline

  • Student of Life
  • member icon

Reputation: 423
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 09:08 AM

View Postrgfirefly24, on 28 May, 2008 - 08:40 AM, said:

aye, see edit above :), From what i've read though ROW_NUM() is a t-SQL or SQL function that returns an int value from 0-infinate based on what row it found the data in the WHERE clause.



Which version of Sql Sever are you using?

This is only available in 2005 and later versions.
Was This Post Helpful? 0
  • +
  • -

#12 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 09:14 AM

ROW_NUM() is a built in function in Oracle, not MSSQL. For MSSQL look into the ROW_NUMBER function in Transact-SQL :)
Was This Post Helpful? 0
  • +
  • -

#13 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,221
  • Joined: 07-April 08

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 11:19 AM

View PostPsychoCoder, on 28 May, 2008 - 09:14 AM, said:

ROW_NUM() is a built in function in Oracle, not MSSQL. For MSSQL look into the ROW_NUMBER function in Transact-SQL :)



answer to all questions:

RUM_NUM is my PK now

Running SQL Server 2005

and DOH. see i learned SQL and T-SQL on oracle 10g never actually messed with MSSQL untill this client
Was This Post Helpful? 0
  • +
  • -

#14 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1663
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 11:36 AM

Oracle uses PL/SQL, MSSQL uses T/SQL, though they are similar they are different languages all together. Glad we could help
Was This Post Helpful? 0
  • +
  • -

#15 rgfirefly24   User is offline

  • D.I.C Lover
  • member icon


Reputation: 473
  • View blog
  • Posts: 2,221
  • Joined: 07-April 08

Re: using select statement to grab index of item in sql database

Posted 28 May 2008 - 12:04 PM

View PostPsychoCoder, on 28 May, 2008 - 11:36 AM, said:

Oracle uses PL/SQL, MSSQL uses T/SQL, though they are similar they are different languages all together. Glad we could help



and now you see how fried my brain is right now >< i am having so many problems with this website that nothing is working right inside my head.

I am having trouble with getting variables to work when set inside a _button_click event and staying set. It keeps reseting to the null value. I'll have to keep working on that more. If i dont get it by 2:30 then i'll post for all you code ninjas
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1