Post reply

image files

  • July 7, 2004 at 11:25 am

    #162913

    HELP!  I need to save an image file - SOMEHOW - to a column in the database.  I am using Visual Basic as the interface program, and I have no clue how to go about this.  I have been warned that it is bet NOT to save images to the database but is this true that it isn't a good idea?  I tried this...found it on the microsoft support web site, but I get a compile error of "user-defined type not defined" on the "Dim mstream As ADODB.Stream"

     

    Dim cn As ADODB.Connection

    Dim rs As ADODB.Recordset

    Dim mstream As ADODB.Stream

     

    Set cn = New ADODB.Connection

    cn.Open "Provider=SQLOLEDB;data Source=<name of your SQL Server>;

    Initial Catalog=pubs;User Id=<Your Userid>;Password=<Your Password>"

    Set rs = New ADODB.Recordset

    rs.Open "Select * from pub_info", cn, adOpenKeyset, adLockOptimistic

    Set mstream = New ADODB.Stream

    mstream.Type = adTypeBinary

    mstream.Open

    mstream.Write rs.Fields("logo").Value

    mstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite

    rs.Close

    cn.Close

     

    any ideas anyone??

    Kristin

     

     

    Kristin

  • July 7, 2004 at 11:33 am

    #513707

    Best advice - do not store the images in the database! We have an imaging system in my company and all they do is store the location in the database. The images are kept externally to the database.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 7, 2004 at 2:27 pm

    #513740

    try this link http://www.codeguru.com/forum/showthread.php?threadid=29373

  • July 7, 2004 at 2:28 pm

    #513741

    so the path is listed in its place in the column?

    Kristin

    Kristin

  • July 7, 2004 at 3:01 pm

    #513746

    Yes. What happens is that you then set the Picture source of the image to the path and either refresh the form or open a new one (depending on what works best for you). Are you also scanning the images in? You'll need a way to pick the location and store them. I also suggest using URL's where possible as opposed to fixed drive letters. That way you can move to different servers if need be in the future. And preferably have 2 columns. One for the path and one for the filename. That way if you want to burn some images to CD/DVD (say former employees) then you can update the path based on a resign date not null to point to the DVD drive.

    I also suggest jpg format for the images. Just smaller size.

    Just some thoughts.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 7, 2004 at 3:45 pm

    #513755

    Check the reference to Microsoft ActiveX Data Objects and make certain you are using a library version which supports the stream object ( 2.5 or higher )

  • July 7, 2004 at 4:29 pm

    #513759

    I'm not scanning the images in, but they are coming in off of a fax server.  And I'm trying to make this application EASY EASY for the girls here in the office to use.  So I have to figure out a way to save it and store it in the database easily for them.  None of this is web based, the company is against it. (we are federally regulated and it gets a little messy with the requirements)  Good idea though burning the old stuff onto cd's.  Going to plan on that one.  And what do you thing about tif images?  too big?

    thnks for the input

    Kristin

  • July 7, 2004 at 6:02 pm

    #513769

    A lot of our images are tif. Just avoid bitmap. Lots of wasted space.

    If the fax server saves the file in one location and you are moving them to another location for long-term storage and annotation/rename try looking at this. The following routine was written in MS Access to find what files were FTP'd to us overnight. I then put the list up on a form for files to be selected and processed.

    ----------------------------
Public Function Find_FTP_Files()

Dim PathName As String
Dim FileName As String
Dim FileDate As Date
Dim FileTime As Date
Dim FileSize As Long
Dim ScanTime As Date

Dim DB As database
Dim FTPRS As Recordset

Dim IndexExist As Boolean
Dim i As Integer
Dim Counter As Integer

IndexExist = True

Set DB = CurrentDb()
If DB.TableDefs("FTP_Files").Indexes.Count = 0 Then
    IndexExist = False
ElseIf DB.TableDefs("FTP_Files").Indexes.Count > 0 Then
    Counter = 0
    IndexExist = False
    i = DB.TableDefs("FTP_Files").Indexes.Count
    Do Until Counter = i
        If CStr(DB.TableDefs("FTP_Files").Indexes(Counter).name) = "FileNameIndex" Then
            IndexExist = True
            Exit Do
        End If
        Counter = Counter + 1
    Loop
End If
If IndexExist = False Then
    DB.Execute "CREATE INDEX FileNameIndex ON FTP_Files (File_Name);"
End If

Set FTPRS = DB.OpenRecordset("FTP_Files")
With FTPRS
    .Index = "FileNameIndex"
End With

If FTPRS.EOF = False Then
    FTPRS.MoveFirst

    If DateDiff("n", FTPRS!Last_Scan, Now()) < 1 Then
        FTPRS.Close
        Exit Function
    End If
End If
'If FTPRS.EOF = True Then Exit Function

ScanTime = Now()

PathName = "R:\MSNAUT\"

If Right(PathName, 1)  "\" Then PathName = PathName & "\"

FileName = Dir(PathName & "*.*")
Do Until FileName = ""
    FileDate = DateValue(FileDateTime(PathName & FileName))
    FileTime = TimeValue(FileDateTime(PathName & FileName))
    FileSize = FileLen(PathName & FileName)
    With FTPRS
        If .RecordCount > 0 Then .MoveFirst
        .Seek "=", FileName
        If .NoMatch Then
            .AddNew
            !File_Name = FileName
            !File_Date = FileDate
            !File_Time = FileTime
            !File_Size = FileSize
            !Last_Scan = ScanTime
            !New_File = True
            !Uploaded = False
            .Update
        Else
            If !File_Time = FileTime And !File_Date = FileDate And !File_Size = FileSize Then
                .Edit
                !New_File = False
                
                !Last_Scan = ScanTime
                .Update
            Else
                .Edit
                !File_Date = FileDate
                !File_Time = FileTime
                !File_Size = FileSize
                !Last_Scan = ScanTime
                !New_File = True
                !Uploaded = False
                .Update
            End If
        End If
    End With
    FileName = Dir
Loop

FTPRS.MoveFirst

Do Until FTPRS.EOF
    If Dir(PathName & FTPRS!File_Name) = "" Then FTPRS.Delete
    FTPRS.MoveNext
Loop

FTPRS.Close

End Function
----------------------------



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 8, 2004 at 12:47 am

    #513793

    You might want to have a look at this:

    http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx

    and decide then which way to go.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 8, 2004 at 4:24 am

    #513814

    You can store the images in the SQL Column which has a data type of image. But it is always advisable not to store the image in the database, as it makes tha database heavier and thus performance can decrease. Any how If you want to store the images in the database then this code will certainly help you.

    Dim cn As New ADODB.Connection

    Dim rs As ADODB.Recordset

    Dim DataFile As Integer, Fl As Long, Chunks As Integer

    Dim Fragment As Integer, Chunk() As Byte, i As Integer, FileName As String

    Const ChunkSize As Integer = 16384

    Const conChunkSize = 100

    Dim str1 As String

    Private Sub SavePicture(strFileNm As String)

        If cn.State = adStateOpen Then cn.Close

        cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<USERNAME>;Password=<PASSWORD>;Initial Catalog=<DATABASE NAME>;Data Source=<SQL SERVER NAME>"

        Dim strsql As String

        Set rs = New Recordset

        strsql = "SELECT * FROM <TABLE NAME>"

        rs.Open strsql, cn, adOpenDynamic, adLockOptimistic

        If Not rs.EOF Then

            DataFile = 1

            Open strFileNm For Binary Access Read As DataFile

            Fl = LOF(DataFile)  

            If Fl = 0 Then Close DataFile: Exit Sub

            Chunks = Fl \ ChunkSize

            Fragment = Fl Mod ChunkSize

            ReDim Chunk(Fragment)

            Get DataFile, , Chunk()

            rs!<FEILD NAME>.AppendChunk Chunk()

            ReDim Chunk(ChunkSize)

            For i = 1 To Chunks

                Get DataFile, , Chunk()

                rs!<FEILD NAME>.AppendChunk Chunk()

            Next i

            Close DataFile

        END IF

    End Sub

    In the above mentioned code the <> tag is to be replaced by the approriate namd. You have to be ensure that the column (feild) you are using to hold the image data should be of Image Datatype.

     

    You can retrieve this picture with the help of the below mentioned function

    Private Sub ShowPic()

        DataFile = 1

        Open "pictemp" For Binary Access Write As DataFile

            Fl = rs!<FEILD NAME>.ActualSize 

            If Fl = 0 Then Close DataFile: Exit Sub

            Chunks = Fl \ ChunkSize

            Fragment = Fl Mod ChunkSize

            ReDim Chunk(Fragment)

            Chunk() = rs!<FEILD NAME>.GetChunk(Fragment)

            Put DataFile, , Chunk()

            For i = 1 To Chunks

                ReDim Buffer(ChunkSize)

                Chunk() = rs!<FEILD NAME>.GetChunk(ChunkSize)

                Put DataFile, , Chunk()

            Next i

        Close DataFile

        FileName = "pictemp"

        Picture1.Picture = LoadPicture(FileName)

    End Sub

  • July 8, 2004 at 12:06 pm

    #513899

    Hmmmm, ok, but where am I when I'm saving this file?  I need to create a vb interface to save the file to the table.  do I hook this code to a command on my form?  Where does the image actually tie in?  Should I save it first to a file on my hard drive - or  on the server?

    Kristin

  • July 8, 2004 at 12:10 pm

    #513902

    HAH!  oh my god, this is way over my head  Isn't there a simple way to do this?  We get like 300 faxes in a day that have to be saved to the database somehow.  As a link or whatever, and I have to have "push button" access to any/all of them for the people that work here....

    what do you think?

    Kristin

    Kristin

  • July 8, 2004 at 1:22 pm

    #513912

    Kristin,

    Send me an e-mail at " jimpen at go2netmail .com " and I'll e-mail you an Access database that does some file handling and an easy interface for users.

    You'll have to do some mods to point at the DB server and file movements, but it will give you the general idea.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 8, 2004 at 1:31 pm

    #513914

    you're my hero  going to shoot you an email right now...

     

    kristin

    Kristin

  • July 8, 2004 at 1:37 pm

    #513915

    So not letting me send you an email.....  here is mine.

    kstariha72 at msn.com

     

     

     

    Kristin

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply