Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2014
    Posts
    1

    Unanswered: insert picture in SQL with VBA

    hello.my english is not good.
    i use the botton code for save image in SQL


    Code:
    Dim fDialog As Object
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    Dim varFile As Variant
    Set Cn = New ADODB.Connection
    Cn.Open "Provider=SQLOLEDB;data Source=.;" & _
    "Initial Catalog=test;Trusted_Connection=Yes;"
    Set rs = New ADODB.Recordset
    rs.Open "Select * from Table_2", Cn, adOpenKeyset, adLockOptimistic
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    
    With fDialog
    .AllowMultiSelect = False
    .Title = "لطفا تصوير را مشخص نماييد :"
    .InitialFileName = ""
    
    .Filters.Clear
    .Filters.Add "picture file", "*.png;*.jpg;*.pdf"
    
    If .Show = True Then
    'Loop through each file selected and add it to our list box. '
    For Each varFile In .SelectedItems
    GetFileName = varFile
    Next
    
    Else
    ' MsgBox "You clicked Cancel in the file dialog box."
    End If
    End With
    If GetFileName <> "" Then
    mstream.LoadFromFile GetFileName
    rs.Fields("logo").Value = mstream.Read
    rs.Update
    rs.Close
    Cn.Close
    MsgBox "تصوير ذخيره گرديد"
    End If

    but i have a problem when my database more than 2000 record.
    i think my problem in

    Code:
    rs.Open "Select * from Table_2", Cn, adOpenKeyset, adLockOptimisti

    because select all record in Table_2
    I decided to use bottom code


    Code:
    strSQL = "INSERT INTO Table_2(image_v,sabt,filetype_v,imageid) Values (mstream.Read,'" & [kodimage] & "','" & [file_type] & "','" & Me.Text8 & "');"
    Cnxn.Execute strSQL
    But I get an error with mstream.Read!!!!
    very thank you

    Click image for larger version. 

Name:	Untitled-2.jpg 
Views:	1 
Size:	14.4 KB 
ID:	15882
    Click image for larger version. 

Name:	Untitled-1 copy.jpg 
Views:	3 
Size:	55.1 KB 
ID:	15883

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Code:
    'Loop through each file selected and add it to our list box. '
    For Each varFile In .SelectedItems
        GetFileName = varFile
        
         'PUT YOUR INSERT PICTURE QUERY HERE
    
        'UPDATE [picture] = " & GETFILENAME  & "'"
    Next

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •