dcsimg
Results 1 to 8 of 8

Thread: VB SQL Problem

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    3

    VB SQL Problem

    Hello Everyone, this is my first post. Thanks in advance for any help or pointers given.
    This is my problem. I am attempting to insert some member photos into my column "Membership_Picture". If I use bound controls everything works like a charm, but I don't want bound controls. I want to manually add everything ( learning process....)


    This is I hope the relevant stub you guys need to tell me what i am doing wrong.


    Dim Membership_PictureBytes() as Byte
    Dim Membership_Picture as Image


    Public Sub Convert_Image_To_Bytes()


    Try
    Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream()
    Membership_Picture.Save(ms, Membership_Picture.RawFormat)
    Membership_PictureBytes = ms.GetBuffer

    Catch ex As Exception
    MsgBox("hithere")

    MsgBox(ex.Message)
    End Try
    End Sub

    Public Sub AddNew()
    Dim m_Query As String
    Convert_Image_To_Bytes()

    m_Query = "INSERT INTO Membership (Full_Name,Address_House,Address_Street,Address_City,Address_Province,Address_Postal_Code," & _
    "Phone_Cell,Phone_House,Phone_Other,Doctor_Name,Doctor_Address,Doctor_Phone," & _
    "Membership_CREATED,Membership_Expires,Membership_Type,Notes,Other1,Other2,Membership_Picture) " & _
    "VALUES ( " & _
    "'" & Name & "'," & _
    "'" & Address_House & "'," & _
    "'" & Address_Street & "'," & _
    "'" & Address_City & "'," & _
    "'" & Address_Province & "'," & _
    "'" & Address_Postal_Code & "'," & _
    "'" & Phone_Cell & "'," & _
    "'" & Phone_House & "'," & _
    "'" & Phone_Other & "'," & _
    "'" & Doctor_Name & "'," & _
    "'" & Doctor_Address & "'," & _
    "'" & Doctor_Phone & "'," & _
    "'" & Membership_Created & "'," & _
    "'" & Membership_Expires & "'," & _
    "'" & Membership_Type & "'," & _
    "'" & Notes & "'," & _
    "'" & Membership_Other1 & "'," & _
    "'" & Membership_Other2 & "'," & _
    "'@Picture')"


    m_SqlConn.Open()


    Dim m_SqlCmd As SqlCommand
    m_SqlCmd = New SqlCommand(m_Query, m_SqlConn)
    m_SqlCmd.Parameters.Add("@picture", SqlDbType.Image).Value = Membership_PictureBytes

    Try

    m_SqlConn.Open()
    Dim iresult As Integer = m_SqlCmd.ExecuteNonQuery()

    Debug.Print(Membership_PictureBytes.ToString)
    Catch ex As Exception
    MsgBox(ex.Message)
    m_SqlConn.Close()
    End Try
    m_SqlConn.Close()
    End Sub



    Ok, So This is my problem.. The Code Works without Any Complaints. You go to retreive the photo and BAMMMM error galore. So What I originally had done is used bound controls for the first few entries in my table and those photos work like a charm. but now these new manually added photos do not.
    This is a snippet of my data from the Membership_Picture Column in my Database.

    Good Photo:
    0xFFD8FFE000104A46494600010201006000600000FFEE000E41646F626500640000000001FFE114024578696600004D4D00 2A00000008000501320002000000140000004A013B0002000000070000005E47460003000000010004000047490003000000 01003F0000876900040000000100000066000000C6323030393A30333A31322031333A34383A323800436F72626973000000 0490030002000000140000009C9004000200000014000000B092910002000000033137000092920002000000033137000000 000000323030383A30323A31312031313A33323A343300323030383A30323A31312031313A33323A34330000000005010300 030000000100060000 ......... (it goes on obviously)

    Bad Photo:
    0x4070696374757265
    (All The Entries are the Same, my matter What I convert to byte array)

    I'm searched EVERYWHERE I COULD FOR someone with a Similar Problem, and I'm out of options here.

    I've eliminated a few posibilities. I've converted the Image to Byte Array, then Back To Image and Displayed it in another picturebox. So i'd say the Byte Array "Membership_Picturebytes " works fine, it seems like the database just isn't getting the proper data.

  2. #2

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    3

    Re: VB SQL Problem

    Also Just To Clarify I am Usuaing Visual Studio 2012 Ultimate and SQL Express 2014

  3. #3
    Registered User
    Join Date
    Feb 2015
    Posts
    3

    Re: VB SQL Problem

    AM new to proggraming.please assist me in learning vb as fast as possible.kindly advice on the learning materials that i should get.your assistance will be highly appreciated.thank you

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,795

    Re: VB SQL Problem

    Use parameters or do not. There is no try... actually use parameters. Always... there should be no argument for not using parameters. Ever.
    I'm not surprised it doesn't work - "'@Picture')" you enclosed @Picture in single quotes, so it is sending the LITERAL STRING "@PICTURE" to the database... that's why reading back the image doesn't work... you're not getting the image, you're getting the bytes for "@Picture" ...

    your SQL should look like this:
    Code:
    m_Query = "INSERT INTO Membership (Full_Name,Address_House,Address_Street,Address_City,Address_Province,Address_Postal_Code," & _
    "Phone_Cell,Phone_House,Phone_Other,Doctor_Name,Doctor_Address,Doctor_Phone," & _
    "Membership_CREATED,Membership_Expires,Membership_Type,Notes,Other1,Other2,Membership_Picture) " & _
    "VALUES (@Full_Name, @Address_House, @Address_Street, @Address_City, @Address_Province, @Address_Postal_Code," & _
    " @Phone_Cell, @Phone_House, @Phone_Other, @Doctor_Name, @Doctor_Address, @Doctor_Phone," & _
    " @Membership_CREATED, @Membership_Expires, @Membership_Type, @Notes, @Other1, @Other2, @Picture)"
    then use the Parameters.Add to add each parameter to the query before executing it.

    Somewhat related... be careful stuffing images into the database. Unless you will always know the type of image you're inserting, and will always be the same (ie, all images are bmp or jpeg, etc) then you'll need to store the image type along with it. Also, I'd only do this for low-res, smallish images... I've got a client that insisted on inserting 8x10 sized high res images, AND VERIONSING them, so they could see changes over time .... within 6 months they blew past a Terrabyte of data 70% of which was related to the images... and then they had also turned on the auditing of that table... which made matters worse... we got the auditing turned off, but still they have one of the largest databases I've seen and its mostly image data.

    If how, ever, they are large files, or high res, look into the FILESTREAM datatype on SQL Server, it may work out better.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    3

    Re: VB SQL Problem

    WOW, Thank you so much, i didn't even notice that mistake. All The Images are Small, They are just photo id sized photos, but i'm definantly going to be checking into the Filestream datatype as you suggested for future endeavors.. I was wondering how long it would take for someone to call me out on not use paramaters for everything. I had planned on it, I just wanted to get through my mistake

    This is Definantly RESOLVED. ( not sure how to mark it resolved on the forum.)

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,795

    Re: VB SQL Problem

    I always plan from the start to use parameters... even in one-off prototypes... simply because it's just so much easier for me to do so since I build all my SQL in SSMS - more often than not it's going to end up as a stored proc any how, so string concatenation isn't even an option. I also deal with "people" records, including names, so things like O'Bannion or O'Brien are definate possibilities... that's usually the fastest way to see if string concat is being used or parameters. And then there is also the Little Bobby Drop Tables test.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,795

    Re: VB SQL Problem

    Oh, to mark it resolved, use the Thread Tools menu at the top... top right of post #1... it may or may not be an option for you as a new user though.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Hyperactive Member
    Join Date
    Mar 2012
    Posts
    311

    Re: VB SQL Problem

    Wlecome! Just an FYI since this was your first post... Try to use [CODE] tags around your code (the button with the # sign). This will wrap your code into a code-box like in TG's first response which maintains the code's formatting and adds scrollbars making it easier to read. Actually, TG wrote up a nice intro for new users of the forums that you may want to read; it's in tg's signature under the heading Hitchhiker's Guide to Getting Help at VBF...

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width