Member Avatar for 15389049

Having problims adding a picture files to the database. Bot sure where i am going worng, ignore the commented out lines that was my first attempt. A little help please!

'If the query found that the name had not been entered before add it into the table
        If imgUpload.PostedFile Is Nothing Then

            Label1.Visible = True
            Label1.Text = "No file specified"

            Exit Sub

        Else
            'Declaration of variables
            Dim connection As SqlConnection = Nothing
            Dim img As FileUpload = CType(imgUpload, FileUpload)
            Dim imgByte As Byte() = Nothing
            If img.HasFile AndAlso Not img.PostedFile Is Nothing Then
                'To create a PostedFile
                Dim File As HttpPostedFile = img.PostedFile
                'Create byte Array with file len
                imgByte = New Byte(File.ContentLength - 1) {}
                'force the control to load data in array
                File.InputStream.Read(imgByte, 0, File.ContentLength)
            End If

            'assigns ext to the file name
            Dim ext As String = imgUpload.FileName
            'converts name to lower case
            ext = ext.ToLower

            'Declaration of variables
            'Dim imgType = objFile.PostedFile.ContentType

            'if statement to check extension validation
            If ext = ".jpg" Then
            ElseIf ext = ".bmp" Then
            ElseIf ext = ".gif" Then
            ElseIf ext = "jpg" Then
            ElseIf ext = "bmp" Then
            ElseIf ext = "gif" Then
            Else
                Label1.Text = "Only gif, bmp, or jpg format files supported."
                Exit Sub
            End If

            'SQL query to access the table tblUser
            'sql = "INSERT INTO tblUser"
            'sql = sql & " SET UserPic " = "'" &  & "'"
            'sql = sql & "Values('" & objFile.Value & "')"

            'Declaration of variables
            Dim myConnection3 As OleDbConnection
            Dim myCommand3 As OleDbCommand

            'Creates a connection to the database
            myConnection3 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" _
            & "Data Source=" & Server.MapPath("DB\ScrumManagementSystem.accdb") & ";")

            'Opens a connection 
            myConnection3.Open()

            Dim sql As String = "INSERT INTO tblUser(UserPic) VALUES(@eimg) SELECT @@IDENTITY"
            Dim cmd As SqlCommand = New SqlCommand(sql)
            cmd.Parameters.AddWithValue("@eimg", imgByte)

            'Create the command object to pass in the SQL string
            myCommand3 = New OleDbCommand(sql, myConnection3)
            'myCommand3.ExecuteNonQuery()

            'closes the connection
            myConnection3.Close()

            Label1.Visible = True
            Label1.Text = "File uploaded Sucessfully!"
        End If
  • 2 Contributors
  • 1 Reply
  • 129 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by TomW

Dani AI

Generated

A simpler, more reliable approach is to save the uploaded image to disk and store the file path in Access, or — if you must keep images inside the database — insert the raw bytes into an OLE Object column. As noted, keeping files on disk avoids rapid growth and maintenance problems in Access. For the quickest wins are: fix a handful of logic/command mistakes, validate the extension properly, and either SaveAs the file or parameterize a proper OleDb INSERT.

Primary bugs to fix (immediate, concrete):

  • The code compares the full FileName to extensions; use System.IO.Path.GetExtension(fileName).ToLowerInvariant().
  • You mixed SqlClient and OleDb: you created a SqlCommand and added parameters to it, then constructed an OleDbCommand but never attached the parameters or executed it.
  • You never call ExecuteNonQuery (and you close the connection before any execution).
  • You appended "SELECT @@IDENTITY" to the INSERT; with OleDb/Access run the identity query as a separate command after the insert.
  • For Access use an OLE Object column for raw bytes; Attachment is an Access UI feature and is harder to manage from simple OleDb SQL.

Minimal VB.NET workflow (save to disk, store path):

If imgUpload.HasFile Then
  Dim ext = Path.GetExtension(imgUpload.FileName).ToLowerInvariant()
  If Not {".jpg",".jpeg",".png",".gif",".bmp"}.Contains(ext) Then Exit Sub
  Dim filename = Guid.NewGuid().ToString("N") & ext
  Dim folder = Server.MapPath("~/uploads")
  If Not Directory.Exists(folder) Then Directory.CreateDirectory(folder)
  imgUpload.SaveAs(Path.Combine(folder, filename))

  Using cn As New OleDbConnection(yourConnString)
    cn.Open()
    Using cmd As New OleDbCommand("INSERT INTO tblUser(UserPicPath) VALUES (?)", cn)
      cmd.Parameters.AddWithValue("?", "/uploads/" & filename)
      cmd.ExecuteNonQuery()
    End Using
  End Using
End If

Quick checklist before deploying:

  • Use parameterized queries (OleDb uses positional parameters — order matters).
  • Validate file size and content (do not trust ContentType alone).
  • Use unique filenames and store relative paths.
  • Ensure upload folder permissions and implement cleanup when records are deleted.
  • If storing bytes, set the Access field to OLE Object and add the parameter as binary, then run a separate "SELECT @@IDENTITY" to retrieve the new ID.
Member Avatar for TomW

It is much more efficient (specially using access) to store just the file name & path of your file in the database and retrieve that info to use in loading your file. Images take up larges amounts of space and everytime you edit a record that contains a picture (even if your not changing the picture itself) the database makes a new copy of that record which keep accumalating the size of your database.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.