Results 1 to 4 of 4
  1. #1
    Kencao is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    12

    Pass image parameter to stored procedure

    Hi,



    I am trying to pass an image to a SQL stored procedure but have no success. I don't know what data type or length to use for the image parameter to be passed to the stored procedure. Received a "Parameter object is improperly defined. Inconsistent or incomplete information was provided." error message when running the cmdExecuteSP_Click() sub below. Could someone help?

    Thanks,
    Ken

    Notes: 1) datatype is image for the image field in the SQL table.
    2) Use bound object frame (photo1) to store the image (not image file).
    3) Access 2007 and SQL 2005
    4) Use stored procedure with one parameter, @photo1 (image type), to insert image to table.


    ===========================================
    Private Sub cmdExecuteSP_Click()

    Dim cmd As ADODB.Command

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = "DSN=STDB;Trusted_Connection=Yes;DATABASE=STDB ;"

    cmd.CommandText = "usp_tblTest2_Insert01b"
    cmd.CommandType = adCmdStoredProc

    ' need help with statement below
    cmd.Parameters.Append cmd.CreateParameter("@photo1", adLongVarBinary, adParamInput, 2500, photo1.Value)

    cmd.Execute

    End Sub

  2. #2
    Max D's Avatar
    Max D is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    63
    Do you really need such an complicated syntax?

    Maybe you'd better tell us what are you trying to achieve? Usually you give image to Access, Access pushes it to SQL Serv.

  3. #3
    Kencao is offline Novice
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    12
    Max D,

    I am trying to create an application for end-users to enter real estate properties information and images. It takes about 3 minutes to save each property (include data and 4 images) from Access to the SQL database. That time frame is unacceptable for the end-users.

    I am hoping to use a stored procedure to reduce the save-data time from Access to the SQL database. I can pass the data to the stored procedure but not the images. I want to know if it is possible to pass image parameters to a stored procedure.

    Also, I will test out the AccessImagine component you suggested in the UtterAccess forum.

    Thank you!

  4. #4
    Max D's Avatar
    Max D is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2009
    Posts
    63
    Quote Originally Posted by Kencao View Post
    It takes about 3 minutes to save each property (include data and 4 images) from Access to the SQL database.
    There is an explanation for this. When you use OLE image, Access saves your image file + uncompressed bitmap made of this image (more then 2 Mb for 1024x768 pic). It can take a while to save such an amount thru network.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Passing variable values to Stored Procedure
    By rodrigopcnet in forum Access
    Replies: 1
    Last Post: 04-14-2010, 10:35 AM
  2. Pass a form property to a procedure
    By trb5016 in forum Programming
    Replies: 2
    Last Post: 02-17-2010, 04:15 PM
  3. Replies: 1
    Last Post: 11-17-2009, 06:55 AM
  4. Replies: 0
    Last Post: 10-04-2009, 04:11 AM
  5. stored procedure return value to access form
    By rbw940 in forum Programming
    Replies: 0
    Last Post: 10-13-2008, 01:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums