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
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">
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.
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
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
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
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:
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
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.
frost
Member
19 Points
112 Posts
Dynamic data column not returning correct information
Jun 30, 2009 06:16 PM|LINK
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 FunctionDynamic 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>csgeyer@hotm...
Member
222 Points
36 Posts
Re: Dynamic data column not returning correct information
Jun 30, 2009 07:16 PM|LINK
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>
<div style="position: absolute; left: -10000px; top: 164px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> <asp:Parameter Name="employeeID" Type="Int32" /></div> <div style="position: absolute; left: -10000px; top: 164px; width: 1px; height: 1px; overflow-x: hidden; overflow-y: hidden;" id="_mcePaste"> </SelectParameters></div><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.
frost
Member
19 Points
112 Posts
Re: Dynamic data column not returning correct information
Jun 30, 2009 08:34 PM|LINK
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 FunctionOption 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 FunctionOption 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 Functionfrost
Member
19 Points
112 Posts
Re: Dynamic data column not returning correct information
Jun 30, 2009 08:57 PM|LINK
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
frost
Member
19 Points
112 Posts
Re: Dynamic data column not returning correct information
Jun 30, 2009 09:34 PM|LINK
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 Functioncsgeyer@hotm...
Member
222 Points
36 Posts
Re: Dynamic data column not returning correct information
Jun 30, 2009 10:51 PM|LINK
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.
csgeyer@hotm...
Member
222 Points
36 Posts
Re: Dynamic data column not returning correct information
Jun 30, 2009 10:55 PM|LINK
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.
frost
Member
19 Points
112 Posts
Re: Dynamic data column not returning correct information
Jul 14, 2009 03:38 PM|LINK
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 Functionfrost
Member
19 Points
112 Posts
Re: Dynamic data column not returning correct information
Jul 15, 2009 05:29 PM|LINK
Never mind, the images are poor quality to begin with.
n.mounika
Member
8 Points
8 Posts
Not binding row data to grid
Jan 20, 2011 11:12 AM|LINK
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