How to create jQuery slider from images in a Database?

Question

I am working on an ASP.NET website and can't seem to find a good solution to this. I would like to create a jQuery image slider that cycles through the last 3 images added to my database. I have tried looking at tutorials around the web, but none seem to address pulling only the most recent additions from a database. Any suggestions?

Answer 1

Check out http://docs.dev7studios.com/jquery-plugins/nivo-slider for a good example of a free (MIT Licensed) jQuery plugin that you could use on a company website. In order to get what you want, have ASP.NET echo a list of images in the format the Nivo Slider documentation provides. Example:

Top of page: (comes from http://www.go4expert.com/articles/connecting-mssql-server-aspnet-t2559/)

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

Head:

<!-- The Nivo files can be downloaded from the link I provided above. -->
<link rel="stylesheet" href="nivo-slider.css" type="text/css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js" type="text/javascript"></script>
<script src="jquery.nivo.slider.pack.js" type="text/javascript"></script>
<script type="text/javascript">
$(window).load(function() {
    $('#slider').nivoSlider();
});
</script>

Body:

<div id="slider" class="nivoSlider">
<!-- To connect to a MSSQL db comes from http://www.go4expert.com/articles/connecting-mssql-server-aspnet-t2559/ since I do not have prior knowledge on how to do this with ASP.NET -->
<%
  Dim myDataReader as SqlDataReader
  Dim mySqlConnection as SqlConnection
  Dim mySqlCommand as SqlCommand

  mySqlConnection = new SqlConnection("server=mssql.win-servers.com;user=dbuser;password=dbpwd;database=db")
  mySqlCommand = new SqlCommand("SELECT * FROM pictures ORDER BY id DESC LIMIT 3", mySqlConnection)
  mySqlConnection.Open()
  myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)


  Do While (myDataReader.Read())
    Response.Write('<img src="' & myDataReader.getString(1) & '" alt="" />')
  Loop

  myDataReader.Close()
  mySqlConnection.Close()
%>
</div>

Notice the "SELECT * FROM pictures ORDER BY id DESC LIMIT 3" query. I got that query tip from https://stackoverflow.com/a/15425791 on how to select last 3 rows of a table. Also, if you want to store more information about the image (like captions) then I suggest adding that information to the row of the table where you store the images.

I'm also not sure if myDataReader.getString(1) is working as I intend it to. You'll have to figure out the best way to read from myDataReader.Read().