[RESOLVED] [2005] - SQL 2005 Images

Hey All,

I currently have some images stored as binary in SQL 2005. I currently have a datagrid which calls an external page to convert the binary into GIF. The datagrid then resizes the images. At the moment, for each image a new row is created.

What I would like to do is say create a 6 x 6 panel which would display 36 pictures out of the 150 stored. Any suggestions on how I could go about this. I can post my current code if required. I have also tried Response.BinaryWrite but that only displays the 1st Image

Re: [2005] - SQL 2005 Images

You should use a Datalist instead of a datagrid.

datalist1.RepeatColumns = 6

Re: [2005] - SQL 2005 Images

Thanks Besoup, worked like a charm :D. One more question though. I'm trying to implement paging with the datalist using some code I found. When clicking the previous button, I receive the following error message.

Index -1 is either negative or above rows count.

Another problem is that there are 3 page indexes in total (0,1,2), but the next button only increments as far as 1. Below is the code I am using.

VB Code:

<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Drawing.Imaging" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Web.UI.WebControls" %>
<%@ Import Namespace="System.Web.UI.HTMLControls" %>
<%@ Import Namespace="System.Math" %>
 
 
<script runat="server">
    
    Dim pageddata As New PagedDataSource
    
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack() Then
          
            doPaging()
            
        End If
    End Sub
    
    
    Function BindDataGrid() As DataTable
        
        Dim myConnection As SqlConnection = New SqlConnection("Initial Catalog=EmployeeDB;Data Source=;Integrated Security=True;")
        Const strSQL As String = "SELECT EMPLOYEENO, GIVENNAME, SURNAME, PICTURE FROM STAFF;"
                         
                         
        Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, myConnection)
        Dim dstaccounts As New DataSet()
       
        myConnection.Open()
        myDataAdapter.Fill(dstaccounts, "STAFF")
 
        Return dstaccounts.Tables("STAFF").Copy
 
    End Function
    
    Sub doPaging()
        
        pageddata.DataSource = BindDataGrid().DefaultView
        pageddata.AllowPaging = True
        pageddata.PageSize = 1
 
        Try
            pageddata.CurrentPageIndex = Int32.Parse(Request.QueryString("Page")).ToString()
        Catch ex As Exception
            pageddata.CurrentPageIndex = 0
        End Try
 
        btnPrev.Visible = (Not pageddata.IsFirstPage)
        btnNext.Visible = (Not pageddata.IsLastPage)
 
        pageNumber.Text = "Page " & (pageddata.CurrentPageIndex + 1) & " Of Page " & pageddata.PageCount
 
        datagrid.DataSource = pageddata
        datagrid.DataBind()
        
    End Sub
    
    Public Sub Prev_Click(ByVal obj As Object, ByVal e As EventArgs)
        
        'If pageddata.CurrentPageIndex = 0 Then
            
        'Response.Redirect("e.aspx")
            
        'Else
            
        Response.Redirect(Request.CurrentExecutionFilePath & "?Page=" & (pageddata.CurrentPageIndex - 1))
            
        'End If
    End Sub
 
    Public Sub Next_Click(ByVal obj As Object, ByVal e As EventArgs)
        
        Response.Redirect(Request.CurrentExecutionFilePath & "?Page=" & (pageddata.CurrentPageIndex + 1))
    
    End Sub
 
 
 
 
Function FormatURL(strArgument) as String
 
                                    Return ("readrealimage.aspx?id=" & strArgument)
 
End Function
 
</script>
 
 
 
 
<html>
<body>
 
<form action="" runat="server">
<asp:DataList id="datagrid" RepeatColumns="6" RepeatDirection="Horizontal" runat="server">
 
<HeaderTemplate>
Employees
</HeaderTemplate>
 
<ItemTemplate>
<asp:Image ID="Image1" runat="server" ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "EMPLOYEENO")) %>'  Visible ='<%# FormatURL(DataBinder.Eval(Container.DataItem, "EMPLOYEENO")) <> "" %>' />
</ItemTemplate>
 
 
 
<FooterTemplate>
Copyright 2008
</FooterTemplate>
 
</asp:DataList>
 
<asp:Button ID="btnPrev" onclick="Prev_Click" text="Previous" runat="server" />
<asp:Button ID="btnNext" onclick="Next_Click" text="Next" runat="server" />
 
<br />
 
<asp:Label ID="pageNumber"  runat="server" />
 
</form>
 
</body>
</html>

Re: [2005] - SQL 2005 Images

Scrap the last post. Another team member amened the code to include the following. Whether it is the best method or not, thats to debate another day.

VB Code:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not IsPostBack() Then
          
            doPaging()
            
        Else
            Try
                pageddata.CurrentPageIndex = Int32.Parse(Request.QueryString("Page")).ToString()
            Catch ex As Exception
                pageddata.CurrentPageIndex = 0
            End Try
        End If
    End Sub

Re: [RESOLVED] [2005] - SQL 2005 Images

If I were your teammate, I'd change it today :D

You shouldn't use try-catch blocks for functionality. You can use Int32.TryParse to see if it is numeric, that saves on the expensive operation of a catch.