I'm doing a sample of an image stored in a SQL Server database but as a search, I explain to you to show you have to search by a date and an office. And when it finds it is shown. The truth if I get to leave but when in the search is not the image I get an error like this:
Unable to convert an object of type 'System.DBNull' to type 'System.Byte []'.
This is my current code:
<form id="form1" runat="server">
<cc1:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server" EnableScriptGlobalization="true">
</cc1:ToolkitScriptManager>
<table>
<tr>
<td> Seleccione la Fecha :</td>
<td>
<asp:TextBox ID="txtFecha" runat="server"></asp:TextBox> </td>
<cc1:CalendarExtender ID="CalendarExtender1" PopupButtonID="txtFecha" runat="server" TargetControlID="txtFecha" Format="yyyy/MM/dd"> </cc1:CalendarExtender>
</tr>
<tr>
<td> Seleccionar la Zona : </td>
<td> <asp:DropDownList ID="dprAgencia" class="form-control" runat="server" style="width: 226px;" OnSelectedIndexChanged="dprAgencia_SelectedIndexChanged"></asp:DropDownList></td>
</tr>
<tr>
<td>
<asp:Button ID="Button1" runat="server" Text="Buscar" OnClick="Button1_Click1" /> </td>
</tr>
<tr>
<td> <asp:Button ID="Button2" runat="server" Text="Limpiar" OnClick="Button2_Click1" Visible="false" /> </td>
</tr>
</table>
<div>
<asp:ListView ID="ListImagenes" runat="server" Visible="true">
<LayoutTemplate>
<table id="Table2" runat="server" class="table-responsive">
<tr id="Tr1" runat="server">
<td id="Td1" runat="server">
<table id="itemPlaceholderContainer" runat="server" class="table table-bordered table-hover table-condensed table-responsive" border="3" style="background-color: #FFFFFF; border-collapse: collapse; border-color: #999999; border-style: none; border-width: 1px; font-family: Verdana, Arial, Helvetica, sans-serif;">
<tr id="Tr2" runat="server">
<th id="Th3" runat="server" style="text-align: center;">Rubro</th>
<th id="Th1" runat="server" style="text-align: center;">Usuario Registro</th>
<th id="Th2" runat="server" style="text-align: center;">Recibo</th>
</tr>
<tr id="itemPlaceholder" runat="server">
</tr>
</table>
</td>
</tr>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr class="success">
<td>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("tarea") %>'></asp:Label>
</td>
<td>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("UsuarioAtencion") %>'></asp:Label>
</td>
<td>
<asp:Image ID="Image1" ImageUrl='<%# "data:image/jpg;base64," + Convert.ToBase64String((byte[])Eval("filecontent")) %>' runat="server" Style="width: 150px; height: 150px" data-container="body" data-toggle="popover" data-placement="right" />
<script>
$(document).ready(function () {
$('<%# "#ListImagenes_Image1_" + Container.DisplayIndex %>').popover({
html: true,
trigger: 'hover',
content: '<img src="' + '<%# "data:image/jpg;base64," + Convert.ToBase64String((byte[])Eval("filecontent")) %>' + '" id="popover_img" Style="width: 500px" />'
}).hover(function () {
$('#popover_img').attr('src', $(this).data('img'));
});
$('<%# "#ListImagenes_Image1_" + Container.DisplayIndex %>').mouseover(function () {
$('<%# "#ListImagenes_Image1_" + Container.DisplayIndex %>').popover('show')
});
$('<%# "#ListImagenes_Image1_" + Container.DisplayIndex %>').mouseout(function () {
$('<%# "#ListImagenes_Image1_" + Container.DisplayIndex %>').popover('hide')
});
});
</script>
</td>
</tr>
</ItemTemplate>
</asp:ListView>
</div>
</form>
This is my code in code behind :
protected void Button1_Click1(object sender, EventArgs e)
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlConnection connection = new SqlConnection(ObtenerCadenaConexion());
SqlCommand sqlCommand;
DataTable dt;
dt = new DataTable();
try
{
connection.Open();
sqlCommand = new SqlCommand("Sp_MostrarImagen", connection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@fecha", SqlDbType.Date);
sqlCommand.Parameters.Add("@oficina", SqlDbType.Int);
sqlCommand.Parameters["@fecha"].Value = txtFecha.Text;
sqlCommand.Parameters["@oficina"].Value = dprAgencia.SelectedValue;
adapter.SelectCommand = sqlCommand;
adapter.Fill(dt);
connection.Close();
connection.Dispose();
ListImagenes.DataSource = SelectFotoPrueba();
ListImagenes.DataBind();
ListImagenes.Visible = true;
connection = null;
}
catch
{
}
finally
{
dt.Dispose();
Button1.Visible = false;
Button2.Visible = true;
}
}
This is my code in the stored procedure:
ALTER PROCEDURE [dbo].[Sp_MostrarImagen] @fecha DATE
,@oficina INT
AS
BEGIN
--select idatenciontarea,filename,filecontent,active from TTareasAtender
SELECT tarea
,idatenciontarea
,UsuarioAtencion
,filename
,filecontent
,active
FROM TTareasAtender
WHERE year(FechacumplimientoOriginal) = year(@fecha)
AND MONTH(FechacumplimientoOriginal) = MONTH(@fecha)
AND day(FechacumplimientoOriginal) = day(@fecha)
AND oficina = @oficina
ORDER BY 1 ASC
END
In the SelectedPhotoTest
public DataTable SelectFotoPrueba()
{
SqlDataAdapter adapter = new SqlDataAdapter();
SqlConnection connection = new SqlConnection(ObtenerCadenaConexion());
SqlCommand sqlCommand;
DataTable dt;
dt = new DataTable();
try
{
connection.Open();
sqlCommand = new SqlCommand("ImagenDaniel", connection);
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@opt", SqlDbType.Int);
sqlCommand.Parameters["@opt"].Value = 1;
adapter.SelectCommand = sqlCommand;
adapter.Fill(dt);
connection.Close();
connection.Dispose();
connection = null;
return dt;
}
catch
{
return null;
}
finally
{
dt.Dispose();
}
}