none
find and populate datagridview with textboxes RRS feed

  • Question

  • dear all

    please check the attached image . i have a module for leather stock . now i have two tables 

    leatherstock and leatherstock_join            in ms sql.

    now what i need is whenever user enter LSID no or Lot no in circled textbox, they must get related record if available and populate in textboxes and datagridview columns. datagridview is unbound 

    i tried one code but it not working and making own columns in datagridview infect not populating textboxes . 

    here are the code . textbox1.text is to search record by using LSID no (circled)

    Public Sub FilterData(ByVal valueToSearch As String)
    
    
    
            'SELECT * From Users WHERE CONCAT(fname, lname, age) like '%F%'
            '  Dim searchQuery As String = "SELECT * From items WHERE itemname like '%" & valueToSearch & "%'"
    
            Dim searchQuery As String = "SELECT * From leatherstock WHERE LSID like '%" & valueToSearch & "%'"
            Dim command As New SqlCommand(searchQuery, connectionx)
            Dim adapter As New SqlDataAdapter(command)
            Dim table As New DataTable()
            adapter.Fill(table)
            DataGridView1.DataSource = table
            '\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    
    
    
    
    
    
    
      
    
    
        End Sub
    
    
       Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
    
    
    
            
         
            If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
    
                FilterData(TextBox1.Text)
    
            End If
    
    
        End Sub
    
    

    Saturday, June 29, 2019 7:49 AM

All replies

  • Hi

    Here is an some example code that shows the use of a datagridview in conjunction with textboxes and searching (filtering)

    This is purely an example and may or may not be helpful. It saves/loads the data to/from the DeskTop. If no data file found, it will create a bunch of dummy data. The Find box will filter the records from any field and does so in real time (not tested with huge amounts of data)

    The example needs controls as shown in image below, including a BindingNavigator1 and BindingSource1. Any number of textboxes/fields could be used as long as the code is set up for them (straightforward)

    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim dt As New DataTable("Freddy")
    	Dim started As Boolean = False
    
    	'-----------------------
    	' only for this example
    	Dim SaveTo As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.Desktop, "TestDate.xml")
    	Dim rand As New Random
    	'-----------------------
    
    	Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
    		BindingSource1.EndEdit()
    		dt.WriteXml(SaveTo)
    	End Sub
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		MinimumSize = New Size(760, 400)
    		With dt
    			.Columns.Add("ID", GetType(String))
    			.Columns.Add("Phone", GetType(String))
    			.Columns.Add("One", GetType(String))
    			.Columns.Add("Two", GetType(String))
    			.Columns.Add("Three", GetType(String))
    			.Columns.Add("Four", GetType(String))
    			.Columns.Add("Five", GetType(String))
    
    			If IO.File.Exists(SaveTo) Then
    				dt.ReadXml(SaveTo)
    			Else
    				' set up some dummy data just 
    				' for this example
    				For i As Integer = 0 To 99
    					.Rows.Add(i.ToString, rand.Next(101, 1001), rand.Next(2001, 3001), rand.Next(3001, 4001), rand.Next(4001, 5001), rand.Next(5001, 6001), rand.Next(6001, 7001))
    				Next
    			End If
    
    		End With
    
    		SetUpBS()
    		SetUpDGV()
    
    		started = True
    
    	End Sub
    	Private Sub SetUpBS()
    
    		BindingSource1.DataSource = dt
    
    		TextBox1.DataBindings.Add("Text", BindingSource1, "id")
    		TextBox2.DataBindings.Add("Text", BindingSource1, "Phone")
    
    		TextBox3.DataBindings.Add("Text", BindingSource1, "One")
    		TextBox4.DataBindings.Add("Text", BindingSource1, "Two")
    		TextBox5.DataBindings.Add("Text", BindingSource1, "Three")
    		TextBox6.DataBindings.Add("Text", BindingSource1, "Four")
    
    		BindingNavigator1.BindingSource = BindingSource1
    
    		DGV.DataSource = BindingSource1
    	End Sub
    	Sub SetUpDGV()
    		With DGV
    			' here, setup such things as
    			' column alifnments, formats etc
    			.RowHeadersVisible = False
    		End With
    	End Sub
    	Private Sub ToolStripTextBox1_TextChanged(sender As Object, e As EventArgs) Handles ToolStripTextBox1.TextChanged
    
    		Dim match As String = Trim(ToolStripTextBox1.Text)
    
    		Dim filt As String = "ID like '%" & match & "%'"
    		filt &= " Or Phone Like'%" & match & "%'"
    		filt &= " Or One Like'%" & match & "%'"
    		filt &= " Or Two Like'%" & match & "%'"
    		filt &= " Or Three Like'%" & match & "%'"
    		filt &= " Or Four Like'%" & match & "%'"
    		filt &= " Or Five Like'%" & match & "%'"
    
    		BindingSource1.Filter = filt
    
    	End Sub
    	Private Sub BindingNavigatorDeleteItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorDeleteItem.Click
    		If MessageBox.Show("Are you sure you want to DELETE this record?", "WARNING!", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.Yes Then
    			BindingSource1.RemoveCurrent()
    		End If
    	End Sub
    End Class
    


    Regards Les, Livingston, Scotland

    Saturday, June 29, 2019 1:32 PM
  • Thank you for your reply but i am using ms sql and i need something to retrieve data from ms sql.


    Wednesday, July 3, 2019 4:44 AM
  • Hi,

    see the demo,

    Imports System.Data.SqlClient
    
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
        Dim conn As SqlConnection
        Dim sda As SqlDataAdapter
        Dim cmd As SqlCommand
        Dim bind As New BindingSource
        Private Sub TextBox1_KeyPress(sender As Object, e As KeyPressEventArgs) Handles TextBox1.KeyPress
            TextBox2.DataBindings.Clear()
            TextBox3.DataBindings.Clear()
            TextBox4.DataBindings.Clear()
            TextBox5.DataBindings.Clear()
            TextBox6.DataBindings.Clear()
            Dim dt As New DataTable
            If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
                Using conn = New SqlConnection(constr)
                    conn.Open()
                    cmd = New SqlCommand("Select * From Student where id=@id", conn)
                    cmd.Parameters.AddWithValue("@id", TextBox1.Text)
                    sda = New SqlDataAdapter(cmd)
                    sda.Fill(dt)
                    bind.DataSource = dt
                    DataGridView1.DataSource = bind
                End Using
                TextBox2.DataBindings.Add(New Binding("Text", bind, "id"))
                TextBox3.DataBindings.Add(New Binding("Text", bind, "Name"))
                TextBox4.DataBindings.Add(New Binding("Text", bind, "Age"))
                TextBox5.DataBindings.Add(New Binding("Text", bind, "Uid"))
                TextBox6.DataBindings.Add(New Binding("Text", bind, "Pid"))
            End If
        End Sub
    End Class
    

    Best  Regards,

    Alex


    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, July 4, 2019 6:21 AM
    Moderator
  • thank you so much alex for your reply.. i will try your code but can u please check my question regarding COA "issue in datagridview urgent help required " that issue is getting difficult for me to solve please help me with that
    Thursday, July 4, 2019 12:46 PM
  • im getting this error while using your code 

    Cannot bind to the property 'lotno' on the target control.
    Parameter name: PropertyName

    Friday, July 5, 2019 5:28 AM
  • Hi,

    What is 'lotno'?TextBox does not have this property,I use 'Text' property.

    Best Regards,

    Alex


    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 5, 2019 8:37 AM
    Moderator
  •  Try
                LotnoTextBox.DataBindings.Clear()
    
                SuplieridTextBox.DataBindings.Clear()
                SuppliernameTextBox.DataBindings.Clear()
                ItemTextBox.DataBindings.Clear()
                DetailsTextBox.DataBindings.Clear()
    
                PerfeetTextBox.DataBindings.Clear()
    
                PerpcsTextBox.DataBindings.Clear()
    
    
                InvoicenoTextBox.DataBindings.Clear()
                WagesTextBox.DataBindings.Clear()
    
                OtherchargesTextBox.DataBindings.Clear()
                DiscountTextBox.DataBindings.Clear()
    
                TotalamountTextBox.DataBindings.Clear()
                AdvancepayTextBox.DataBindings.Clear()
    
                CommentsTextBox.DataBindings.Clear()
                GrandtotalTextBox.DataBindings.Clear()
    
    
                DaterecvdDateTimePicker.DataBindings.Clear()
                InvdateDateTimePicker.DataBindings.Clear()
                InvduedateDateTimePicker.DataBindings.Clear()
    
                Dim dt As New DataTable
                If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Return) Then
                    Using conn = New SqlConnection(constr)
                        conn.Open()
                        cmd = New SqlCommand("Select * From leatherstock where lsid=@id", conn)
                        cmd.Parameters.AddWithValue("@id", LsidTextBox.Text)
                        sda = New SqlDataAdapter(cmd)
                        sda.Fill(dt)
                        bind.DataSource = dt
                        DataGridView1.DataSource = bind
                    End Using
    
                    LotnoTextBox.DataBindings.Add(New Binding("lotno", bind, "id"))
    
                    SuplieridTextBox.DataBindings.Add(New Binding("supplierid", bind, "id"))
                    SuppliernameTextBox.DataBindings.Add(New Binding("suppliername", bind, "id"))
                    ItemTextBox.DataBindings.Add(New Binding("item", bind, "id"))
                    DetailsTextBox.DataBindings.Add(New Binding("details", bind, "id"))
    
                    PerfeetTextBox.DataBindings.Add(New Binding("perfeet", bind, "id"))
    
                    PerpcsTextBox.DataBindings.Add(New Binding("perpcs", bind, "id"))
    
                    InvoicenoTextBox.DataBindings.Add(New Binding("invno", bind, "id"))
                    WagesTextBox.DataBindings.Add(New Binding("wages", bind, "id"))
    
                    OtherchargesTextBox.DataBindings.Add(New Binding("othercharges", bind, "id"))
                    DiscountTextBox.DataBindings.Add(New Binding("discount", bind, "id"))
    
                    TotalamountTextBox.DataBindings.Add(New Binding("totalamount", bind, "id"))
                    AdvancepayTextBox.DataBindings.Add(New Binding("advancepay", bind, "id"))
    
                    CommentsTextBox.DataBindings.Add(New Binding("comments", bind, "id"))
                    GrandtotalTextBox.DataBindings.Add(New Binding("grandtotal", bind, "id"))
    
    
                    DaterecvdDateTimePicker.DataBindings.Add(New Binding("daterecvd", bind, "id"))
                    InvdateDateTimePicker.DataBindings.Add(New Binding("invdate", bind, "id"))
                    InvduedateDateTimePicker.DataBindings.Add(New Binding("invduedate", bind, "id"))
    
    
    
    
    
    
                End If
            Catch ex As Exception
                MsgBox("Contact Develpoer Team", vbCritical + vbOKOnly, "Error No LS-15225")
    
            End Try
    
    
    
    
    
    
    this is what i am trying 
    Friday, July 5, 2019 10:20 AM
  • Hi

    You didn't read Alex last reply I think where he explains what to do.

    Here is a veryy simple stand alone example that you can try separately to get to see what is needed.

    This example only illustrates two bindings - you have many more.

    ' Form1 with blank DataGridView
    ' named DGV and TextBoxes named
    ' LotnoTextBox and SuplieridTextBox.
    Option Strict On
    Option Explicit On
    Public Class Form1
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		Dim dt As New DataTable
    		With dt
    			.Columns.Add("lotno")
    			.Columns.Add("supplierid")
    			For i As Integer = 1 To 10
    				.Rows.Add(i, i * 33)
    			Next
    		End With
    		Dim bind As New BindingSource
    		bind.DataSource = dt
    		DGV.DataSource = bind
    
    		' repeat for each TextBox, using
    		' the Rext Property And the dt
    		' column Name. I use only 2 for
    		' this example.
    
    		LotnoTextBox.DataBindings.Add(New Binding("Text", bind, "lotno"))
    
    		SuplieridTextBox.DataBindings.Add(New Binding("Text", bind, "supplierid"))
    
    	End Sub
    End Class


    Regards Les, Livingston, Scotland


    • Edited by leshay Friday, July 5, 2019 12:08 PM
    Friday, July 5, 2019 12:07 PM