Click here to Skip to main content
14,970,700 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem where I need to get data from 2 different DataBases, one is a normal MySQL and another is a SQL server.
Both Databases share products but both use and store different data.
MySQL is used for basic quick data like cost, name, desc. and can be written and read.
And SQL is used for calculations of stock, pvp's , etc. only too read.

I need to cross this information into a single Listview for logistic purposes. And one problem is that some products in MYSQL dont have match values in the SQL one.

What I have tried:

I tried this code so far with some help, but the code is not returning values (I have data in the DB and i've tested the sql script). What can I be doing wrong?

VB
<pre>Private Sub Prod_input_KeyDown(sender As Object, e As KeyEventArgs) Handles Prod_input.KeyDown
        If e.KeyCode = Keys.Enter Then
            Call button_search_Click(Nothing, Nothing)
        End If
    End Sub

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

        Dim dt = GetMySqlData(proc_desc.Checked, proc_ref.Checked, Prod_input.Text)
        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 ConStrMySql As String = "server=#######; userid=#######; password=#######; database=#######"
    Private ConStrSql As String = "Data Source=#######; Initial Catalog=#######; User ID=#######; Password=#######"

    Private Function GetMySqlData(ProcDesc As Boolean, ProcRef As Boolean, ProdInput As String) As DataTable
        Dim marca_prod As String
        Dim fam_prod As String

        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 "
        ElseIf ProcRef Then
            sql &= "WHERE p.referencia LIKE @Prod "
        Else
            sql &= "WHERE (p.referencia LIKE @Prod OR p.descricao LIKE @Prod) "
        End If

        If type_1.Checked Then
            sql &= "AND p.tipo = 1 "
        ElseIf type_2.Checked Then
            sql &= "AND p.tipo = 2 "
        End If

        If combo_proc_marca.SelectedIndex = -1 And combo_proc_fam.SelectedIndex = -1 Then
            marca_prod = ""
            sql &= "AND p.Id_marca LIKE '" & marca_prod & "%' "
        ElseIf combo_proc_marca.SelectedIndex > -1 And combo_proc_fam.SelectedIndex = -1 Then
            marca_prod = combo_proc_marca.SelectedIndex + 1
            sql &= "AND p.Id_marca = " & marca_prod & " "
        ElseIf combo_proc_fam.SelectedIndex > -1 And combo_proc_marca.SelectedIndex = -1 Then
            fam_prod = combo_proc_fam.SelectedIndex + 1
            sql &= "AND p.Id_familia = " & fam_prod & " "
        ElseIf combo_proc_fam.SelectedIndex > -1 And combo_proc_marca.SelectedIndex > -1 Then
            fam_prod = combo_proc_fam.SelectedIndex + 1
            marca_prod = combo_proc_marca.SelectedIndex + 1
            sql &= "AND p.Id_familia = " & fam_prod & " AND p.Id_marca = " & marca_prod & " "
        End If

        If prod_active.Checked Then
            sql &= "AND p.disponibilidade = 1 ORDER BY ultima_atualizacao ASC"
        ElseIf prod_inactive.Checked Then
            sql &= "AND p.disponibilidade = 0 ORDER BY ultima_atualizacao ASC"
        ElseIf proc_aprov.Checked Then
            sql &= "AND p.disponibilidade = 2 ORDER BY ultima_atualizacao ASC"
        ElseIf proc_both.Checked Then
            sql &= " ORDER BY ultima_atualizacao ASC"
        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 & "%'"
            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
Posted
Updated 11-May-21 5:06am

This looks wrong:
VB.NET
<pre>Using conn As New MySqlConnection(ConStrMySql),
            cmd As New MySqlCommand(sql, conn)
            cmd.Parameters.Add("@Prod", MySqlDbType.VarChar).Value = "'%" & ProdInput & "%'"
            conn.Open()

There should not be quote marks surrounding the value you're passing. The Parameter object takes care of the quote marks for you. It should be:
VB.NET
<pre>Using conn As New MySqlConnection(ConStrMySql),
            cmd As New MySqlCommand(sql, conn)
            cmd.Parameters.Add("@Prod", MySqlDbType.VarChar).Value = "%" & ProdInput & "%"
            conn.Open()

Other than that, if it's still a problem, you have no choice but to set a breakpoint at the start of the GetMySqlData function and run the code. When the breakpoint is hit, step through the code and examine variables, watch what happens and check the SQL statement it builds. Chances are really good your code is building an SQL statement that doesn't match any records or is not valid SQL.
   
Comments
Rwolf27 11-May-21 11:21am
   
Nice one, i think solved the problem of the DT, but if you don't mind. In the same code (because i never got so far). Like I said in the beginning, there are some products that i dont have in the SQL DB, so they might return empty or null, and I think that now I'm getting an error in the lv.SubItems.Add(dt2.Rows(0)(0).ToString) of "'There is no row at position 0." what can I do to handle this?
Dave Kreskowiak 11-May-21 11:37am
   
Like I said, you have no choice but to run the code under the debugger and look at the variables, one line at a time, to see what the code is doing and if it's using data you expect.

There's a reason why your code isn't returning the data you think it's supposed to return. The only way you're going to find that is to use debugging techniques.

The debugger is there to debug YOU and your understanding of the code and data it's working with.

Nobody can do any of that for you because we don't have the application code, nor the databases, data, environment, ... Nobody can run your code to test it, other than you.
Rwolf27 11-May-21 11:43am
   
No, its not that, your answer got me the data, its returning. The problem that I'm saying now, is that my code can't handle the fact that some rows in the SQL is returning empty (is supposed too). But I want in that case that the rest of the columns be just empty. I don't know if I'm being clear, sorry.
Dave Kreskowiak 11-May-21 12:37pm
   
You're going to have to look at the content of the datatable before you try convert what may be nulls to strings.

I don't know what you expect to be in the datatable, so this is a guess:
lv.SubItems.Add(If(dt2.Rows(0)(0) = Nothing, String.Empty, dt2.Rows(0)(0).ToString))
Rwolf27 11-May-21 12:46pm
   
The SQL DB is all decimals.
Dave Kreskowiak 11-May-21 12:50pm
   
I couldn't tell you what's going then. Only the debugger can.

I'm making guesses without being able to see the data in the datatable. That's what the debugger is for. Start examining the content of the datatable right before you try to convert stuff .ToString. Chances are goo you're trying to convert a Nothing or DbNull to a string, which will not work. If that's the case, you have to check for that condition before you try to convert that data to a string.
CHill60 11-May-21 11:34am
   
5'd - flippin' good eyesight!
Dave Kreskowiak 11-May-21 12:26pm
   
My eyes focus at different ranges, making everything kind of blurry no matter what I'm looking at, so that's funny! :)
One approach could be to populate two DataTables - one from each database as you have done, and then join them using Linq.

There are some suggestions on the Solutions to this CP post C# Linq join two DataTables[^]
   
Comments
Rwolf27 11-May-21 10:29am
   
Thanks, but my dt is returning as "Nothing", and i cant point what is causing this. The function never gets to go to the loop to populate the listview.
CHill60 11-May-21 10:40am
   
Are you absolutely sure the SQL script you have tested is the one you are creating? You should really be using a parameterised query rather than string concatenation
Rwolf27 11-May-21 10:42am
   
I know, but I used the output caming in the parameters in VS and testes directly in Workbench, and worked.
This code is reused and with just Mysql worked perfectly.
By the way, Iam a junior in VB, still learning.
Rwolf27 11-May-21 10:54am
   
One thing i miss to point out, I'm already populate 2 DT, one for MySQL and another from SQL, and then join them in the ListView, don't know if it is the same. But i never worked with linq.
CHill60 11-May-21 11:43am
   
I know you are already populating 2 DTs - I said "as you have done"
Given the fix that Dave spotted, and you say has fixed the problem, I don't see how this code could have worked against the MySQL database as it would have had the SQL bug - hence my point of making sure that the SQL command you are constructing really is the one you think you are running.
The way you are "joining" the information together when populating the listview is assuming that there is one row exactly on both databases for each and every ref. You stated "And one problem is that some products in MYSQL dont have match values in the SQL one." so your method won't work unless you find a way to handle those gaps.
Rwolf27 11-May-21 11:47am
   
True, I'm sorry I didnt saw that. You right, that's why I'm asking if there is a way to adapt the code to accept that or to handle that ...
CHill60 11-May-21 11:51am
   
It is a real PITA trying to keep two separate lists in sync - you have to move forward on one of them until you get a matching ref again - it may be more than one record. You need to let "something" do that heavy lifting - either set up the databases as Linked Servers so you can get all the data in a single SQL query (from both databases at once) or use Linq - both methods will return a single row per Ref with blanks where there is no matching data from the "other" database (assuming you use a left outer join)
Rwolf27 11-May-21 11:58am
   
Right, the link you have sent have informations in regard of that topic?
CHill60 11-May-21 12:51pm
   
The link has some other links about how to use Linq for this, yes. If you go down the Linked Server route then this might help Access MySQL data from SQL Server via a Linked Server[^]

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