Insert Values in Listview from 2 different DataBases (MySQL and SQL)

Question

I'm working with 2 different DB, one is an SQL server and the other is a MySQL.

I'm trying to cross the data between them. The 2 DB have one reference of product in common. But both use very different data.

For example in Mysql I have simple data like, description, price, type .... And in SQL I have Last price used, discounts, barcode .... But there are some products that don't have data in both DB, so they have to remain empty in some fields. And I Have to cross this data into a ListView.

I have this code so far:

    Private Sub button_search_Click(sender As Object, e As EventArgs) Handles button_search.Click


    '--------------------------------MySQL

    Call connect()

    Dim marca_prod As String
    Dim fam_prod As String
    Dim tipo_prod As String
    Dim sql As String

    rs = New ADODB.Recordset


    If type_1.Checked Then
        tipo_prod = "1"
    ElseIf type_2.Checked Then
        tipo_prod = "2"
    Else
        tipo_prod = ""
    End If


    If proc_desc.Checked Then
        sql = "SELECT p.referencia AS Referencia, p.descricao AS Descrição, p.tipo AS Tipo, f.nome_familia AS Familia, m.nome_marca AS Marca FROM ((produtos AS p INNER JOIN familias AS f ON p.Id_familia = f.Id_familia) INNER JOIN marcas AS m ON p.Id_marca = m.Id_marca) WHERE p.descricao LIKE '%" & Prod_input.Text & "%' AND p.tipo LIKE '" & tipo_prod & "%' "
    ElseIf proc_ref.Checked Then
        sql = "SELECT p.referencia AS Referencia, p.descricao AS Descrição, p.tipo AS Tipo, f.nome_familia AS Familia, m.nome_marca AS Marca FROM ((produtos AS p INNER JOIN familias AS f ON p.Id_familia = f.Id_familia) INNER JOIN marcas AS m ON p.Id_marca = m.Id_marca) WHERE p.referencia LIKE '%" & Prod_input.Text & "%' AND p.tipo LIKE '" & tipo_prod & "%' "
    Else
        sql = "SELECT p.referencia AS Referencia, p.descricao AS Descrição, p.tipo AS Tipo, f.nome_familia AS Familia, m.nome_marca AS Marca FROM ((produtos AS p INNER JOIN familias AS f ON p.Id_familia = f.Id_familia) INNER JOIN marcas AS m ON p.Id_marca = m.Id_marca) WHERE (p.referencia LIKE '%" & Prod_input.Text & "%' OR p.descricao LIKE '%" & Prod_input.Text & "%') AND p.tipo LIKE '" & tipo_prod & "%' "
    End If

    rs.Open(sql, conn)

    query_results.Items.Clear()

    While Not rs.EOF
        Dim lv As New ListViewItem
        lv = query_results.Items.Add(rs.Fields(0).Value)
        lv.SubItems.Add(rs.Fields(1).Value)
        lv.SubItems.Add(rs.Fields(2).Value)
        lv.SubItems.Add(rs.Fields(3).Value)
        lv.SubItems.Add(rs.Fields(4).Value)

        rs.MoveNext()
    End While


    '-----------------------------SQL

    Dim myDBCnn As SqlConnection
    Dim myDBCmd As SqlCommand
    Dim myDBReader As SqlDataReader
    myDBCnn = New SqlConnection("************")
    myDBCnn.Open()
    Dim MystrQ As String

    Dim i = 0
    While Not rs.EOF
        MystrQ = "Select stock, epcult, epcpond FROM st WHERE ref ='" & rs.Fields(0).Value & "'"
        myDBCmd = New SqlCommand(MystrQ, myDBCnn)
        myDBReader = myDBCmd.ExecuteReader
        While myDBReader.Read
            Dim lv As New ListViewItem
            lv = query_results.Items.Add(myDBReader.Item(5))
            lv.SubItems.Add(myDBReader.Item(6))
            lv.SubItems.Add(myDBReader.Item(7))
        End While
    End While

    myDBReader.Close()
        myDBCmd.Dispose()
        myDBCnn.Close()

        If query_results.Items.Count = 0 Then
        MsgBox("Não foram encontrados registos para os parametros selecionados!")

    End If

    count_label.Text = query_results.Items.Count

End Sub

At the moment I got an error in the myDBReader.Close() at the end -> "Object reference not set to an instance of an object" But i think the other code is not right. What can I do ?

Mary · Accepted Answer · 2021-05-11 17:11:45Z

I separated the data access code from the user interface code. This will make your application easier to maintain. The database functions are passed values so they are not dependent on where the values come from. The user interface is not dependent on where the data comes from. It could be a text file, a web service or any kind of database.

I dumped the ADODB and recordset which are very old technologies and used straight ADO.net. This will require an imports for the MySql provider.

Imports MySql.Data.MySqlClient

The Using...End Using blocks handle closing and disposing database objects even if there is an error.

Always use parameters. Never concatenate strings to build sql statements. I had to guess at the datatypes for the parameters. Check your database and adjust the code as necessary.

Private ConStrMySql As String = "Your connection string"
Private ConStrSql As String = "Your connection string"

Private Sub button_search_Click(sender As Object, e As EventArgs) Handles button_search.Click
    Dim tipo_prod As String
    If type_1.Checked Then
        tipo_prod = "1"
    ElseIf type_2.Checked Then
        tipo_prod = "2"
    Else
        tipo_prod = ""
    End If
    Dim dt = GetMySqlData(proc_desc.Checked, proc_ref.Checked, Prod_input.Text, tipo_prod)
    query_results.Items.Clear()
    For Each row As DataRow In dt.Rows
        Dim lv As New ListViewItem
        lv.Text = row(0).ToString
        lv.SubItems.Add(row(1).ToString)
        lv.SubItems.Add(row(2).ToString)
        lv.SubItems.Add(row(3).ToString)
        lv.SubItems.Add(row(4).ToString)
        Dim dt2 = GetSqlData(row(0).ToString)
        lv.SubItems.Add(dt2.Rows(0)(0).ToString)
        lv.SubItems.Add(dt2.Rows(0)(1).ToString)
        lv.SubItems.Add(dt2.Rows(0)(2).ToString)
        query_results.Items.Add(lv)
    Next
    If query_results.Items.Count = 0 Then
        MsgBox("Não foram encontrados registos para os parametros selecionados!")
    End If
    count_label.Text = query_results.Items.Count.ToString
End Sub

Private Function GetMySqlData(ProcDesc As Boolean, ProcRef As Boolean, ProdInput As String, tipo As String) As DataTable
    Dim sql = "SELECT p.referencia AS Referencia, p.descricao AS Descrição, p.tipo AS Tipo, f.nome_familia AS Familia, m.nome_marca AS Marca FROM ((produtos AS p INNER JOIN familias AS f ON p.Id_familia = f.Id_familia) INNER JOIN marcas AS m ON p.Id_marca = m.Id_marca) "
    If ProcDesc Then
        sql &= "WHERE p.descricao Like @Prod AND p.tipo LIKE @Type;"
    ElseIf ProcRef Then
        sql &= "WHERE p.referencia LIKE @Prod AND p.tipo LIKE @Type;"
    Else
        sql &= "WHERE (p.referencia LIKE @Prod OR p.descricao LIKE @Prod) AND p.tipo LIKE @Type;"
    End If
    Dim dt As New DataTable
    Using conn As New MySqlConnection(ConStrMySql),
            cmd As New MySqlCommand(sql, conn)
        cmd.Parameters.Add("@Prod", MySqlDbType.VarChar).Value = "%" & ProdInput & "%"
        cmd.Parameters.Add("@Type", MySqlDbType.VarChar).Value = tipo & "%"
        conn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

Private Function GetSqlData(Ref As String) As DataTable
    Dim dt As New DataTable
    Using conn As New SqlConnection(ConStrSql),
            cmd As New SqlCommand("Select stock, epcult, epcpond FROM st WHERE ref = @Ref", conn)
        cmd.Parameters.Add("@Ref", SqlDbType.VarChar).Value = Ref
        conn.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Return dt
End Function

EDIT

For tipo to be an Int16 (Short in vb.net) you will need to do more than just change the datatype of the parameter.

Correct the setting of the value of tipo_prod.

    Dim tipo_prod As Short
    If type_1.Checked Then
        tipo_prod = 1
    ElseIf type_2.Checked Then
        tipo_prod = 2
    Else
        tipo_prod = 0
    End If

Correct the signature of the MySql function.

    Private Function GetMySqlData(ProcDesc As Boolean, ProcRef As Boolean, ProdInput As String, tipo As Short) As DataTable

Correct the sql statement. What could it possibly mean for one number to be "Like" another?

    If ProcDesc Then
        sql &= "WHERE p.descricao Like @Prod AND p.tipo = @Type;"
    ElseIf ProcRef Then
        sql &= "WHERE p.referencia LIKE @Prod AND p.tipo = @Type;"
    Else
        sql &= "WHERE (p.referencia LIKE @Prod OR p.descricao LIKE @Prod) AND p.tipo = @Type;"
    End If

Have you checked the syntax of your sql in Workbench and SSMS?

EDIT 2

If there is not matching data in the SQL Server database just ignore it.

    For Each row As DataRow In dt.Rows
        Dim lv As New ListViewItem
        lv.Text = row(0).ToString
        lv.SubItems.Add(row(1).ToString)
        lv.SubItems.Add(row(2).ToString)
        lv.SubItems.Add(row(3).ToString)
        lv.SubItems.Add(row(4).ToString)
        Dim dt2 = GetSqlData(row(0).ToString)
        If dt2.Rows.Count > 0 Then
            lv.SubItems.Add(dt2.Rows(0)(0).ToString)
            lv.SubItems.Add(dt2.Rows(0)(1).ToString)
            lv.SubItems.Add(dt2.Rows(0)(2).ToString)
        End If
        query_results.Items.Add(lv)
    Next

Helo, thank you for your detailed answer. Iam getting an error in Using reader = cmd.ExecuteReader as Input string was not in a correct format. I know you warned me about my datatypes in the DB and so I let the ProdInput as Varchar and tipo as Int16. In the DB they are varchar and Integer respectively. I changed all the datatypes in the code to match, but I get the same error. I think the problem is that the SQL string dont get the values of @Prod and @Type, they go as I have written, so with no value.
So the problem was on my script, but i corrected the issue. But how can I handle the situation when a product doesnt exist in the sql DB ? Because i get an error of that.
@Rwolf27 I addressed your first comment in EDIT. See EDIT 2 for your second comment.

Collectives™ on Stack Overflow

Insert Values in Listview from 2 different DataBases (MySQL and SQL)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related