[RESOLVED] Select random field with No index

[mariolopes]

Hi
I have a sqlserver table and a select statment. With the result of the sql stayment i want to select a random register. How can i achive that if i don't have a index field?
Thank you
There is my statment code
Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\novapastavb\novapasta.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")

Dim command As New SqlCommand("SELECT count(login) as reclames FROM login where login=@nome and passe=@passe", connection)
command.Parameters.AddWithValue("@nome", TextBox1.Text)
command.Parameters.AddWithValue("@passe", TextBox2.Text)

connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader

While reader.Read()
MessageBox.Show(String.Format("Existem Muitos clientes Nome {0} ", reader("reclames")))
End While

reader.Close()

connection.Close()

[Hack]

Moved From The CodeBank

[Shaggy Hiker]

I'm not sure what you mean by register. That word can have serveral different definitions, but none of them seem to apply to the example you posted. Not sure of the translation, but that looks like you are querying on name (is that nome?) and password (is that passe?) from a login table. From that, you should get a reader with only a single line, unless the login table is such that there are multiple records with the same name and password, which would be kind of weird. Since you only return a single value for what should be a single line, ExecuteScalar will have superior performance to ExecuteReader, as you don't really need a datareader.

On the other hand, I could be totally misunderstanding, and I probably am.

[mariolopes]

Thanky you Shaggy Hiker for your time and sorry about my english and my poor explanation of the problem.
When i use a select statment and it returns more than one record i want to show (on a label ) not the first record but a random record.
Thank you

[GaryMazzone]

Try something like this

SELECT TOP 1 login as reclames FROM login where login=@nome and passe=@passe ORDER BY NEWID()

If there is more then one return you will get a random one back.


I orgianlly posted this here:
http://www.vbforums.com/showthread.p...27#post3316427

[mariolopes]

Many thanks