dcsimg
Results 1 to 2 of 2
  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2018
    Posts
    9

    Loop to copy all lines that contain the data

    I have some code that pulls info from a Access DB, however I want it to pull all lines that have the data instead this method only selects one line I have mixed amounts of rows containing the same data that I want to paste to another line gridview

    my code so far:

    Code:
    Private Sub Button1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            myConnection.Open()
            Dim str As String
            Dim i As Integer
            str = "SELECT * FROM [EWH INSTRUMENT KKS] WHERE [KKS Number] = ('" & TextBox1.Text & "')"
            Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
            dr = cmd.ExecuteReader
            While dr.Read()
                Me.DataGridView1.Rows(i).Cells("Column1").Value = dr("KKS Number")
                Me.DataGridView1.Rows(i).Cells("Column2").Value = dr("Type")
    
            End While
            myConnection.Close()
        End Sub

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    98,950

    Re: Loop to copy all lines that contain the data

    Firstly, don't use string concatenation to insert values into SQL code. It is fraught with issues, including the possibility of a malicious user deleting everything in your database in some cases. Always use parameters. To learn how, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.

    As for your issue, your query pulls back all the data and you are reading all that data. The issue is that you keep putting data into the same row in the grid every time, so each record overwrites the previous record so, when the code is done, all you see is the last record. If you expect to see all the data then you need to put each record into a different row in the grid. That means adding a new row on each iteration of the loop or, if you're populating existing rows, incrementing the row index each time.

    The issue described above is exactly the sort of thing that you can see easily if you actually debug your code, rather than just reading it. If you don't know how to debug, learn now. You would set a breakpoint at the top of the code and then step through it line by line. You could then examine all the relevant variables and other expressions as you go and you would see that you do indeed read every record and put every record in the grid, but you do it in the same row over and over.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.