Question Output datagridview results to textbox

[willdev]

Hi, simple question I think!

I would like to make it so that when, you select the row on the datagridview the properties of the row are in putted into textboxes?

Thanks

 

[jmcilhinney]

Is your grid bound? If so then you simply bind the TextBoxes to the same data source. That way there's no code required.

 

[willdev]

I don't think so, The code I have for the datagrid is:

Me.DataGridView1.DataSource = pa.Tables(0)

pa I have previously created dataset

Cheers

 

[jmcilhinney]

Setting the DataSource is exactly how you data-bind a DataGridView. As such, you can just bind your TextBoxes to the same DataTable, e.g.

Dim table As DataTable = pa.Tables(0)

Me.DataGridView1.DataSource = table
Me.TextBox1.DataBindings.Add("Text", table, "SomeColumn")

you can bind as many TextBoxes as you need in the same way, just changing the column name as appropriate.

 

[willdev]

Thank you so much! Your a legend so much more helpful compared to other forums.

Right I think it's working however If I press the button twice I get the error:

This causes two bindings in the collection to bind to the same property.
Parameter name: binding

Code:

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim dv As New DataView(pa.Tables(0))
            dv.RowFilter = "Forename ='" & txtFirstName.Text & "'"
            dv.RowFilter = "Surname ='" & txtLastName.Text & "'"
            dv.RowFilter = "First_Line_Adr='" & txtFirst_Line_Adr.Text & "'"
            Dim table As DataTable = pa.Tables(0)
            txtLastName.DataBindings.Add("Text", table, "Surname")
            DataGridView1.DataSource = dv
            DataGridView1.Refresh()

    End Sub

Cheers

 

[jmcilhinney]

First up, you are not binding the same data to the grid and the TextBox. You're binding the DataTable to the TextBox and a DataView to the grid. You need to use the same data source both times.

Also, there's no need to create a DataView explicitly because every DataTable already has a DataView associated with it via its DefaultView property. When you bind a DataTable, the data comes from the DeafultView anyway, so there's no point binding the DefaultView directly.

Another issue is that you are setting the RowFilter three times, so each time you are replacing what went before, not adding to it. If you want to filter the data using three conditions then you must combine those three conditions.

Your filter doesn't really make sense though, because you are filtering the data using the contents of a TextBox that you then bind that same data to.

Finally, that call to Refresh has nothing to do with the data and is therefore of no use.

As for the question, why are you binding on that button click? You only need to bind the data once so why do it using a Button that the user can click multiple times? Why not bind the data when you get the data in the first place?

 

[willdev]

Thank you so much for this extremely helpful reply!

How would do I define the deafultview property? I'm using three different conditions as I couldn't get the "or" statement to work properly . What I am wanting to achieve is that when the user enters either firstname, last name and or first_line_adr when the user presses the button, the information is searched within the datagrid view and outputted in the datagrid view and it also fills the rest of the information to the textboxes (firstname, last name etc).

Thanks again!

Will

 

[jmcilhinney]

You don't define the DefaultView property. It is already defined. You just go ahead and use it, just as you might use the Text property of a TextBox.

If you want to OR multiple conditions then it's just a single String with the word "OR" between the conditions, e.g.

myDataTable.DefaultView.RowFilter = String.Format("Column1 = '{0}' OR Column2 = '{1}'", value1, value2)

It still doesn't make sense to me to enter filter values and then display the filtered data in the same TextBoxes. That sounds like a very unintuitive UI.

 

[willdev]

pa is actually a dataset I've just relised. I have changed the code to this:

    dv.RowFilter = String.Format("Forename = '{0}' OR Surname = '{1}'", txtFirstName, txtLastName)

However, when I execute it the datagridview shows nothing

 

[jmcilhinney]

I suggest that you have a look at the actual value you're assigning to the RowFilter property.

 

[willdev]

Ok I have worked harder at it this time and it now works to some extent:

   Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim table As DataTable = pa.Tables(0)
        table.DefaultView.RowFilter = String.Format("Forename = '{0}' OR Surname = '{1}'", txtFirstName.Text, txtLastName.Text)
            txtLastName.DataBindings.Add("Text", table, "Surname")
        DataGridView1.DataSource = table
    End Sub

However when I try to select the whole table again it only shows what I have just searched.

This is how I refresh:

Private Sub txtRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtRefresh.Click
        PatientFields()
    End Sub

Dim pa As New DataSet()
    Dim intCurrentIndex As Integer = 0
    Dim pat As New MySqlDataAdapter()
    Dim conn As New MySqlConnection("server=localhost" & ";user id=" & Login.txtUsername.Text & "; password=" & Login.txtPassword.Text & "; port=3306; database=test; pooling=false")




    Private Sub PatientFields()
        txtFirstName.Text = pa.Tables(0).Rows(intCurrentIndex).Item("Forename").ToString()
        txtLastName.Text = pa.Tables(0).Rows(intCurrentIndex).Item("Surname").ToString()
        txtFirst_Line_Adr.Text = pa.Tables(0).Rows(intCurrentIndex).Item("First_Line_Adr").ToString()
        txtPatientID.Text = pa.Tables(0).Rows(intCurrentIndex).Item("Patient_Number").ToString()
        txtSecond_Line_Adr.Text = pa.Tables(0).Rows(intCurrentIndex).Item("Second_Line_Adr").ToString()
        txtCity.Text = pa.Tables(0).Rows(intCurrentIndex).Item("City").ToString()
        txtPostcode.Text = pa.Tables(0).Rows(intCurrentIndex).Item("Postcode").ToString()
        txtTelephone_Number.Text = pa.Tables(0).Rows(intCurrentIndex).Item("Telephone_Number").ToString()
        txtExemptions.Text = pa.Tables(0).Rows(intCurrentIndex).Item("Exemptions").ToString()
        Me.DataGridView1.DataSource = pa.Tables(0)
    End Sub

    Public Sub PatientSQL()
        pat.SelectCommand = New MySqlCommand("SELECT * FROM Patient")
        pat.SelectCommand.Connection = conn


        pat.UpdateCommand = New MySqlCommand("UPDATE patient SET Forename  = ?Forename , Surname  = ?Surname , First_Line_Adr  =?First_Line_Adr , Second_Line_Adr =?Second_Line_Adr, City =?City, Postcode =?Postcode, Telephone_Number =?Telephone_Number, Exemptions =?Exemptions WHERE Patient_Number = ?Patient_Number")
        pat.UpdateCommand.Connection = conn
        pat.UpdateCommand.Parameters.Add("?Forename", MySqlDbType.VarChar, 40, "Forename")
        pat.UpdateCommand.Parameters.Add("?Surname", MySqlDbType.VarChar, 40, "Surname")
        pat.UpdateCommand.Parameters.Add("?First_Line_Adr", MySqlDbType.VarChar, 40, "First_Line_Adr")
        pat.UpdateCommand.Parameters.Add("?Patient_Number", MySqlDbType.Int16, 5, "Patient_Number") '.SourceVersion = pattaRowVersion.Original
        pat.UpdateCommand.Parameters.Add("?Second_Line_Adr", MySqlDbType.VarChar, 40, "Second_Line_Adr")
        pat.UpdateCommand.Parameters.Add("?City", MySqlDbType.VarChar, 40, "City")
        pat.UpdateCommand.Parameters.Add("?Postcode", MySqlDbType.VarChar, 40, "Postcode")
        pat.UpdateCommand.Parameters.Add("?Telephone_Number", MySqlDbType.VarChar, 40, "Telephone_Number")
        pat.UpdateCommand.Parameters.Add("?Exemptions", MySqlDbType.Char, 1, "Exemptions")

        pat.InsertCommand = New MySqlCommand("INSERT INTO Patient(Patient_Number, Forename , Surname , First_Line_Adr , Second_Line_Adr, City, Postcode,Telephone_Number, Exemptions ) VALUES(?Patient_Number,?Forename ,?Surname , ?First_Line_Adr , ?Second_Line_Adr, ?City, ?Postcode, ?Telephone_Number, ?Exemptions )")
        pat.InsertCommand.Connection = conn
        pat.InsertCommand.Parameters.Add("?Forename", MySqlDbType.VarChar, 40, "Forename")
        pat.InsertCommand.Parameters.Add("?Surname", MySqlDbType.VarChar, 40, "Surname")
        pat.InsertCommand.Parameters.Add("?First_Line_Adr", MySqlDbType.VarChar, 40, "First_Line_Adr")
        pat.InsertCommand.Parameters.Add("?Second_Line_Adr", MySqlDbType.VarChar, 40, "Second_Line_Adr")
        pat.InsertCommand.Parameters.Add("?Patient_Number", MySqlDbType.Int16, 5, "Patient_Number")
        pat.InsertCommand.Parameters.Add("?City", MySqlDbType.VarChar, 40, "City")
        pat.InsertCommand.Parameters.Add("?Postcode", MySqlDbType.VarChar, 40, "Postcode")
        pat.InsertCommand.Parameters.Add("?Telephone_Number", MySqlDbType.VarChar, 40, "Telephone_Number")
        pat.InsertCommand.Parameters.Add("?Exemptions", MySqlDbType.Char, 1, "Exemptions")

   

        pat.DeleteCommand = New MySqlCommand("DELETE FROM Patient WHERE Patient_Number = ?Patient_Number")
        pat.DeleteCommand.Connection = conn
        pat.DeleteCommand.Parameters.Add("?Patient_Number", MySqlDbType.Int16, 5, "Patient_Number")



        pat.Fill(pa)

        If pa.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            PatientFields()
        End If
    End Sub

Thanks

 

[jmcilhinney]

If you want to remove the filter then you set the RowFilter to Nothing or an empty string.

 

[willdev]

How would I filter integers?

I tried this but it produces an error:

 table.DefaultView.RowFilter = String.Format("Patient_Number = '{0}'", txtPatientID.Text)

 

[jmcilhinney]

This is the documentation for the RowFilter property:

DataView.RowFilter Property (System.Data)

It directs you here for more information:

DataColumn.Expression Property (System.Data)

Always read the relevant documentation first. Often, as in this case, you won't need to ask question after having done so.