Member Avatar for TechNewbie23

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
  • 228 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by TechNewbie23

Dani AI

Generated

As showed, the listbox approach works — the missing pieces for a clean DataGridView solution are (1) bind a DataTable/BindingSource (not a raw DataSet) and (2) stop building SQL with string concatenation. was right to suggest a DataAdapter; the following is a safer, clearer pattern that fixes the common problems you hit (grid filling with everything, SQL injection, date/NULL handling).

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
    If String.IsNullOrWhiteSpace(txtFName.Text) OrElse String.IsNullOrWhiteSpace(txtLName.Text) Then
        MessageBox.Show("Please enter both first and last name.")
        Return
    End If

    Dim connString = "Data Source=User-PC;Initial Catalog=patientrecords;Integrated Security=True"
    Dim sql = "SELECT Child_First_Name, Child_Last_Name, Child_DOB FROM Parent_Details " &
              "WHERE Parent_First_Name = @FirstName AND Parent_Last_Name = @LastName " &
              "ORDER BY Child_First_Name, Child_Last_Name, Child_DOB;"

    Dim dt As New DataTable()
    Using cn As New SqlClient.SqlConnection(connString)
        Using cmd As New SqlClient.SqlCommand(sql, cn)
            cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 100).Value = txtFName.Text.Trim()
            cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 100).Value = txtLName.Text.Trim()
            Using da As New SqlClient.SqlDataAdapter(cmd)
                da.Fill(dt)
            End Using
        End Using
    End Using

    If dt.Rows.Count = 0 Then
        MessageBox.Show("Record does not exist")
        DataGridView1.DataSource = Nothing
    Else
        DataGridView1.AutoGenerateColumns = True
        DataGridView1.DataSource = dt
        DataGridView1.Columns("Child_DOB").DefaultCellStyle.Format = "d"
    End If
End Sub

Troubleshooting tips: if your grid still shows all rows, check that the WHERE actually ran (debug the command parameters or use SQL Profiler), and make sure you assign DataGridView.DataSource to the DataTable (or use a BindingSource.DataSource = dt). To allow optional or partial-name searches, build the WHERE dynamically and add only the needed parameters, or use LIKE @name + '%'. Finally, move the connection string into app.config and add indexes on the parent name columns if queries are slow.

Recommended Answers

All 6 Replies

Member Avatar for Pgmer

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?

Member Avatar for TechNewbie23

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

Member Avatar for Pgmer

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

Member Avatar for TechNewbie23

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

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

Member Avatar for Pgmer 
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
Member Avatar for TechNewbie23

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.