Duplicate entries with HeidiSQL

  • VB.NET

Es gibt 8 Antworten in diesem Thema. Der letzte Beitrag () ist von INOPIAE.

    Duplicate entries with HeidiSQL

    Hello,
    I am trying to retrieve data from HeidiSQL. In the table M, the rows are mentioned in the code below are occuring 6 times but in the table N they are occuring only 4 times and i wanna join them but i am getting each entry 4 times and i want it only once. Can someone help me? Both have only one common column and that is "Prod_ Order No_"

    VB.NET-Quellcode

    1. Private Sub btnFAübernhmn_Click(sender As Object, e As EventArgs) Handles btnFAübernhmn.Click
    2. If DataGridView1.SelectedCells.Count > 0 Then
    3. Dim rowIndex As Integer = DataGridView1.SelectedCells(0).RowIndex
    4. Dim Prod_OrderNo_ As String = DataGridView1.Rows(rowIndex).Cells(0).Value
    6. 'msg for user to select atleast one row
    7. If String.IsNullOrEmpty(Prod_OrderNo_) Then
    8. MsgBox("Please select a non-empty row.", vbInformation)
    9. Return
    10. End If
    12. ' Open the database connection
    13. OpenNavDB()
    15. ClearDataGridView2()
    17. Dim Erpcmd2 As New SqlCommand
    18. Erpcmd2.Connection = NavDB
    19. 'WHERE dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Routing No_] LIKE '01%' AND dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Status]= 3
    20. 'from database
    21. Erpcmd2.CommandText = "SELECT M.[Operation No_],N.[No_],M.[Qlty Measure Code],M.[Description],M.[Min_ Value],M.[Max_ Value],M.[Mean Tolerance]
    22. FROM dbo.[RPS24$Prod_ Order Rtng Qlty Meas_] M
    23. INNER JOIN dbo.[RPS24$Prod_ Order Routing Line] N ON M.[Prod_ Order No_] = N.[Prod_ Order No_]
    24. where M.[Prod_ Order No_]= @Prod_OrderNo_ AND M.[Status]=N.[Status]
    25. ORDER BY [Operation No_]"

    *Code-BBCode eingefügt*

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Marcus Gräfe“ ()

    HeidiSQL is a database access client, not a DBMS. You probably mean something like MSSQL or MySQL.

    Concerning your question: You could change SELECT to SELECT DISTINCT to get only one entry per identical fetched row.
    Besucht auch mein anderes Forum:
    Das Amateurfilm-Forum

    It sounds like you want to create a specific query. If you provide example data, it will be much easier to help you.
    I think you dont clarify sufficiently which rows you want. With 6 rows in M matching one specific ordernumber and 4 results in N matching the same ordernumber and status you should expect to find 24 rows in your resultset.
    You can try creating a fiddle for us, with that we could also easily check for the results ourselves.

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „Haudruferzappeltnoch“ ()

    SpaceyKevin schrieb:

    FROM dbo.[RPS24$Prod_ Order Rtng Qlty Meas_] M
    INNER JOIN dbo.[RPS24$Prod_ Order Routing Line] N ON M.[Prod_ Order No_] = N.[Prod_ Order No_]
    where M.[Prod_ Order No_]= @Prod_OrderNo_ AND M.[Status]=N.[Status]


    You link the tables two times:
    once in the INNER JOIN M.[Prod_ Order No_] = N.[Prod_ Order No_]
    and once in the WHERE statement M.[Status]=N.[Status]
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).

    Hello, Thank you for your reply. The idea behind it was that a user would select one row from a datagridview1 and click on the button 'btnFAübernhmn' and then the Quality measuring values(Prüfmerkmale) would be shown in another datagridview2.

    The problem I was facing was that all the data needed to show in datagridview2 was present in one table in the database except one data which was in another table so I had to join those two tables.
    For each row there are six (Prüfmerkmale) which are present in the 'M' column and one Merkmale in 'N', now in the 'N' Table this information was mentioned 4 times, but I needed the information for 6 merkmales.

    Explanation in more detail: There are four Arbeitsgang where the quality features of the product are mentioned, eg. During one Arbeitsgang the product has one only quality merkmale to be measured by the user while in the second Arbeitsgang, there are four quality features to be measured. Now in table 'M' there are quality features mentioned i.e 6 and in table 'N' the number of Arbeitsgang is mentioned which I have is 4.
    So I needed each merkmale their corresponding Arbeitsgang number and there I was facing an error.

    But the code I provided is working fine now.




    VB.NET-Quellcode

    1. Private Sub btnFAübernhmn_Click(sender As Object, e As EventArgs) Handles btnFAübernhmn.Click
    2. If DataGridView1.SelectedCells.Count > 0 Then
    3. Dim rowIndex As Integer = DataGridView1.SelectedCells(0).RowIndex
    4. Dim Prod_OrderNo_ As String = DataGridView1.Rows(rowIndex).Cells(0).Value
    6. 'msg for user to select atleast one row
    7. If String.IsNullOrEmpty(Prod_OrderNo_) Then
    8. MsgBox("Please select a non-empty row.", vbInformation)
    9. Return
    10. End If
    12. ' Open the database connection
    13. OpenNavDB()
    15. ClearDataGridView2()
    17. Dim Erpcmd2 As New SqlCommand
    18. Erpcmd2.Connection = NavDB
    20. 'from database
    21. Erpcmd2.CommandText = "SELECT dbo.[RPS24$Prod_ Order Routing Line].[No_], dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Operation No_],
    22. dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Qlty Measure Code], dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Description],
    23. dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Min_ Value], dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Max_ Value], dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Mean Tolerance]
    24. FROM dbo.[RPS24$Prod_ Order Routing Line]
    25. JOIN dbo.[RPS24$Prod_ Order Rtng Qlty Meas_] ON dbo.[RPS24$Prod_ Order Routing Line].[Prod_ Order No_] = dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Prod_ Order No_]
    26. Where dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Status] = dbo.[RPS24$Prod_ Order Routing Line].[Status]
    27. AND dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Operation No_] = dbo.[RPS24$Prod_ Order Routing Line].[Operation No_]
    28. AND dbo.[RPS24$Prod_ Order Routing Line].[Prod_ Order No_] = @Prod_OrderNo_"
    30. 'AND dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Routing No_] LIKE '01%'
    32. Erpcmd2.Parameters.Add("@Prod_OrderNo_", SqlDbType.NVarChar).Value = Prod_OrderNo_
    33. Erpcmd2.Parameters.Add("@Status", SqlDbType.Int).Value = 3
    36. Dim MyErpReader As SqlDataReader
    37. MyErpReader = Erpcmd2.ExecuteReader
    39. If MyErpReader.HasRows Then
    40. While MyErpReader.Read
    41. DataGridView2.Rows.Add(
    42. MyErpReader("No_"),
    43. MyErpReader("Operation No_"),
    44. MyErpReader("Qlty Measure Code"),
    45. MyErpReader("Description"),
    46. Format(MyErpReader("Min_ Value"), "0.000"),
    47. Format(MyErpReader("Max_ Value"), "0.000"),
    48. Format(MyErpReader("Mean Tolerance"), "0.000")
    49. )
    50. End While
    52. 'adjusting values in columns to fit
    53. For Each column As DataGridViewColumn In DataGridView2.Columns
    54. column.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
    55. Next
    57. Else
    58. MsgBox("It is empty", vbInformation)
    59. End If
    60. NavDB.Close()
    61. End If
    63. End Sub

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „Marcus Gräfe“ ()

    You can change your code to this may be it is better to understand:

    SQL-Abfrage

    1. ​FROM dbo.[RPS24$Prod_ Order Routing Line], dbo.[RPS24$Prod_ Order Rtng Qlty Meas_]
    2. WHERE
    3. dbo.[RPS24$Prod_ Order Routing Line].[Prod_ Order No_] = dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Prod_ Order No_] AND
    4. dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Status] = dbo.[RPS24$Prod_ Order Routing Line].[Status] AND
    5. dbo.[RPS24$Prod_ Order Rtng Qlty Meas_].[Operation No_] = dbo.[RPS24$Prod_ Order Routing Line].[Operation No_] AND
    6. dbo.[RPS24$Prod_ Order Routing Line].[Prod_ Order No_] = @Prod_OrderNo_"
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).

Facebook
Telefonspende