0

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
2
Contributors
6
Replies
11
Views
6 Years
Discussion Span
Last Post by TechNewbie23
0

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?

0

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

0

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

0

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

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

0
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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.