1 Replies - 2478 Views - Last Post: 13 December 2012 - 11:40 PM Rate Topic: -----

#1 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

retrieve values from tables having one to many relationship

Posted 13 December 2012 - 10:28 PM

I am retrieving data from tables with one to many relationship and displaying them inside textboxes.I dont get any error but no data is being displayed in the textboxes.There are two tables Product_Table and Service_Centre_Table.Produtct_Table contains S_Code which is primary key of Service_Centre_Table.Record should be dispalyed depending on the Prd_Sr_Key_No which is primary key of Product_Table entered in the Sr.Key.No textbox.
Following is my code:
 Dim sqlcon As New SqlConnection(My.Settings.Hardware_DatabaseConnectionString)
        sqlcon.Open()
        Dim datestr As String
 Dim sqlcmdri As New SqlCommand("SELECT * from Product_Table inner join Service_Centre_Table on Product_Table.S_Code=Service_Centre_Table.S_Code where Prd_Sr_Key_No='" & SrKeyNoTextBox.Text & "'", sqlcon)
        Dim sdrri As SqlDataReader

        sdrsearch = sqlcmdsearch.ExecuteReader()
        If sdrsearch.HasRows Then
            While sdrsearch.Read
                ProductTypeComboBox.Text = sdrsearch.Item("Prd_Type")
            End While
        End If
        sdrsearch.Close()
 ElseIf ProductTypeComboBox.Text = "REPAIR(INWARD)" Then
                sdrri = sqlcmdri.ExecuteReader()
                If sdrri.HasRows Then
                    While sdrri.Read
                        ProductNameTextBox.Text = sdrri.Item("Prd_Name")
                        BrandNameComboBox.Text = sdrri.Item("Prd_Brand")
                        ProductTypeComboBox.Text = sdrri.Item("Prd_Type")
                        DetailRichTextBox.Text = sdrri.Item("Prd_Detail")
                        datestr = sdrri.Item("Dte_Stck_In")
                        Dim splitstr As String() = datestr.Split("/")
                        ddComboBox.Text = splitstr(0)
                        mmComboBox.Text = splitstr(1)
                        yyyyComboBox.Text = splitstr(2)

                        BroughtByTextBox.Text = sdrri.Item("Brght_By")
                        NoteRichTextBox.Text = sdrri.Item("Note")

                        ServiceCentreCodeTextBox.Text = sdrri.Item("S_Code")
                        ServiceCentreNameComboBox.Text = sdrri.Item("S_Name")
                        ServiceCentreAddressRichTextBox.Text = sdrri.Item("S_Address")
                        ServiceCentreContactTextBox.Text = sdrri.Item("S_Cntct")
                        ServiceCentreContactPersonTextBox.Text = sdrri.Item("S_Cntct_Prsn")
                    End While
                End If
                sdrri.Close()
            End If



Is This A Good Question/Topic? 0
  • +

Replies To: retrieve values from tables having one to many relationship

#2 purvabhilare1587  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 27-May 12

Re: retrieve values from tables having one to many relationship

Posted 13 December 2012 - 11:40 PM

I think I should post the entire code for search button because the else part does not seem to work too.
 Private Sub SearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchButton.Click
        Dim sqlcon As New SqlConnection(My.Settings.Hardware_DatabaseConnectionString)
        sqlcon.Open()
        Dim datestr As String
        Dim sqlcmdsearch As New SqlCommand("SELECT Prd_type from Product_Table where Prd_Sr_Key_No='" & SrKeyNoTextBox.Text & "'", sqlcon)
        Dim sdrsearch As SqlDataReader

        Dim sqlcmdsearchcust As New SqlCommand("SELECT Product_Table.*,Customer_Table.* from Product_Table inner join (Customer_Table inner join Prd_Cust_Table on Customer_Table.C_Code=Prd_Cust_Table.C_Code) on Product_Table.Prd_Sr_Key_No = Prd_Cust_Table.Prd_Sr_Key_No where Product_Table.Prd_Sr_Key_No='" & SrKeyNoTextBox.Text & "'", sqlcon)
        Dim sdrsearchcust As SqlDataReader

        Dim sqlcmdsearchvend As New SqlCommand("SELECT Product_Table.*,Vendor_Table.* from Product_Table inner join (Vendor_Table inner join Prd_Vend_Table on Vendor_Table.V_Code=Prd_Vend_Table.V_Code) on Product_Table.Prd_Sr_Key_No=Prd_Vend_Table.Prd_Sr_Key_No where Product_table.Prd_Sr_Key_No='" & SrKeyNoTextBox.Text & "'", sqlcon)
        Dim sdrsearchvend As SqlDataReader

        Dim sqlcmdri As New SqlCommand("SELECT * from Product_Table inner join Service_Centre_Table on Product_Table.S_Code=Service_Centre_Table.S_Code where Product_Table.Prd_Sr_Key_No='" & SrKeyNoTextBox.Text & "'", sqlcon)
        Dim sdrri As SqlDataReader

        Dim sqlcmdother As New SqlCommand("SELECT * from Product_Table where Prd_Sr_Key_No='" & SrKeyNoTextBox.Text & "'", sqlcon)
        Dim sdrother As SqlDataReader


        sdrsearch = sqlcmdsearch.ExecuteReader()
        If sdrsearch.HasRows Then
            While sdrsearch.Read
                ProductTypeComboBox.Text = sdrsearch.Item("Prd_Type")
            End While
        End If
        sdrsearch.Close()
        If ProductTypeComboBox.Text = "REPAIR(INHOUSE)" Then
            sdrsearchcust = sqlcmdsearchcust.ExecuteReader()
            If sdrsearchcust.HasRows Then
                While sdrsearchcust.Read
                    ProductNameTextBox.Text = sdrsearchcust.Item("Prd_Name")
                    BrandNameComboBox.Text = sdrsearchcust.Item("Prd_Brand")
                    ProductTypeComboBox.Text = sdrsearchcust.Item("Prd_Type")
                    DetailRichTextBox.Text = sdrsearchcust.Item("Prd_Detail")

                    datestr = sdrsearchcust.Item("Dte_Stck_In")
                    Dim splitstr As String() = datestr.Split("/")
                    ddComboBox.Text = splitstr(0)
                    mmComboBox.Text = splitstr(1)
                    yyyyComboBox.Text = splitstr(2)

                    BroughtByTextBox.Text = sdrsearchcust.Item("Brght_By")
                    NoteRichTextBox.Text = sdrsearchcust.Item("Note")

                    CustomerCodeTextBox.Text = sdrsearchcust.Item("C_Code")
                    CustomerNameTextBox.Text = sdrsearchcust.Item("C_Name")
                    CustomerAddressRichTextBox.Text = sdrsearchcust.Item("C_Address")
                    CustomerContactTextBox.Text = sdrsearchcust.Item("C_Cntct")
                    CustomerContactPersonTextBox.Text = sdrsearchcust.Item("C_Cntct_Prsn")
                End While
            End If
            sdrsearchcust.Close()

        ElseIf ProductTypeComboBox.Text = "PURCHASE" Then
            sdrsearchvend = sqlcmdsearchvend.ExecuteReader()
            If sdrsearchvend.HasRows Then
                While sdrsearchvend.Read
                    ProductNameTextBox.Text = sdrsearchvend.Item("Prd_Name")
                    BrandNameComboBox.Text = sdrsearchvend.Item("Prd_Brand")
                    ProductTypeComboBox.Text = sdrsearchvend.Item("Prd_Type")
                    DetailRichTextBox.Text = sdrsearchvend.Item("Prd_Detail")

                    datestr = sdrsearchvend.Item("Dte_Stck_In")
                    Dim splitstr As String() = datestr.Split("/")
                    ddComboBox.Text = splitstr(0)
                    mmComboBox.Text = splitstr(1)
                    yyyyComboBox.Text = splitstr(2)

                    BroughtByTextBox.Text = sdrsearchvend.Item("Brght_By")
                    NoteRichTextBox.Text = sdrsearchvend.Item("Note")

                    VendorCodeTextBox.Text = sdrsearchvend.Item("V_Code")
                    VendorNameComboBox.Text = sdrsearchvend.Item("V_Name")
                    VendorAddressRichTextBox.Text = sdrsearchvend.Item("V_Address")
                    VendorContactTextBox.Text = sdrsearchvend.Item("V_Cntct")
                    VendorContactPersonTextBox.Text = sdrsearchvend.Item("V_Cntct_Prsn")
                End While
                sdrsearchvend.Close()
            ElseIf ProductTypeComboBox.Text = "REPAIRED(INWARD)" Then
                sdrri = sqlcmdri.ExecuteReader()
                If sdrri.HasRows Then
                    While sdrri.Read
                        ProductNameTextBox.Text = sdrri.Item("Prd_Name")
                        BrandNameComboBox.Text = sdrri.Item("Prd_Brand")
                        ProductTypeComboBox.Text = sdrri.Item("Prd_Type")
                        DetailRichTextBox.Text = sdrri.Item("Prd_Detail")
                        datestr = sdrri.Item("Dte_Stck_In")
                        Dim splitstr As String() = datestr.Split("/")
                        ddComboBox.Text = splitstr(0)
                        mmComboBox.Text = splitstr(1)
                        yyyyComboBox.Text = splitstr(2)

                        BroughtByTextBox.Text = sdrri.Item("Brght_By")
                        NoteRichTextBox.Text = sdrri.Item("Note")

                        ServiceCentreCodeTextBox.Text = sdrri.Item("S_Code")
                        ServiceCentreNameComboBox.Text = sdrri.Item("S_Name")
                        ServiceCentreAddressRichTextBox.Text = sdrri.Item("S_Address")
                        ServiceCentreContactTextBox.Text = sdrri.Item("S_Cntct")
                        ServiceCentreContactPersonTextBox.Text = sdrri.Item("S_Cntct_Prsn")
                    End While
                End If
                sdrri.Close()
            Else
                sdrother = sqlcmdother.ExecuteReader
                If sdrother.HasRows Then
                    While sdrother.Read
                        ProductNameTextBox.Text = sdrother.Item("Prd_Sr_Key_No")
                        BrandNameComboBox.Text = sdrother.Item("Prd_Brand")
                        ProductTypeComboBox.Text = sdrother.Item("Prd_Type")
                        DetailRichTextBox.Text = sdrother.Item("Prd_Detail")
                        BroughtByTextBox.Text = sdrother.Item("Brght_By")
                        If IsDBNull("Note") Then
                            NoteRichTextBox.Text = ""
                        Else
                            NoteRichTextBox.Text = sdrother.Item("Note")
                        End If
                    End While
                End If
            End If
        End If
        'end of if for product type 
    End Sub


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1