Hey!

I am creating an application which will allow users to search parents names and display their childen respectively in a datagrid.
I have created a form with two text boxes, a search button and a datagrid. The two text boxes are txtFName.text and txtLName.Text. On clicking btnSearch I want to be able to run a search to the SQL database for specific records.

So far I have been sucessful in doing so with a listbox but I would prefer to have the data populating in a datagrid as it is more cleaner.

Here is the working code for displaying searched data in a listbox:

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

        Dim myConnection2 As New SqlClient.SqlConnection()
        Dim myCommand2 As New SqlClient.SqlCommand()
        Dim myReader2 As SqlClient.SqlDataReader

        Try
            lstReader.Items.Clear()

            myConnection2.ConnectionString = "Data Source=User-PC;Initial Catalog=patientrecords;Integrated Security=True"

            myConnection2.Open()

            myCommand2.CommandText = "SELECT [Child_First_Name], [Child_Last_Name], [Child_DOB] FROM Parent_Details " & _
            "WHERE [Parent_First_Name] = '" & txtFName.Text & "'  AND " & _
            " [Parent_Last_Name] = '" & txtLName.Text & "' order By [Child_First_Name], [Child_Last_Name], [Child_DOB] ; "




            myCommand2.Connection = myConnection2

            myReader2 = _
            myCommand2.ExecuteReader(CommandBehavior.SequentialAccess)

            Do While myReader2.Read
                lstReader.Items.Add(myReader2.GetString(0) & " " & _
                                    myReader2.GetString(1) & " " & _
                                    myReader2.GetString(2).ToString)

            Loop

            If Not myReader2.HasRows Then
                MsgBox("Record does not exist")
                lstReader.Items.Clear()
            End If

        Catch el As Exception
            Console.WriteLine(el.Message)

        Finally
            myConnection2.Close()
        End Try

    End Sub

Use data adapter and dataset objects and fill dataset with records and then

Datagridview.datasource=dataset.table("Tablename").

What if user wants to search only on first name or only on last name? If eaither of textbox are emty will ur sql statement gives u record?

Yes thanks I have tried this but it fills the grid will all of the data in the SQL table rather then the data that I am serching for i.e I am searching for a child belonging to a particular parent

Users must enter both the first and last name to receive a record. If either are empty then they will see a Message Box with "Record does not exist" displayed

Then get that data into dataview sort or find you can do, even in dataset you can find for perticlar record before assigning to the grid

I don't fully understand what you mean.
Could you provide code please?

Should I be declaring the SqlDataAdapter? Within my frm or btnSearch?

Dim myConnection2 As New SqlClient.SqlConnection()
        Dim myCommand2 As New SqlClient.SqlCommand()

        Dim da As SqlDataAdapter
        Dim ds As New DataSet


        Try
            ' lstReader.Items.Clear()

            myConnection2.ConnectionString = "Data Source=User-PC;Initial Catalog=patientrecords;Integrated Security=True"

            myConnection2.Open()

            myCommand2.CommandText = "SELECT [Child_First_Name], [Child_Last_Name], [Child_DOB] FROM Parent_Details " & _
            "WHERE [Parent_First_Name] = '" & txtFName.Text & "'  AND " & _
            " [Parent_Last_Name] = '" & txtLName.Text & "' order By [Child_First_Name], [Child_Last_Name], [Child_DOB] ; "




            myCommand2.Connection = myConnection2
            da = New SqlDataAdapter(myCommand2)
            da.Fill(ds)
            If ds.Tables.Count > 0 Then
                DataGridView1.DataSource = ds
            Else
                MsgBox("Record does not exist")
            End If
          

        Catch el As Exception
            Console.WriteLine(el.Message)

        Finally
            If myConnection2.State = ConnectionState.Open Then
                myConnection2.Close()
            End If
        End Try

Thank you so much for your help. It was driving me crazy

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.