locked
Dynamic data column not returning correct information RRS feed

  • Question

  • User396067491 posted

     Hi,

    I'm attempting to return some values from the database by passing an ID to a grid who's column is generated on the build; the grid has a different sqldatasource that the column that's populated dynamically but they are linked by an ID.  Although I have the ID getting to the column which is generated for some reason the result that's returned is not the ID that is getting passed to it, it always returns the first row in the data table, not the row which contains the ID that was passed.

    Below is by codebehind for the creation of the column:

    Private Function FindImage(ByVal id As String) As Byte()
            Dim ds As New SqlDataSource()
            'ds.DataBind()
            ds.SelectCommand = "select Photo from [Employees] where employeeid=" & id
    
            Dim view As DataView = CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView)
            If view.Count > 0 Then
                Return TryCast(view(0)(0), Byte())
            End If
            Return Nothing
        End Function
    My aspx page has the follow:
    Dynamic grid colum:
    
    <dxwgv:GridViewDataColumn Caption="Details" VisibleIndex="8" Width="5%">
                            <DataItemTemplate>
                                <a href="javascript:void(0);" onclick="OnMoreInfoClick(this, '<%# Container.KeyValue %>')">More Info...</a>
                            </DataItemTemplate>
                        </dxwgv:GridViewDataColumn>
    
    Datasource for Dynamic grid column:
    
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                ConnectionString="<%$ ConnectionStrings:employeeConnectionString %>"           
                SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Photo], [Notes] FROM [Employees]">
            </asp:SqlDataSource>
    
    


     

    Tuesday, June 30, 2009 2:16 PM

Answers

  • User269257549 posted

    The type returned is not Byte it is an array of bytes, i.e. Byte(). That is how the IMAGE type is returned from SQL Server.


    You are going to have to render this array to the browser, most likely by enlisting the help of a handler. Here are a couple of examples of how to do this:

    http://support.microsoft.com/kb/326502 - simply displays the image in the browser

    http://www.aspdotnetcodes.com/Insert_Images_Database.aspx - example of how to render the image in a grid view. I think this is more likely what you want.


    If you have any more questions, post them here.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 30, 2009 6:51 PM

All replies

  • User269257549 posted

    I'm trying to follo as best I can from your description. What I think you are experiencing is that your Return TryCast(view(0)(0), Byte()) is always returning the first column of the first row. It happens to be the first EmployeeID in the Employees table because that was the first column specified in SqlDataSource2's Select statement. This statement should have a WHERE clause associated with it to return the appropriate row from the database.

    When using a DataView as you have, for instance view(r)(c), the first number, r, specifies the record index and the second, c, specifies the column index. In your usage, you are returning the first EmployeeID form the Employees table. You have c right, but you need to get r right. The trouble is you don't know the column that r is in. There are several solutions to your problem:

    1. Iterate over all the rows in view as in

    For counter As Integer = 0 To view.Count - 1

    If view(0)(counter) = id Then

    Return TryCast(view(0)(counter), Byte())

    End If

    Next


    2. Use the RowFilter expression on view as in:

    view.RowFilter = "EmployeeID = " & id.ToString()


    3. Change SqlDataSource2 to include a collection of SelectParameters and set that parameter in the code behind before the select statement is executed as in:

    <asp:SqlDataSource ID="SqlDataSource2" ... SelectCommand="SELECT ... FROM [Employees] WHERE [EmployeeID] = @employeeID">

    <SelectParameters>

    <asp:Parameter Name="employeeID" Type="Int32" />
    </SelectParameters>

    <asp:Parameter Name="employeeID" Type="Int32" />

    </SelectParameters>

    </asp:SqlDataSource>

    and in the code-behind before you execute the select insert:

    SqlDataSource2.SelectParameters(0).DefaultValue = id

    And change the select to:

    SqlDataSource2.Select(New DataSourceSelectArguments())


    Any of these solutions should return the correct row into view(0)(0).

    I think that should resolve the problem for you. If not, plesae post more and we'll keep working on it.

    Tuesday, June 30, 2009 3:16 PM
  • User396067491 posted

     Thanks very much for the detailed response, it's much apprecited.  So I went through each option and tried them to see what would work, I'm not sure i have the code exactly right in them but I'll show you want I did and the result.

    Option 1:

    Fails with an error in the DataView line of code:  Line 1: Incorrect syntax near '='.

       

        Private Function FindImage(ByVal id As String) As Byte()
            Dim ds As New SqlDataSource()
            'ds.DataBind()
            ds.SelectCommand = "select Photo from [Employees] where employeeid=" & id
    
            Dim view As DataView = CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView)
    
            For counter As Integer = 0 To view.Count - 1
                If view(0)(counter) = id Then
                    Return TryCast(view(0)(counter), Byte())
                End If
            Next
            Return Nothing
        End Function


     

    Option 2:

    The page compiles and runs but still returns the data from the first row.

      Private Function FindImage(ByVal id As String) As Byte()
            Dim ds As New SqlDataSource()
            'ds.DataBind()
            ds.SelectCommand = "select Photo from [Employees] where employeeid=" & id
            Dim view As DataView = CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView)
            If view.Count > 0 Then
                view.RowFilter = "EmployeeID = " & id.ToString()
                Return TryCast(view(0)("Photo"), Byte())
            End If
                Return Nothing
        End Function

    Option 3:

    Fails due to the 'New DataSourceSelectArguments' which causing the dataview to be a null object.

    Aspx page:
    
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                ConnectionString="<%$ ConnectionStrings:myConnectionString %>"           
                SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Photo], [Notes] FROM [Employees]= @employeeID">
                 <SelectParameters>
                    <asp:Parameter Name="employeeID" Type="Int32" />
                 </SelectParameters>
            </asp:SqlDataSource>
    		
    Code Behind:
    
    Private Function FindImage(ByVal id As String) As Byte()
            Dim ds As New SqlDataSource()
    
            ds.SelectCommand = SqlDataSource2.SelectParameters(0).DefaultValue = id
    
            Dim view As DataView = CType(SqlDataSource2.Select(New DataSourceSelectArguments()), DataView)
            If view.Count > 0 Then
                Return TryCast(view(0)("Photo"), Byte())
            End If
                Return Nothing
        End Function
     
    Tuesday, June 30, 2009 4:34 PM
  • User396067491 posted

     I had also tried to do this which I think would work with a datafile setup but I can't use that method.  If I try and do it this method it doesn't know what the connection string is for ds and fails.

      Private Function FindImage(ByVal id As String) As Byte()
            Dim ds As New SqlDataSource()
           ds.SelectCommand = "select Photo from [Employees] where employeeid=" & id

            Dim view As DataView = CType(ds.Select(DataSourceSelectArguments.Empty), DataView)

            If view.Count > 0 Then
                Return TryCast(view(0)("Photo"), Byte())
            End If
                Return Nothing
        End Function

     

    Tuesday, June 30, 2009 4:57 PM
  • User396067491 posted

     In the above example I had to add the connection string which seems to have worked!!!! But... the returned value is not a byte.  It returns as an array for some reason so the image thats stored in the database does not show up.  I have checked the database and it's an image field and the below code is defined as a Byte so I don't know what's wrong.

    I had to add this line:

    Public Function FindImage(ByVal id As String) As Byte()
            Dim ds As SqlDataSource = SqlDataSource2
            ds.SelectCommand = "select Photo from [Employees] where employeeid=" & id
    
            Dim view As DataView = CType(ds.Select(DataSourceSelectArguments.Empty), DataView)
    
            If view.Count > 0 Then
                Return TryCast(view(0)("Photo"), Byte())
            End If
            Return Nothing
        End Function


     

     

    Tuesday, June 30, 2009 5:34 PM
  • User269257549 posted

    The type returned is not Byte it is an array of bytes, i.e. Byte(). That is how the IMAGE type is returned from SQL Server.


    You are going to have to render this array to the browser, most likely by enlisting the help of a handler. Here are a couple of examples of how to do this:

    http://support.microsoft.com/kb/326502 - simply displays the image in the browser

    http://www.aspdotnetcodes.com/Insert_Images_Database.aspx - example of how to render the image in a grid view. I think this is more likely what you want.


    If you have any more questions, post them here.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 30, 2009 6:51 PM
  • User269257549 posted

    Just for clarity if anyone wanted to try the other options...

    Option 1 should have been view(0)(counter).ToString = id. I missed the type you were passing in to FindImage.

    In Option 2 needs to have RowStateFilter set before the return happens.

    Tuesday, June 30, 2009 6:55 PM
  • User396067491 posted

     Well, I'm continuing to battle with this.  I have the correct records and array being returned... sort of.  My images didn't display because apparently using a SQLdatasource to return the image data results in an OLE header being created as part of the array for the image and as such the image did not display.  I have found some examples the same issue and have implemented their method to correct the problem but now my image quality has gone down considerable.  My images look terrible, can anyone tell me why and how to correct the problem?

    Below is my vb code:

    Private Function FindImage(ByVal id As String) As Byte()
            Dim ds As New SqlDataSource()
            ds.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString
            ds.SelectCommand = "select Photo from [Employees] where employeeid=" & id
            Dim view As DataView = CType(ds.Select(DataSourceSelectArguments.Empty), DataView)
            If view.Count > 0 Then
                Dim data As Byte() = TryCast(view(0)(0), Byte())
                Dim result As Byte() = New Byte(data.Length - 79) {}
                Array.Copy(data, 78, result, 0, data.Length - 78)
                Return result
            End If
            Return Nothing
        End Function


     

     


     

     

     

    Tuesday, July 14, 2009 11:38 AM
  • User396067491 posted

     Never mind, the images are poor quality to begin with.  Embarassed

    Wednesday, July 15, 2009 1:29 PM
  • User694195886 posted

    I have a grid in 1st page and one link in it.If i click that link it should open a popup which has a grid and it should bind data according to the row information.In my grid its displaying columns evrything in popup but data is not displayed.


    Thanks

    Thursday, January 20, 2011 7:12 AM
  • User694195886 posted

    Hi

    I have a aspxcombobox, It has so many items with different lengths. If i select smaller item, combobox width is minimum but if i select an item with more length, the combobox width is increasing to fit the item in it. Can u please suggest any propery for aspxcombobox width to set it constant even if i select any item of any length.

    Thank You. 

    Monday, January 31, 2011 12:20 AM
  • User694195886 posted

    Hi

    I have a aspxcombobox, It has so many items with different lengths. If i select smaller item, combobox width is minimum but if i select an item with more length, the combobox width is increasing to fit the item in it. Can u please suggest any propery for aspxcombobox width to set it constant even if i select any item of any length.

    Thank You. 

     

    Monday, January 31, 2011 12:21 AM