[VB.Net] How to get data of image into another database RRS feed

  • Question

  • I have data of barcode in the listview that download by For Loop of barcode, then query data of barcode  for sent image to insert to another database. 

    I have code but the result comes like insert on row 1 was correct but row2 and 3 weren't related to barcode.

    Dim shw As String = TextBox1.Text
            Dim date2 As String = DateTimePicker1.Text
            Dim namec As String = TextBox3.Text
            Dim objConn As New OleDbConnection
            Dim objCmd As New OleDbCommand
            Dim strConnString As String
            Dim cmdUpdate As New OleDbCommand
            Dim img2 As Byte()
            olecon = New OleDbConnection
            olecon.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database\database2.accdb;"
            strConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database\databaseorder.mdb;"
            objConn.ConnectionString = strConnString
            Dim i As Integer = ListView1.Items.Count - 1
            For j As Integer = 0 To i
                Dim num As String = ListView1.Items(j).SubItems(0).Text
                Dim barp As String = ListView1.Items(j).SubItems(1).Text
                Dim namep As String = ListView1.Items(j).SubItems(2).Text
                Dim pricep As Double = ListView1.Items(j).SubItems(3).Text
                Dim coup As Double = ListView1.Items(j).SubItems(4).Text
                Dim sump As Double = ListView1.Items(j).SubItems(5).Text
                oledb = "select * from warehouse where barcode = '" & barp & "'"
                Dim dts2 As DataTable = cmd_excuteToDataTable()
                img2 = dts2.Rows(0)("picofpro")
                cmdUpdate.CommandText = "INSERT INTO ordercus (order_code,no_id,barcode,namecus,namepro,pricepro,countpro,sumprice,dateorder,pic_pro) VALUES ('" & shw & "','" & num & "','" & barp & "','" & namec & "','" & namep & "','" & pricep & "','" & coup & "','" & sump & "','" & date2 & "',@img);"
                cmdUpdate.Parameters.Add("@img", OleDbType.LongVarBinary).Value = img2
                cmdUpdate.CommandType = CommandType.Text
                cmdUpdate.Connection = objConn

    If anyone got an idea, Please help
    Thank you so much

    • Edited by pptadd Saturday, June 17, 2017 10:52 AM
    Saturday, June 17, 2017 10:30 AM

All replies

  • Hello,

    Several things I see, first you are adding a parameter on each iteration, that is iterative so in two loops the parameter is in there twice and should be once. You should setup parameters as shown below, one of the main reasons are strings values containing apostrophes and (this may not be a concern in this project) opens code up to SQL injection.

    Next, you are not checking the result from ExecuteNonQuery.

    What I would recommend is using logic where you parameterize each value, go through a for/each or for/next and run the insert.  Each time get the new primary key. If there is an issue in the code below the function returns false and sets the exception for the failure into a property.

    You don't need to place your code into a class, I did so it's easy to read and would suggest using a class.

    Public Class Sample4
        Private mExceptiom As Exception
        Public ReadOnly Property Exception As Exception
                Return mExceptiom
            End Get
        End Property
        Private Builder As New OleDbConnectionStringBuilder With
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        Public Function AddNewRow(ByVal PersonList As List(Of Person)) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "INSERT INTO Customer (CompanyName,ContactName,Image) Values(@CompanyName,@ContactName,@Image)"
                        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@CompanyName", .DbType = DbType.String})
                        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@ContactName", .DbType = DbType.String})
                        cmd.Parameters.Add(New OleDbParameter With {.ParameterName = "@Image", .OleDbType = OleDbType.LongVarBinary})
                        For Each person As Person In PersonList
                            cmd.Parameters("@CompanyName").Value = person.Name
                            cmd.Parameters("@ContactName").Value = person.Contact
                            cmd.Parameters("@Image").Value = person.Image
                            Affected = cmd.ExecuteNonQuery()
                            If Affected = 1 Then
                                cmd.CommandText = "Select @@Identity"
                                person.Id = CInt(cmd.ExecuteScalar)
                            End If
                    End Using
                End Using
            Catch ex As Exception
                mExceptiom = ex
                Success = False
            End Try
            Return Success
        End Function
    End Class
    Public Class Person
        Public Property Id As Integer
        Public Property Name As String
        Public Property Contact As String
        Public Property Image As Byte()
    End Class

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, June 17, 2017 10:56 AM