error in fetching data

hello i'm using visual studio 2005 and asp.net 2 i have an error in the code that fetching data from databse in grid here is the code :

Dim con As SqlConnection
con = New SqlConnection("Data Source=local;AttachDbFilename='D:\New Folder\horus\horus.mdf';Integrated Security=True")
Dim cmd As New String("SELECT TOP (10) serial, name, gender, dateofbirth FROM(dbo.students)ORDER BY RAND(CONVERT(varbinary(4), NEWID()))")
Dim cd As SqlDataAdapter
cd = New SqlDataAdapter(cmd, con)
Dim ds As New DataSet()
cd.Fill(ds, "students")
grid.DataSource = ds.Tables("students").DefaultView
grid.DataBind()

that code is selecting random records from the database and showing it in grid the error is in the cd.fill(ds,"students") line so can any one help me in that problem.thanksAre you sure your query (SQL) statement is valid? I've never seen RAND() specified in ORDER BY clause before. ORDER BY clause usually requires a list of column names. Did you try running the query in MS-SQL directly to see if it works?|||

No, the SQL statement isn't valid, there must be some kind of language barrier here because I've pointed that out twice before, but not because of the RAND(). You can't put your table names in parenthesis like that.

Wrong:

Dim cmd As New String("SELECT TOP (10) serial, name, gender, dateofbirth FROM(dbo.students)ORDER BY RAND(CONVERT(varbinary(4), NEWID()))")

Right:

Dim cmd As New String("SELECT TOP (10) serial, name, gender, dateofbirth FROM dbo.students ORDER BY RAND(CONVERT(varbinary(4), NEWID()))")

|||hello Motley that code is not showing anything as i said in the other topic, anyway i have another code that serch for name and also not working hope u tell me what is the error in that line :
("SELECT tserial,name,subjectname,stage,class FROM teachers WHERE name =' &TextBox1.Text'&", con)

thanks|||

You should change to use parameterized queries in order to avoid SQL Injection attacks.

This will "fix" your problem, but leave a huge security hole in your application:

("SELECT tserial,name,subjectname,stage,class FROM teachers WHERE name='" &TextBox1.Text&"'", con)

Note that that is name, equals, single quote, double quote, ampersand, Textbox1.text, ampersand, double quote, single quote, double quote.

|||hello, ok that code is ok i have another code problem i have two dropdownlist the first one has the name of the tables in my database the second one has a value in column in all these tables the problem is the server cant get the table name from the dropdownlist here is the code :
Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\mydb.mdf;Integrated Security=True;User Instance=True")
Dim com As New SqlDataAdapter("SELECT * FROM " & ddl1.SelectedValue & "WHERE class = " & ddl2.SelectedValue, con)
Dim ds As New DataSet()
com.Fill(ds, ddl1.SelectedValue)
grid.DataSource = ds.Tables(ddl1.SelectedValue).DefaultView
grid.DataBind()

the table name is in the value of the dropdownlist by the way this a periods tables for a school and every stage has three classes so the column class is in all the tables, hope u can help thanks.|||no one can help?!!! i'm close to finish the site hope u can help.|||Try assigning the SELECT statement into a string variable to verify whether or not there is any syntax error. Also, try out the hard-coded string to make sure the SQL works in the first place. Finally, unless your "class" column is an integer type, you should quote the value using single quotes.|||hello jcasp i tried what u told me the sql statement is alright but the problem is in that dropdown list i typed its value as the name of the table but it doesnt work i tried to put single quotes in that line "WHERE class = " & 'ddl2.SelectedValue', con) i put ddl2.selectedvalue between the single quotes but it put a blue line under the & so hope u can help and by the way the class is not integer it's varchar, hope u can help me soon i'm really close to do it.|||hey guys i just need a little help to make it, i'm waiting ur replies.|||To enclose the value in single quotes, the syntax should be:

"WHERE class ='" & ddl2.SelectedValue & "'", con)
|||

Bad design. Move all the class tables into a single table with a column that has a class name column. Then do this:

Dim conn as new SqlCommand("{Connecting String here}")
conn.open
Dim cmd as new SqlCommand("SELECT * FROM MyClassTable WHEREClassName=@.ClassName AND Class=@.Class",conn)
cmd.parameters.add("@.ClassName",sqldbtype.varchar).Value=ddl1.Selectedvalue
cmd.parameters.add("@.Class",sqldbtype.varchar).Value=ddl2.SelectedValue
dim ds as new DataSet()
dim com as new sqldataadapter(cmd)
com.fill(ds,"MyClassTable")
grid.Datasource=ds.Tables(0).DefaultView
grid.Databind

|||hello Motley i guess i will do as u said and make them all in one table but i will do it with the datasource i just have a last question i want to add my tables to the aspnetdb the default database in visual studio so can u tell me how to do that thanks for ur endless help|||up!!!|||I have no magic way of making it easy. I use Management Studio, and it's not easy -- I run the export/import wizard to move as much as it will, then go back and apply my indexes, trigger, primary keys, etc.