Populating a GridView with query based on textBox.text

[mikejs]

Hi I have a form which populates a TextBox I want to use the textBox.text to then load a gridView with the results

something like

Private Sub TextBoxId_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBoxIdSearchExist.TextChanged

  Dim cmd As New SqlCommand("select * from pay Where t_id='" + tId.Text + "'"

        Try
            conn.Open()
            da.Fill(ds, "List")

 Dim ds As DataSet = GetData(queryString)
      If (ds.Tables.Count > 0) Then

        AuthorsGridView.DataSource = ds
        AuthorsGridView.DataBind()

      Else

        Message.Text = "Unable to connect to the database."

      End If

    End Sub

any ideas

thanks
M

[Oxiegen]

You should consider moving that code onto a button click event.
Otherwise the query will be fired for every keystroke you use in the textbox.

And you are very close to the solution, my friend. :)
Notice my lack of use of the SqlConnection object. It will work anyway, because the SqlDataAdapter will take care of connecting to the database. If provided with the connection string.

The SqlCommandBuilder will help take care of creating INSERT, UPDATE and DELETE statements for you.

Private bindingSource1 As New BindingSource

Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
   AuthorsGridView.DataSource = bindingSource1
End Sub

Private Sub TextBoxId_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBoxIdSearchExist.TextChanged
   Try
      da = New SqlDataAdapter("SELECT * FROM pay WHERE t_id = '" & tId.Text & "'", <your connection string>)
      Dim builder As New SqlCommandBuilder(da)
      da.Fill(ds, "pay") 'Needs to be the name of the source table
   
      'I don't know what GetData(queryString) is for, so I'm ignoring it
      If ds.Tables.Count > 0 Then
         bindingSource1.DataSource = ds.Tables(0)
      Else
         MessageBox.Show("No results found in database.")
      End If
   Catch ex As Exception
      MessageBox.Show("Unable to connect to database, or an error occured.")
   End Try
End Sub

[mikejs]

Awesome thanks for the help I used

Private Sub RadTextBoxid_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadTextBoxId.TextChanged
        GridView1.DataSource = bindingSource1
        Dim connString As String = My.Settings.strConn
        Dim conn As New SqlConnection(connString)
        Dim ds As New DataSet
        Dim strSQL As String = "SELECT * FROM payments WHERE t_id = '" & TextBoxId.Text & "'"
        Dim da As New SqlDataAdapter(strSQL, conn)


        Try
            conn.Open()


            Dim builder As New SqlCommandBuilder(da)
            da.Fill(ds, "payments") 

            If ds.Tables.Count > 0 Then
                bindingSource1.DataSource = ds.Tables(0)
            Else
                MessageBox.Show("No results found in database.")
            End If
        Catch ex As Exception
            MessageBox.Show("Unable to connect to database, or an error occurred.")
        End Try
        conn.Close()

    End Sub

I am using the text change event as the results are from a text box search

all working well thanks again

[mikejs]

Is it possible to set the table headers at run time I tried to do it via gui but it just fills across after my table headers I already added ?

[mikejs]

I am trying to use

Private Sub InitializeDataGridViewSearchExist(ByVal ignored As Object, _
 ByVal ignoredToo As EventArgs) Handles DataGridViewSearchExist.ColumnAdded
        ' Set the column header names.
        DataGridViewSearchExist.Columns(0).Name = "Tenant ID"
        DataGridViewSearchExist.Columns(1).Name = "First Name"
        DataGridViewSearchExist.Columns(2).Name = "Last Name"
        DataGridViewSearchExist.Columns(3).Name = "Paid"
        DataGridViewSearchExist.Columns(4).Name = "Date"

    End Sub

But I get error Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

I have 5 columns I am trying to change each column name?

[Oxiegen]

Sure, you can have your own column headers.
However (and I must refer to others on this one) I don't think that the ColumnAdded event is triggered AFTER the databind is done.

First, just add and name the columns in the Designer, as you tried to do.
But you MUST also enter the name of the database field in the DataPropertyName property for each column.
When that is done, go to the form load event and add this line as the very first line.

DataGridViewSearchExist.AutoGenerateColumns = False

[mikejs]

thanks worked a treat :-)

[mikejs]

For some reason this has stopped working the data does not appear if I remove the

DataGridViewSearchExist.AutoGenerateColumns = False

I can see the default headers and data ? I am using the following code to populate

Private bindingSource1 As New BindingSource
    Private Sub RadTextBoxTenantIdSearchExist_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadTextBoxTenantIdSearchExist.TextChanged
        'DataGridViewSearchExist.Columns(0).DefaultCellStyle.BackColor = Color.Aqua

        'DataGridViewSearchExist.Rows(0).DefaultCellStyle.BackColor = Color.Aqua
        DataGridViewSearchExist.DataSource = bindingSource1
        Dim connString As String = My.Settings.strConn
        Dim conn As New SqlConnection(connString)
        Dim ds As New DataSet
        Dim strSQL As String = "SELECT t_id, firstName, lastName, rentPaid, datePaid, propRef, rentDue FROM payments WHERE t_id = '" & RadTextBoxTenantIdSearchExist.Text & "'"
        Dim da As New SqlDataAdapter(strSQL, conn)
        Try
            conn.Open()
            Dim builder As New SqlCommandBuilder(da)
            da.Fill(ds, "payments") 'Needs to be the name of the source table

            If ds.Tables.Count > 0 Then
                bindingSource1.DataSource = ds.Tables(0)
                'RadTextBox1.Text = Total().ToString("c")
            Else
                RadDesktopAlert1.Show()
                RadDesktopAlert1.CaptionText = "Sorry!"
                RadDesktopAlert1.ContentText = "No results found in database"
            End If
        Catch ex As Exception
            RadDesktopAlert1.Show()
            RadDesktopAlert1.CaptionText = "Sorry!"
            RadDesktopAlert1.ContentText = "Unable to connect to database, or an error occurred"
        End Try
        conn.Close()
        da.Dispose()

    End Sub

and this is my db

CREATE TABLE [dbo].[payments](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[t_id] [varchar](10) NOT NULL,
	[firstName] [varchar](20) NOT NULL,
	[lastName] [varchar](20) NOT NULL,
	[rentPaid] [decimal](18, 0) NOT NULL,
	[datePaid] [date] NOT NULL,
	[propRef] [varchar](10) NOT NULL,
	[rentDue] [decimal](18, 0) NULL,
 CONSTRAINT [PK_payments] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

it should work fine??

[mikejs]

here is an image [IMG]http://img20.imageshack.us/img20/3897/screendg.png[/IMG]

[Oxiegen]

First, move the line DataGridViewSearchExist.DataSource = bindingSource1 to the forms Load event.
You only need to assign that once.

And make sure that the fields "t_id, firstName, lastName, rentPaid, datePaid, propRef, rentDue" are still tied to each of the columns you have created in the DataGridView.
Each field name are to be typed into each columns DataPropertyName property.

I would render a guess as to that might be the main reason for why it stopped working.

[mikejs]

Thanks for the reply I have followed all the steps above even created a new dataGridView but I get the same result I copied each field name to each column name moved .DataSource = bindingSource1 to the form onload

still blank results in the datagrid I have done this several times with the same result I am going to create a new form and see if that works

[Oxiegen]

Ok. How about this.
This always works for me.
I'm manually creating and adding each column in the forms Load event, instead of using the Designer.
That way I get more control on what goes where, and how.

Imports System
Imports System.Data.SqlClient

Public Class Form1
    Private connString As String = "Data Source=<computerName>\SQLEXPRESS; Initial Catalog=<database>;Integrated Security=SSPI;"
    Private bindingSource1 As New BindingSource
    Private myDA As SqlDataAdapter
    Private mydataset As DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        mydataset = New DataSet()

        DataGridViewSearchExist.AutoGenerateColumns = False

        Dim clmID As New DataGridViewTextBoxColumn
        clmID.HeaderText = "ID"
        clmID.DataPropertyName = "t_id"
        DataGridViewSearchExist.Columns.Add(clmID)

        Dim clmFirstName As New DataGridViewTextBoxColumn
        clmFirstName.HeaderText = "First Name"
        clmFirstName.DataPropertyName = "firstName"
        DataGridViewSearchExist.Columns.Add(clmFirstName)

        Dim clmLastName As New DataGridViewTextBoxColumn
        clmLastName.HeaderText = "Last Name"
        clmLastName.DataPropertyName = "lastName"
        DataGridViewSearchExist.Columns.Add(clmLastName)

        Dim clmRentPaid As New DataGridViewTextBoxColumn
        clmRentPaid.HeaderText = "Rent Paid"
        clmRentPaid.DataPropertyName = "rentPaid"
        DataGridViewSearchExist.Columns.Add(clmRentPaid)

        Dim clmDatePaid As New DataGridViewTextBoxColumn
        clmDatePaid.HeaderText = "Date Paid"
        clmDatePaid.DataPropertyName = "datePaid"
        DataGridViewSearchExist.Columns.Add(clmDatePaid)

        Dim clmPropRef As New DataGridViewTextBoxColumn
        clmPropRef.HeaderText = "Property Reference"
        clmPropRef.DataPropertyName = "propRef"
        DataGridViewSearchExist.Columns.Add(clmPropRef)

        Dim clmRentDue As New DataGridViewTextBoxColumn
        clmRentDue.HeaderText = "Rent Due"
        clmRentDue.DataPropertyName = "rentDue"
        DataGridViewSearchExist.Columns.Add(clmRentDue)

        DataGridViewSearchExist.DataSource = bindingSource1
    End Sub

    Private Sub RadTextBoxTenantIdSearchExist_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadTextBoxTenantIdSearchExist.TextChanged
        BindData()
    End Sub

    Private Sub BindData()
        Try
            myDA = New SqlDataAdapter("SELECT t_id, firstName, lastName, rentPaid, datePaid, propRef, rentDue FROM payments WHERE t_id = '" & RadTextBoxTenantIdSearchExist.Text & "'", connString)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(myDA)
            myDA.Fill(mydataset, "payments")
            bindingSource1.DataSource = mydataset.Tables("payments")
        Catch ex As Exception
            bindingSource1.DataSource = Nothing
        End Try
    End Sub
End Class

[mikejs]

If I could give you a hug I would thank you for your exceptional help it worked perfectly, sometimes I just hate computers any idea why it doesn't work via design mode and previous code or is just one of those things?

[Oxiegen]

Well. Having tried my luck on relying purely on the Designer myself, I know that sometimes you can miss or forget certain details.
By doing the coding yourself, you can get a clearer view of what's going on.

Not to dismiss the Designer entirely, most of the time it saves the day.
But sometimes you need to get your hands dirty and do it yourself. :)