Bind DataGrid to textbox

[OMRebel]

Hey everyone. I'm working on an application that uses an Access database. On one of my forms, I am wanting to set up a textbox to where when the user enters in a charge number, that the datagrid below it will highlight the matching record. The textbox that I am using is named txtCharge. The datagrid is just named DataGridView1. I created the datagrid by just dragginthe datasources over to my windows form, and using the table tblCharges. The field that is being entered into the textbox that I want to tie to the datagrid is the Code field. How can this be done?

Also, if anyone has suggestions on cleaning up my code, I'd appreciate that as well.

Here is the code for my form:

Imports System.Data

Public Class frmTrans

    ' Setup Connection Object; con will hold the connection object
    Dim con As New OleDb.OleDbConnection

    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter    ' Data adapter
    Dim sql As String

    Dim MaxRows As Integer
    Dim inc As Integer

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        Me.Close()
    End Sub


    Private Sub frmTrans_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'TODO: This line of code loads data into the 'ProfeesDataSet.tblTrans' table. You can move, or remove it, as needed.
        Me.TblTransTableAdapter.Fill(Me.ProfeesDataSet.tblTrans)
        'TODO: This line of code loads data into the 'ProfeesDataSet.tblPatient' table. You can move, or remove it, as needed.
        Me.TblPatientTableAdapter.Fill(Me.ProfeesDataSet.tblPatient)
        'TODO: This line of code loads data into the 'ProfeesDataSet.tblInsurance' table. You can move, or remove it, as needed.
        Me.TblInsuranceTableAdapter.Fill(Me.ProfeesDataSet.tblInsurance)
        'TODO: This line of code loads data into the 'ProfeesDataSet.tblPhys' table. You can move, or remove it, as needed.
        Me.TblPhysTableAdapter.Fill(Me.ProfeesDataSet.tblPhys)
        'TODO: This line of code loads data into the 'ProfeesDataSet.tblCharges' table. You can move, or remove it, as needed.
        Me.TblChargesTableAdapter.Fill(Me.ProfeesDataSet.tblCharges)

    End Sub

    Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click

        ' Validating that Account number has been entered.
        If txtAccount.Text = "" Then
            MsgBox("Please Enter Account Number")
            Exit Sub
            txtAccount.Focus()
        End If
        ' Validating that the Charge number has been entered
        If txtCharge.Text = "" Then
            MsgBox("Please Enter Charge Number")
            Exit Sub
            txtCharge.Focus()
        End If

        ' Setup the Connection String; con is the connection string
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = c:\ProFees\profees.mdb"

        ' Open the connection to the database
        con.Open()

        sql = "Select * From tblTrans"
        ' Create the data adapter
        da = New OleDb.OleDbDataAdapter(sql, con)
        ' Fill the DataSet from the data adapter
        da.Fill(ds, "ProFees")      ' ProFees is the identifier.  Does not have to be name of table.

        ' Close the connection to the database
        con.Close()

        MaxRows = ds.Tables("ProFees").Rows.Count   ' Stores how many rows are in the DataSet
        inc = MaxRows

        If inc <> -1 Then       ' Make sure there is a valid record to add
            Dim cb As New OleDb.OleDbCommandBuilder(da) ' Command Builder
            Dim dsNewRow As DataRow ' DataRow Object - needed for adding new rows to DataSet
            dsNewRow = ds.Tables("ProFees").NewRow()  ' Creates the new DataRow object, stores it in dsNewRow variable

            '***********************************
            ' Update the Dataset
            dsNewRow.Item("Account") = txtAccount.Text
            dsNewRow.Item("AllowedAmnt") = txtAllowed.Text
            dsNewRow.Item("PaymentAmnt") = txtPayment.Text
            dsNewRow.Item("PatPortion") = txtPatPortion.Text
            dsNewRow.Item("PatPaid") = txtPatPaid.Text
            dsNewRow.Item("Charge") = txtCharge.Text
            dsNewRow.Item("DOS") = dtpDOS.Value.ToShortDateString
            dsNewRow.Item("Post") = dtpPost.Value.ToShortDateString
            dsNewRow.Item("FaceSheet") = chkFaceSheet.CheckState
            dsNewRow.Item("Insurance") = cboInsurance.Text
            dsNewRow.Item("Physician") = cboPhysician.Text
            '***********************************
            ds.Tables("ProFees").Rows.Add(dsNewRow)  ' This method adds the Row to the DataSet
            da.Update(ds, "ProFees")  ' DataAdapter update
            'MsgBox("New Record added to the Database")
        End If

        '***********************************
        ' Udate the DataBase from the DataSet
        ds.Tables("ProFees").Rows(inc).Item("Account") = txtAccount.Text
        ds.Tables("ProFees").Rows(inc).Item("AllowedAmnt") = txtAllowed.Text
        ds.Tables("ProFees").Rows(inc).Item("PaymentAmnt") = txtPayment.Text
        ds.Tables("ProFees").Rows(inc).Item("PatPortion") = txtPatPortion.Text
        ds.Tables("ProFees").Rows(inc).Item("PatPaid") = txtPatPaid.Text
        ds.Tables("ProFees").Rows(inc).Item("Charge") = txtCharge.Text
        ds.Tables("ProFees").Rows(inc).Item("DOS") = dtpDOS.Value.ToShortDateString
        ds.Tables("ProFees").Rows(inc).Item("Post") = dtpPost.Value.ToShortDateString
        ds.Tables("ProFees").Rows(inc).Item("FaceSheet") = chkFaceSheet.CheckState
        ds.Tables("ProFees").Rows(inc).Item("Insurance") = cboInsurance.Text
        ds.Tables("ProFees").Rows(inc).Item("Physician") = cboPhysician.Text
        '***********************************
        da.Update(ds, "ProFees")    ' This line here is what actually updates the database!!!!
        MsgBox("Data updated")

        ' Clear labels
        txtAccount.Text = ""
        txtAllowed.Text = ""
        txtPayment.Text = ""
        txtPatPortion.Text = ""
        txtPatPaid.Text = ""
        txtCharge.Text = ""
        dtpDOS.Value = Today
        dtpPost.Value = Today

        ' Set focus back to txtAccount
        txtAccount.Focus()

    End Sub

End Class

 

[cjard]

Rather than highlighting the matching record, would you settle for having just that record show?

If so, you can set the filter property of the datagridview to soemthing like:

.Filter = String.Format("'' = '{0}' OR code = '{0}'", txtCharge.Text)

 

[OMRebel]

Thanks for the reply. That would certainly work. However, where do I find the "filter property" for the datagrid? I looked on the properties for the datagrid, and didn't see it anywhere. Sorry if this is a dumb question. I'm just now learning VB.

 

[OMRebel]

Filtering worked. I ended up creating a seperate dataset to grab the data for the grid. Then, on the textchanged for the text box, I appended the sql to include "Code = " & txtCharge.Text, so that it would then filter out the other records. Pretty cool stuff. I'm certainly digging VB.net!

 

[cjard]

Note that DataGrid and DataGridView are different components, DGV being the more modern one. Your original post said:

The datagrid is just named DataGridView1

Which tol me you were using a DataGridView. Please do refer to it in future as a DataGridView or DGV so as to avoid confusion amongst your fellow programmers as to whether youre using a DataGrid or a DataGridView

 

[cjard]

I appended the sql to include "Code = " & txtCharge.Text

You mean you used DataGridView1.Filter in the manner that I suggested, or that you rewrote the SQL query and refilled the datatable? Either way, I'm glad you solved the problem. Here are some things to watch for:

Code in this case is numeric. If your user enters non-number characters in the field then the query or filter may fail..

If you are repateing the SQL you should endeavour to use a parameterised query to improve efficiency and reduce the risk of SQL injection hacking


For more information please ask;