Returning a random row from sql database and displaying in a html table using ASP.NET

Question

Im new to using ASP.NET and would like to know how I can select a random row from a sql database and then display the fields in a html table on a separate page. It is intended that the user can press on button which will retrieve a random movie from the database and then display the movie details in a html table on a new page. I am not sure how to go about this and have been trying to use labels to display the data. Here is a sample of the code so far:

    private SqlConnection conn;
    protected void Page_Load(object sender, EventArgs e)
    {
            ConnectionStringSettings connString = ConfigurationManager.ConnectionStrings ["MovieAppConnectionString1"];
            conn = new SqlConnection(connString.ConnectionString);
    }

    protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            conn.Open();
            string queryString = "SELECT TOP 1 * FROM Movie ORDER BY NEWID()";
            SqlCommand cmd = new SqlCommand(queryString, conn);
            {
                SqlDataReader reader = cmd.ExecuteReader();
                StringBuilder MyStringBuilder = new StringBuilder();
                while (reader.Read())
                {
                    Image2.Text = reader[2].ToString();
                    Label1.Text = reader[1].ToString();
                    Desc.Text = reader[3].ToString();
                    Direc.Text = reader[5].ToString();
                    Strs.Text = reader[7].ToString();
                    Rtime.Text = reader[4].ToString();
                    ImdbRt.Text = reader[8].ToString();
                }
            }
       }
        finally
        {
            conn.Close();
        }
        Server.Transfer("MovieSelected.aspx");

I assume you are using SQL Server since you are using the NEWID() function? — N1tr0
– N1tr0, Commented Jan 30, 2013 at 19:17
And what exactly is your question? Is something wrong with the code? — Gordon Linoff
– Gordon Linoff, Commented Jan 30, 2013 at 19:18
I've done something similar to this with PHP but not ASP. It looks like you have the first part done as far the the query goes. You will need to create an ASP function to build your html table and populate it with the database values retrieved from the select statement. — N1tr0
– N1tr0, Commented Jan 30, 2013 at 19:23
@Gordon Linoff My main problem is I dont know how to display the data I retrieved from the Sql query. I have a html table made in a separate page where I want to display the details of a movie from my database. I have labels in the table rows and Im not sure how I use these and stringbuilder to populate the table. — user2026608
– user2026608, Commented Jan 31, 2013 at 10:07

basarat · Accepted Answer · 2013-01-31 23:21:55Z

1

Change your sql server query from :

SELECT TOP 1 * FROM Movie ORDER BY NEWID()

to

SELECT TOP 1 * FROM Movie ORDER BY RAND()

edited Jan 31, 2013 at 23:21

answered Jan 30, 2013 at 23:08

basarat

278k60 gold badges475 silver badges528 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

Why would you change a SQL Server query to invalid syntax?

BrOSs · Accepted Answer · 2013-02-01 16:13:05Z

In your aspx.cs file:

int iLength = 0; 
int index = 0;

DataTable dt = new DataTable();
dt = SqlComm.SqlDataTable("SELECT * FROM Movie");

object obj = new object();
obj = SqlComm.SqlReturn("SELECT COUNT (yourTargetColumn) FROM yourTable");

if (obj != null)                    
     iLength = Convert.ToInt32(obj); 

string[] stringArray = new string[iLength];
for (index = 0; index < iLength; index++) 
{
     stringArray[index] = (string)dt.Rows[index]["yourTargetColumn"];                   
}
foreach (string strArray in stringArray)
{    
     Label yourLabel = new Label();
     PH.Controls.Add(yourLabel);
}

In your .aspx file:

<asp:PlaceHolder ID="PH" runat="server"></asp:PlaceHolder>

Add a class to your App_Code folder named "SqlComm.cs":

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class SqlComm
{
   static string DatabaseConnectionString = "your connection string";

   public static object SqlReturn(string sql)
    {
        using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);                  
            object result = (object)cmd.ExecuteScalar();
            return result;           

        }
    }

   public static DataTable SqlDataTable(string sql)
   {
       using (SqlConnection conn = new SqlConnection(DatabaseConnectionString))
       {
           SqlCommand cmd = new SqlCommand(sql, conn);
           cmd.Connection.Open();
           DataTable TempTable = new DataTable();
           TempTable.Load(cmd.ExecuteReader());
           return TempTable;
       }
}

Note: Do not forget to add the using System.Data.SqlClient to your code. Also, you just have to customize the SELECT command in order to get the data you want.

Collectives™ on Stack Overflow

Returning a random row from sql database and displaying in a html table using ASP.NET

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related