Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
Hi all,

can anyone please help me to save an image to database. My code was saving the images previously but do know I might have changed something.

Code is given below and is not giving any errors.

Any advise will be considered as a great help.

Thanks.

What I have tried:

VB
<pre> Dim strSQL As String
        Dim lngID As Double

            lngID = SQLGetNumericFieldValue("SELECT IsNull(Max(RequestID), 0)+1 AS MaxVal From TravelApplicationRequest", "MaxVal")
        

            txtRequestNo.Text = lngID
            
            strSQL = "INSERT INTO TravelApplicationRequest (RequestID, ApplicationNo, CustomerCode, CustomerName, StoreName, Package, VisaType, " & _
                        "VisaNo, VisaIssuedate, VisaExpiryDate, PassportNo, PassportIssueDate, PassportExpiryDate, NIDNo, " & _
                        "HotelName, HotelCity, HotelCountry, HotelAddress, HotelChkInDate, Days, " & _
                        "Rooms, Meals, ChckOutdate, Notes, DepAirline, DepFlightNo, DepFromCity, DepToCity, DepFlightDate, DepTktNo, " & _
                        "ArrAirline, ArrFlightNo, ArrFromCity, ArrToCity, ArrFlightDate, ArrTktNo, isTravelWithDependents, IsApproved, SelectApprover, IsProcessed) VALUES ("


            strSQL = strSQL & "'" & txtRequestNo.Text & "', "

            strSQL = strSQL & "'" & txtApplicationNo.Text & "', "

            strSQL = strSQL & "'" & txtCustomerCode.Text & "', "
            strSQL = strSQL & "'" & txtCustomerName.Text & "', "
            strSQL = strSQL & "'" & txtBranch.Text & "', "
            strSQL = strSQL & "'" & txtPackage.Text & "', "
            strSQL = strSQL & "'" & txtVisaType.Text & "', "
            strSQL = strSQL & "'" & txtVisaNo.Text & "', "
            strSQL = strSQL & "'" & txtVisaIssueDate.Text & "', "
            strSQL = strSQL & "'" & txtVisaExpiry.Text & "', "
            strSQL = strSQL & "'" & txtPassportNo.Text & "', "
            strSQL = strSQL & "'" & txtPassportIssueDate.Text & "', "

            strSQL = strSQL & "'" & txtPassportExpiry.Text & "', "
            strSQL = strSQL & "'" & txtIDNo.Text & "', "
            strSQL = strSQL & "'" & txtHotelName.Text & "', "
            strSQL = strSQL & "'" & txtCity.Text & "', "
            strSQL = strSQL & "'" & txtCountry.Text & "', "

            strSQL = strSQL & "'" & txtAddress.Text & "', "
            strSQL = strSQL & "'" & txtChkInDate.Text & "', "
            strSQL = strSQL & "'" & txtStay.Text & "', "
            strSQL = strSQL & "'" & txtRooms.Text & "', "
            strSQL = strSQL & "'" & txtMeals.Text & "', "
            strSQL = strSQL & "'" & txtChkOut.Text & "', "
            strSQL = strSQL & "'" & txtDetails.Text & "', "
            strSQL = strSQL & "'" & txtDepAirLine.Text & "', "

            strSQL = strSQL & "'" & txtDepFlightNo.Text & "', "
            strSQL = strSQL & "'" & txtDepFrCity.Text & "', "
            strSQL = strSQL & "'" & txtDepToCity.Text & "', "
            strSQL = strSQL & "'" & txtDepFlightDate.Text & "', "
            strSQL = strSQL & "'" & txtDepTktNo.Text & "', "
            strSQL = strSQL & "'" & txtArrAirline.Text & "', "
            strSQL = strSQL & "'" & txtArrFlightNo.Text & "', "
            strSQL = strSQL & "'" & txtArrFCity.Text & "', "
            strSQL = strSQL & "'" & txtArrToCity.Text & "', "


            strSQL = strSQL & "'" & txtArrFlightDate.Text & "', "
            strSQL = strSQL & "'" & txtArrTktNo.Text & "', "
            strSQL = strSQL & "'" & txtDependents.Text & "', "
            strSQL = strSQL & "'" & txtIsApproved.Text & "', "
            strSQL = strSQL & "'" & txtApprovedBy.Text & "',"
            strSQL = strSQL & "'" & txtIsProcessed.Text & "')"

            
            ExecuteSQLQuery(strSQL)
          

        If TxtImageCust.BackgroundImage IsNot Nothing Then

          
            strSQL = "UPDATE TravelApplicationRequest SET Image = @Image WHERE RequestID = " & lngID

            '
            Dim SqlConn As New SqlClient.SqlConnection
            Dim Sqlcmmd As New SqlClient.SqlCommand
            Sqlcmmd.CommandTimeout = 1000
            Try
                SqlConn.ConnectionString = ConnectionStrinG
                SqlConn.Open()

                Dim cmd As New SqlCommand(strSQL, SqlConn)
                Dim ms As New MemoryStream()
                TxtImageCust.BackgroundImage.Save(ms, TxtImageCust.BackgroundImage.RawFormat)
                Dim data As Byte() = ms.GetBuffer()
                Dim p As New SqlParameter("@Image", SqlDbType.Image)
                p.Value = data
                cmd.Parameters.Add(p)
                cmd.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                SqlConn.Close()
                SqlConn.Dispose()
            End Try
Posted
Comments
Richard Deeming 8-Feb-18 16:00pm    
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Fix that vulnerability, and you'll probably find it fixes your problem too.
ZurdoDev 8-Feb-18 16:28pm    
Debug it and find out what is happening. We can't run it for you.
David_Wimbley 8-Feb-18 23:33pm    
If your code is not giving any errors than how do you know it isn't working?

You need to realize we don't have access to your database, your code base or any of your servers...nor do we know anything about what it is your app is attempting to do so simply providing a code dump and saying it doesn't work isn't going to be of much use for anyone much less provide a resolution for your issue.

You need to do some leg work on your end to debug your code, figure out where the issue is and attempt to resolve it. If you've changed your code I would hope your using version control so you should be able to see what exactly changed and revert back to the old version. The other option is to start commenting things out or pull your code into a sample proof of concept console application to slowly add stuff to see where the issue may be.

Otherwise if you aren't willing to help you help yourself, youre probably not going to get much help here.
UCP_2005 9-Feb-18 2:02am    
Thanks for your reoly david

I have debugged the code, its saving all data like text except the image only using above code. This is a simple code to save the record of a passenger with flight details.

When I check the table in database. The image field is NULL. Previously the code was working fine and image was saving into table as well. When image is saved into table then the column the text in the column was "Binary data". Do you find anything wrong in the code?

I will amend my post by commenting the code.


David_Wimbley 9-Feb-18 3:49am    
All that is great but the issue still remains, you need to set a break point in your code and figure out where the root cause of the issue is.

Running it and finding that its empty in DB is fine but doing the same thing over and over and hoping itll start working isnt going to fix it.

Just eyeballing your code, if what you say is true that is happening your issue is likely in this area

TxtImageCust.BackgroundImage.Save(ms, TxtImageCust.BackgroundImage.RawFormat)
                Dim data As Byte() = ms.GetBuffer()
                Dim p As New SqlParameter("@Image", SqlDbType.Image)
                p.Value = data



Something with that, whether it be the memory stream/byte array/background image not working...that is a good place to start debugging, mind you we can't debug your code for you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900