Save Image in sql server from vb asp

Question

Save Image in sql server from vb asp

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I would like you to help me, I would like to save the image in my sql server database so I am doing 3 layers programming in visual basic asp Data layer:

Public Class D_Atencion
#Region "Declarar Variables"
    Dim CadCnx As New D_Conexion
    Dim Cnx As SqlConnection
    Dim Cmd As SqlCommand
    Dim da As SqlDataAdapter
    Dim dt As DataTable
#End Region
#Region "Incidencias del Usuario"
    Public Function Obtener_Informa_Atencion(ByVal ivOpt As Integer, ByVal svBuscar As String) As DataSet
        Try
            Return SqlHelper.ExecuteDataset(CadCnx.CnxWebInci, "PE_Ate_InformacionAtencion_New", ivOpt, svBuscar)
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function
    Public Function Proceso_Atencion(ByVal ivOpt As Integer, ByVal svViaIncidencia As String, ByVal svReportadoPor As String, ByVal svAgencia As String, ByVal svTipoIncidencia As String, ByVal svSubtipoIncidencia As String, ByVal svDerivarA As String, ByVal svDescripcion As String, ByVal svUsuarioRegistro As String) As String
        Dim _bol As String = ""

        Cnx = New SqlConnection
        Cnx.ConnectionString = CadCnx.CnxWebInci

        dt = New DataTable
        da = New SqlDataAdapter

        Cmd = New SqlCommand
        Cmd.Connection = Cnx
        Cmd.CommandTimeout = 7000000
        Cmd.CommandType = CommandType.StoredProcedure
        Cmd.CommandText = "PE_Ate_ProcesosAtencion_New"


        Cmd.Parameters.Add(New SqlParameter("@Opt", SqlDbType.Int)).Value = ivOpt
        Cmd.Parameters.Add(New SqlParameter("@ViaIncidencia", SqlDbType.VarChar, 250)).Value = UCase(svViaIncidencia)
        Cmd.Parameters.Add(New SqlParameter("@ReportadoPor", SqlDbType.VarChar, 250)).Value = UCase(svReportadoPor)
        Cmd.Parameters.Add(New SqlParameter("@Agencia", SqlDbType.VarChar, 250)).Value = UCase(svAgencia)
        Cmd.Parameters.Add(New SqlParameter("@TipoIncidencia", SqlDbType.VarChar, 250)).Value = UCase(svTipoIncidencia)
        Cmd.Parameters.Add(New SqlParameter("@SubtipoIncidencia", SqlDbType.VarChar, 250)).Value = UCase(svSubtipoIncidencia)
        Cmd.Parameters.Add(New SqlParameter("@DerivarA", SqlDbType.VarChar, 150)).Value = UCase(svDerivarA)
        Cmd.Parameters.Add(New SqlParameter("@Descripcion", SqlDbType.Text)).Value = UCase(svDescripcion)
        Cmd.Parameters.Add(New SqlParameter("@UsuarioRegistro", SqlDbType.VarChar, 250)).Value = UCase(svUsuarioRegistro)



        Try
            dt.Clear()
            dt.Reset()

            If Cnx.State = ConnectionState.Closed Then Cnx.Open()

            Dim i As Integer = Cmd.ExecuteNonQuery
            Cnx.Close()

            If ivOpt = 2 Then
                _bol = If(i > 0, "Incidencia ha sido Atendida...!", "Proceso no pudo llegar a culminarse...!")

            End If

            If ivOpt = 3 Then
                _bol = If(i > 0, "Atención de la Incidencia ha sido Desistida...!", "Proceso no pudo llegar a culminarse...!")
            End If

            If ivOpt = 10 Then
                _bol = If(i > 0, "Atención de la Incidencia ha sido Derivada...!", "Proceso no pudo llegar a culminarse...!")
            End If

            Return _bol
        Catch ex As Exception
            Throw New Exception(ex.Message)
            Return False
        End Try
    End Function
    Public Function Proceso_Atencion_1(ByVal ivOpt As Integer, ByVal svViaIncidencia As String, ByVal svReportadoPor As String, ByVal svAgencia As String, ByVal svTipoIncidencia As String, ByVal svSubtipoIncidencia As String, ByVal svDerivarA As String, ByVal svDescripcion As String, ByVal svImagen As String, ByVal svUsuarioRegistro As String) As DataSet
        Try
            Return SqlHelper.ExecuteDataset(CadCnx.CnxWebInci, "PE_Ate_ProcesosAtencion_New", ivOpt, UCase(svViaIncidencia), UCase(svReportadoPor), UCase(svAgencia), UCase(svTipoIncidencia), UCase(svSubtipoIncidencia), UCase(svDerivarA), UCase(svDescripcion), UCase(svImagen), UCase(svUsuarioRegistro))
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function
#End Region
End Class

Business layer:

Public Function Proceso_Atencion_1(ByVal ivOpt As Integer, ByVal svViaIncidencia As String, ByVal svReportadoPor As String, ByVal svAgencia As String, ByVal svTipoIncidencia As String, ByVal svSubtipoIncidencia As String, ByVal svDerivarA As String, ByVal svDescripcion As String, ByVal svImagen As String, ByVal svUsuarioRegistro As String) As DataSet
        Try
            Return vD_Atencion.Proceso_Atencion_1(ivOpt, UCase(svViaIncidencia), UCase(svReportadoPor), UCase(svAgencia), UCase(svTipoIncidencia), UCase(svSubtipoIncidencia), UCase(svDerivarA), UCase(svDescripcion), UCase(svImagen), UCase(svUsuarioRegistro))
        Catch ex As Exception
            Throw New Exception(ex.Message)
        End Try
    End Function

Each Presentation:

 Protected Sub BtnRegistrar_Click(sender As Object, e As EventArgs) Handles BtnRegistrar.Click
        Try
            If Not Validar_Registro() Then Exit Sub

            dt = New DataTable
            vB_Atencion = New B_Atencion

            dt = vB_Atencion.Proceso_Atencion_1(1, UCase(CboViaIncidencia.Text.Trim), UCase(TxtUsuario.Text.Trim), UCase(TxtOficina.Text.Trim), UCase(CboTipoIncidencia.Text.Trim), UCase(CboSubTipoAtencion.Text.Trim), IIf(CboDerivar.SelectedIndex = 0, "0", UCase(CboDerivar.Text)), UCase(TxtDescripcion.Text.Trim), UCase(ImagenFile.FileUpload), Session(0)).Tables(0)

            TxtTicket.Text = dt.Rows(0)("NroTicket")

            dt.Dispose()

            Mensaje("Incidencia se registro correctamente...!", "Aviso del Sistema")


            Call Deshabilitar()

            TxtDescripcion.Enabled = False
            BtnRegistrar.Enabled = False

            'If CboDerivar.SelectedIndex = 0 Then
            '    TxtSolucion.Enabled = True
            '    TxtSolucion.Text = ""
            '    TxtSolucion.Focus()
            '    BtnCulminar.Enabled = True
            'End If
            'If CboDerivar.SelectedIndex  0 Then
            '    TxtSolucion.Enabled = False
            '    TxtSolucion.Text = ""
            '    BtnCulminar.Enabled = False
            'End If

        Catch ex As Exception
            Mensaje(Err.Description, "Aviso del Sistema")
            Exit Try
        End Try
    End Sub

T-0001

                </td>
        </tr>

        <tr>
           <th class="auto-style1">
                <asp:Label ID="Label2" runat="server" Font-Bold="True" Text="Nombre de Usuario : "></asp:Label>
           </th>
           <td class="auto-style2">
                <asp:TextBox ID="TxtUsuario" runat="server" placeholder="Nombre de Usuario" style="width:90%" ReadOnly="True"></asp:TextBox>
           </td>
           <th class="auto-style1">
                <asp:Label ID="Label3" runat="server" Font-Bold="True" Text="Nombre de Agencia  :"></asp:Label>
           </th>
           <td class="auto-style2">
                <asp:TextBox ID="TxtOficina" runat="server" placeholder="Nombre de Usuario" style="width:90%" ReadOnly="True"></asp:TextBox>
           </td>
        </tr>
        <tr>
           <th style="width:15%" >
                <asp:Label ID="Label5" runat="server" Font-Bold="True" Text="Tipo de Incidente : "></asp:Label>
           </th>
           <td  style="width:35%">
                <asp:DropDownList ID="CboTipoIncidencia" runat="server" AutoPostBack="True" Font-Names="Arial" Font-Size="8pt" style="width:90%"></asp:DropDownList>
           </td>
           <th  style="width:15%">
                <asp:Label ID="Label6" runat="server" Font-Bold="True" Text="SubTipo de Incidente  :"></asp:Label>
           </th>
           <td  style="width:35%">
                <asp:DropDownList ID="CboSubTipoAtencion" runat="server" Font-Names="Arial" Font-Size="8pt" style="width:90%" Enabled="False"></asp:DropDownList>
           </td>
        </tr>
          <tr>
           <th style="width:15%" >
                <asp:Label ID="Label9" runat="server" Font-Bold="True" Text="Derivar Incidencia  A:"></asp:Label>

           </th>
           <td  style="width:35%">
                <asp:DropDownList ID="CboDerivar" runat="server" AutoPostBack="True" Font-Names="Arial" Font-Size="8pt" style="width:90%" Enabled="False"></asp:DropDownList>
           </td>
           <th  style="width:15%">
                &nbsp;</th>
           <td  style="width:35%">
                &nbsp;</td>
        </tr>
         <tr>
             <th>
                  <asp:Label ID="Label11" runat="server" Font-Bold="True" Text="Subir Archivo:"></asp:Label>
             </th>
             **<td>
                 <input type="file" id="ImagenFile" runat="server"/>
             </td>**
         </tr>

        <tr>
            <th colspan="4">
                <asp:Label ID="Label8" runat="server" Font-Bold="True" Text="Ingresar Detalladamente la incidencia por favor, considerar que mínimo debe de ingresar 20 caracteres:"></asp:Label>
            </th>
        </tr>
        <tr>
            <td colspan="4" style="text-align:center">
                <asp:TextBox ID="TxtDescripcion" runat="server" style="width: 98%; height:120px" TextMode="MultiLine"></asp:TextBox>

                <br />
                <br />
            </td>
        </tr>

    </table>

vb.net asp.net

asked by PieroDev 06.03.2017 в 21:57

source

2 answers

0

You could create a folder in your project called Images and there you store the images you want, in the Database stored only the Route and Name strong> of each image.

   If FileUpload1.HasFile = True Then
                        If FileUpload1.PostedFile IsNot Nothing Then 'Validar que se seleccione un archivo
                            Dim Nombre As String = Path.GetFileName(FileUpload1.PostedFile.FileName) 'Definir el Nombre del archivo Seleccionado
                            FileUpload1.SaveAs(Server.MapPath("~/Imagenes/" & Nombre)) 'Guardar Imagen en carpeta Imagenes con el nombre asignado anteriormente 
                            Dim con As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("TuConexion").ConnectionString) 'Conexion a la DB
                            Dim cmd As New SqlCommand("insert into archivos (ruta_archivo,NOMBRE) values(@ruta,@NOMBRE)") 'Insert a la Tabla                        
                            cmd.Parameters.AddWithValue("@ruta", "~/Archivos/Imagenes/" & Nombre) 'Guardar ruta y nombre de la imagen                         
                            cmd.Parameters.AddWithValue("@NOMBRE", Nombre)'Guardar nombre de la Imagen                         
                            cmd.CommandType = CommandType.Text
                            cmd.Connection = con
                            Try
                                con.Open()
                                cmd.ExecuteNonQuery()
                            Catch ex As Exception
                                Response.Write(ex.Message)
                            Finally
                                con.Close()
                                con.Dispose()
                            End Try
                        End If
                    End If

answered by 12.06.2017 в 15:56

Publish asp.net site [closed] Error trying to enter a date in a "date" column in the database

score 1 · Accepted Answer

According to the title of the question, here is the answer: D

Name of the table: tblFiles

| Campo       | Detalle                            |
|-------------|------------------------------------|
| id          | Identification Number              |
| Name        | Nombre del archivo                 |
| ContentType | Tipo de contenido para el archivo  |
| Data        | Archivo guardado como Binary Data  |

Connection string

<connectionStrings>

<add name="conString" connectionString="Data Source=.\SQLEXPRESS;database=dbFiles; Integrated Security=true"/>

</connectionStrings >

A file.aspx

<asp:FileUpload ID="FileUpload1" runat="server" />

<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />    
<br />    
<asp:Label ID="lblMessage" runat="server" Text="" Font-Names = "Arial"></asp:Label>

We create the method

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

    Dim strConnString As String = System.Configuration.    
    ConfigurationManager.ConnectionStrings("conString").ConnectionString    
    Dim con As New SqlConnection(strConnString)    
    cmd.CommandType = CommandType.Text    
    cmd.Connection = con

    Try

      con.Open()    
      cmd.ExecuteNonQuery()    
      Return True

    Catch ex As Exception

      Response.Write(ex.Message)    
      Return False

    Finally

      con.Close()    
      con.Dispose()

    End Try

End Function

In the click event of the btnUpload button:

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs)

  'Leemos el archivo y lo convertimos a un Byte Array'

  Dim filePath As String = FileUpload1.PostedFile.FileName    
  Dim filename As String = Path.GetFileName(filePath)    
  Dim ext As String = Path.GetExtension(filename)    
  Dim contenttype As String = String.Empty     

  'Definimos el tipo de contenido basado en la extension'

  Select Case ext

    Case ".doc"    
      contenttype = "application/vnd.ms-word"    
      Exit Select

    Case ".docx"    
      contenttype = "application/vnd.ms-word"    
      Exit Select

    Case ".xls"    
      contenttype = "application/vnd.ms-excel"    
      Exit Select

    Case ".xlsx"    
      contenttype = "application/vnd.ms-excel"    
      Exit Select

    Case ".jpg"    
      contenttype = "image/jpg"    
      Exit Select

    Case ".png"    
      contenttype = "image/png"    
      Exit Select

    Case ".gif"    
      contenttype = "image/gif"    
      Exit Select

    Case ".pdf"    
      contenttype = "application/pdf"    
      Exit Select

    End Select

    If contenttype <> String.Empty Then    
      Dim fs As Stream = FileUpload1.PostedFile.InputStream    
      Dim br As New BinaryReader(fs)    
      Dim bytes As Byte() = br.ReadBytes(fs.Length)    

      'Insertamos en la base de datos'

       Dim strQuery As String = "insert into tblFiles" _    
       & "(Name, ContentType, Data)" _    
       & " values (@Name, @ContentType, @Data)"    
       Dim cmd As New SqlCommand(strQuery)    
       cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename    
       cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value _    
       = contenttype    
       cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes 

       InsertUpdateData(cmd)    

       lblMessage.ForeColor = System.Drawing.Color.Green    
       lblMessage.Text = "Archivo subido con éxito!"    
     Else    
       lblMessage.ForeColor = System.Drawing.Color.Red    
       lblMessage.Text = "Formato del archivo no reconocido." _    
       & " Se aceptan sólo formato de Imágenes/Word/PDF/Excel"    
     End If    
  End Sub