locked
issue in datagridview search RRS feed

  • Question

  • dear all i have a datagridview with 5 columns

    whenever i use below code to retrive data using textbox,it retrive data with column name. i need data under my columns 

    my code are 

    Private Function PopulateDataGridView() As DataTable
            Dim query As String = "SELECT documentno, documenttype, documentname,expirydate,attachment FROM documentattachment"
            query &= " WHERE supplierid LIKE '%' + @supplierid + '%'"
            query &= " OR @supplierid = ''"
            Dim constr As String = appx.myconnection
            Using con As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand(query, con)
                    cmd.Parameters.AddWithValue("@supplierid", SupplieridTextBox1.Text.Trim())
                    Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                        Dim dt As DataTable = New DataTable()
                        sda.Fill(dt)
                        Return dt
                    End Using
                End Using
            End Using
        End Function

     Private Sub SupplieridTextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SupplieridTextBox1.TextChanged
            DataGridView1.DataSource = Me.PopulateDataGridView()
    
    
        End Sub

    Please help 

    Wednesday, June 10, 2020 6:58 AM

All replies

  • Hi Omi4u,

    Thank you for posting here.

    According to your description, I have a question to confirm with you.

    >>it retrive data with column name. i need data under my columns 

    Could you describe more clearly what kind of data you want to select from your database? It will help us analzye your problem and make a test.

    Please provide more details about your database.

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 11, 2020 2:36 AM
  • Please check above clip

    in it i made 5 columns head. Document No,Document name,Document Type,Attachment and Expirydate.

    Now if i search any specific record,

    if showing the record but with auto made columns name ..

    Thursday, June 11, 2020 7:11 AM
  • Did you try setting DataGridView1.AutoGenerateColumns to False?

    Thursday, June 11, 2020 9:32 AM
  • i tried autogeneratecolumns but after that it not showing my data, only column names
    Thursday, June 11, 2020 12:25 PM
  • Hi Omi4u,

    Thanks for your feedback.

    >>it not showing my data, only column names

    It seems that your query statement is incorrect, so you cannot retrive data from your database. 

    What conditions do you want your 'supplierid' to meet?

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 15, 2020 6:28 AM
  • Dear Xingyu

    Sorry for a late reply actually i was not well and was on leave.

    well i have 2 tables one is supplier and other is supplierdocx

    now both tables are in relation to each other using supplierid. 

    now what i need is whenever user type supplier id  suppose : ABC Company

    he must get information from both tables like 

    in textboxes user must get details from supplier table and in datagridview user must get details from supplierdocx.

    picture attach

    Tuesday, June 23, 2020 5:28 AM
  • Hi Omi4u,

    In order to select data from two tables, you can try following query statement:

    "SELECT * FROM supplier s JOIN supplierdocx d ON s.supplierid=d.supplierid WHERE s.supplierid = @id"

    Here's the code you can refer to:

            Dim dt As New DataTable
            Dim cmdText = "SELECT * FROM supplier s JOIN supplierdocx d ON s.supplierid=d.supplierid WHERE s.supplierid = @id"
            Using cmd As New SqlCommand(cmdText, conn)
                cmd.Parameters.AddWithValue("@id", Convert.ToInt32(TextBox1.Text))
                Dim reader = cmd.ExecuteReader
                dt.Load(reader)
            End Using

    Then you can load the data into textboxes from the datatable. 

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 24, 2020 9:59 AM
  • ok but i m using below code to retrive data from database.. it giving details in related textbox so how i can use your code in my code to get data in textboxes and datagridview

      query1 = "SELECT * FROM supplier WHERE supplierid ='" & SupplieridTextBox.Text & "'"
            retrieveSingleResult(query1)
            If dt.Rows.Count > 0 Then
                With dt.Rows(0)
                    '    SupplieridTextBox.Text = .Item("supplierid")
                    SuppliernameTextBox.Text = .Item("suppliername")
                    EntrydateDateTimePicker.Text = .Item("entrydate")
                    CompanynameTextBox.Text = .Item("companyname")
                    Address1TextBox.Text = .Item("address1")
                    Address2TextBox.Text = .Item("address2")
                    CityTextBox.Text = .Item("city")
                    CountryTextBox.Text = .Item("country")
                    Contact1TextBox.Text = .Item("contact1")
                    Contact2TextBox.Text = .Item("contact2")
                    ContactpersonTextBox.Text = .Item("contactperson")
                    EmailTextBox.Text = .Item("email")
                    WebsiteTextBox.Text = .Item("website")
                    BrokernameTextBox.Text = .Item("brokername")
                    BrokeragepercentageTextBox.Text = .Item("brokeragepercentage")
                    AccountnameTextBox.Text = .Item("accountname")
                    AccountnumberTextBox.Text = .Item("accountnumber")
                    IbannoTextBox.Text = .Item("ibanno")
                    BanknameTextBox.Text = .Item("bankname")
                    BranchnameTextBox.Text = .Item("branchname")
                    UsernameTextBox.Text = .Item("username")
                    TextBox14.Text = .Item("isactive")
    
                    CreditlimitTextBox.Text = .Item("creditlimit")
    
    
                    If TextBox14.Text = "False" Then
                        IsactiveCheckBox.Checked = False
                    ElseIf TextBox14.Text = "True" Then
                        IsactiveCheckBox.Checked = True
    
                    End If
                End With
            End If
     Public Sub retrieveSingleResult(ByVal query As String)
    
            Try
                coni.Open()
                cmd = New SqlCommand
    
                With cmd
                    .Connection = coni
                    .CommandText = query
    
    
    
                    '"SELECT itemID FROM itemx  WHERE itemID='" & Form2.txtPROCODE.Text & "'"
                End With
    
                da = New SqlDataAdapter
    
                da.SelectCommand = cmd
    
                dt = New DataTable
                da.Fill(dt)
    
                '   da.Fill(ds, "itemx")
    
            Catch ex As Exception
    
            End Try
            coni.Close()
    
    
        End Sub
    please tell me the way that if i write the supplier id, it must give me details of that supplier in all textboxes and in datagrid from relation table 

    Friday, June 26, 2020 7:07 AM
  • Hi Omi4u,

    Did you try the query statement in my reply above in your code?

    After changing the 'query1' string, your code works well in my test.

    Please let me know if you need further assistance.

    Best Regards,

    Xingyu Zhao

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 29, 2020 9:32 AM
  • yeah it working fine but with addition of your code in query1 i am getting data in textboxes but how to get data in datagridview columns 
    Monday, June 29, 2020 12:42 PM
  • Hi Omi4u,

    You can bind the DataTable to your DataGridView:

       DataGridView1.DataSource = dt

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 30, 2020 2:05 AM
  • i tried your code 

      query1 = "SELECT * FROM suppliermaster s JOIN documentattachment d ON s.supplierid=d.supplierid WHERE s.supplierid='" & SupplieridTextBox.Text & "'"
            retrieveSingleResult(query1)
            If dt.Rows.Count > 0 Then
                With dt.Rows(0)
                    '    SupplieridTextBox.Text = .Item("supplierid")
                    SuppliernameTextBox.Text = .Item("suppliername")
                    EntrydateDateTimePicker.Text = .Item("entrydate")
                    CompanynameTextBox.Text = .Item("companyname")
                    Address1TextBox.Text = .Item("address1")
                    Address2TextBox.Text = .Item("address2")
                    CityTextBox.Text = .Item("city")
                    CountryTextBox.Text = .Item("country")
                    Contact1TextBox.Text = .Item("contact1")
                    Contact2TextBox.Text = .Item("contact2")
                    ContactpersonTextBox.Text = .Item("contactperson")
                    EmailTextBox.Text = .Item("email")
                    WebsiteTextBox.Text = .Item("website")
                    BrokernameTextBox.Text = .Item("brokername")
                    BrokeragepercentageTextBox.Text = .Item("brokeragepercentage")
                    AccountnameTextBox.Text = .Item("accountname")
                    AccountnumberTextBox.Text = .Item("accountnumber")
                    IbannoTextBox.Text = .Item("ibanno")
                    BanknameTextBox.Text = .Item("bankname")
                    BranchnameTextBox.Text = .Item("branchname")
                    UsernameTextBox.Text = .Item("username")
                    TextBox14.Text = .Item("isactive")
    
                    CreditlimitTextBox.Text = .Item("creditlimit")
    
                    DataGridView1.DataSource = dt

    but it showing all supplier data with supplierdocx data in datagrid but i only want to see supplierdocx data in datagridview

    Tuesday, June 30, 2020 10:19 AM
  • Hi Omi4u,

    >>i only want to see supplierdocx data in datagridview

    You can create a new DataTable and bind the DataGridView to the new DataTable.

    I make a test on my side and you can refer to the following code:

            query1 = "SELECT * FROM suppliermaster s JOIN documentattachment d ON s.supplierid=d.supplierid WHERE s.supplierid='" & SupplieridTextBox.Text & "'"
            retrieveSingleResult(query1)
    
            Dim table As DataTable = New DataTable()
            table.Columns.Add("supplierdocxColumn1", GetType(String))
            table.Columns.Add("supplierdocxColumn2", GetType(String))
            table.Columns.Add("supplierdocxColumn3", GetType(String))
            table.Columns.Add("supplierdocxColumn4", GetType(String))
            '...
    
            If dt.Rows.Count > 0 Then
                With dt.Rows(0)
    
                    Dim dataRow As DataRow = table.NewRow()
                    dataRow.Item("supplierdocxColumn1") = .Item("supplierdocxColumn1")
                    dataRow.Item("supplierdocxColumn2") = .Item("supplierdocxColumn2")
                    dataRow.Item("supplierdocxColumn3") = .Item("supplierdocxColumn3")
                    dataRow.Item("supplierdocxColumn4") = .Item("supplierdocxColumn4")
                    '...
                    table.Rows.Add(dataRow)
                    DataGridView1.DataSource = table
    
                    SuppliernameTextBox.Text = .Item("suppliername")
                    EntrydateDateTimePicker.Text = .Item("entrydate")
                    '...
    
                End With
            End If

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 1, 2020 2:00 AM
  • dear xingyu 

    thank you for your code. after using your code i added new datagridview (to test your code)

    and it making columns but not showing any data in it. 

    please check below code and attachment

        query1 = "SELECT * FROM suppliermaster s JOIN documentattachment d ON s.supplierid=d.supplierid WHERE s.supplierid='" & SupplieridTextBox.Text & "'"
            retrieveSingleResult(query1)
         
                    '    SupplieridTextBox.Text = .Item("supplierid")
               
    
                    Dim table As DataTable = New DataTable()
                    table.Columns.Add("Document No", GetType(String))
                    table.Columns.Add("Document Name", GetType(String))
                    table.Columns.Add("Document Type", GetType(String))
                    table.Columns.Add("Expiry Date", GetType(String))
                    table.Columns.Add("Attachment", GetType(String))
    
                    If dt.Rows.Count > 0 Then
                        With dt.Rows(0)
    
                            Dim dataRow As DataRow = table.NewRow()
                    dataRow.Item("Document No") = .Item("DocumentNo")
                    dataRow.Item("Document Name") = .Item("DocumentName")
                    dataRow.Item("Document Type") = .Item("DocumentType")
                    dataRow.Item("Expiry Date") = .Item("ExpiryDate")
                            dataRow.Item("Attachment") = .Item("Attachment")
    
                            table.Rows.Add(dataRow)
                    DataGridView2.DataSource = table
    
                            SuppliernameTextBox.Text = .Item("suppliername")
                            EntrydateDateTimePicker.Text = .Item("entrydate")
                            CompanynameTextBox.Text = .Item("companyname")
                            Address1TextBox.Text = .Item("address1")
                            Address2TextBox.Text = .Item("address2")
                            CityTextBox.Text = .Item("city")
                            CountryTextBox.Text = .Item("country")
                            Contact1TextBox.Text = .Item("contact1")
                            Contact2TextBox.Text = .Item("contact2")
                            ContactpersonTextBox.Text = .Item("contactperson")
                            EmailTextBox.Text = .Item("email")
                            WebsiteTextBox.Text = .Item("website")
                            BrokernameTextBox.Text = .Item("brokername")
                            BrokeragepercentageTextBox.Text = .Item("brokeragepercentage")
                            AccountnameTextBox.Text = .Item("accountname")
                            AccountnumberTextBox.Text = .Item("accountnumber")
                            IbannoTextBox.Text = .Item("ibanno")
                            BanknameTextBox.Text = .Item("bankname")
                            BranchnameTextBox.Text = .Item("branchname")
                            UsernameTextBox.Text = .Item("username")
                            TextBox14.Text = .Item("isactive")
    
                            CreditlimitTextBox.Text = .Item("creditlimit")
    
    
                        End With
                    End If

    Wednesday, July 1, 2020 5:09 AM
  • Hi Omi4u,

    You need to make sure that the value of 'DocumentNo', 'DocumentName' ...  is not null or empty in your database when you specify the corresponding 'supplierid'.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, July 1, 2020 7:03 AM
  • no sir it is not null.

    it has value

    Wednesday, July 1, 2020 7:38 AM
  • Hi Omi4u,

    Set breakpoint on the line 'Dim dataRow As DataRow = table.NewRow()' and then step through code to figure out whether the value of '.Item("DocumentNo")', '.Item("DocumentName")' ...  is empty. 

    Best Regards,

    Xingyu Zhao 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, July 1, 2020 9:43 AM
  • check the video

    i have a record no SUP-2000-001

    it showing 2 files saved with it in relation table

    now when i double click it to check the details it showing me details in textboxes as per your code

    but not in 2nd datagridview. (ignore the 1st datagridview with white background)

    this is my issue im facing.

    Friday, July 3, 2020 5:40 AM
  • Waiting for reply 
    Wednesday, July 8, 2020 10:18 AM
  • Hi Omi4u,

    Based on your picture, you successfully get the value of 'Docuement No' and 'Document Type', so it is most likely that there are no values of 'Document Name', 'Expiry Date' and 'Attachment' in the database when 'Supplier ID' is 'SUP-2020-0001'.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 10, 2020 8:51 AM
  • hi xingyu 

    how are you. No actually in second clip ( after i click the record ) it not retrieving all fields. i have data in document name but it not showing. 

    can u please do me a favor and write a code for me in other way or anything else that can help me please im very badly stuck at this point . 

    Saturday, July 11, 2020 6:09 AM