CODEWITHSUNDEEP
vb.netlinq
mandarin software
mandarin software

Reputation: 217

Problem in searching with linq using vb.net

I have this code working as well for searching with data I typed manually :

 Dim Collections = New String() {"Frensh", "Britich", "English"}
 Dim textToSearch As String = dr7.Text.ToLower()
 ListBox1.Visible = False
 If String.IsNullOrEmpty(textToSearch) Then Return
 Dim result As String() = (From i In Collections Where i.Trim().Contains(textToSearch) Select i).ToArray()
 If result.Length = 0 Then Return
 ListBox1.Items.Clear()
 ListBox1.Items.AddRange(result)
 ListBox1.Visible = True

Now I want to load the data not using array but from database and I did this code :

Dim strSql As String = "SELECT distinct t2 FROM add_emp where  t2 like '%" & dr7.Text.Trim & "%'"
        Dim dtb As New DataTable
        If con.State = ConnectionState.Open Then con.Close()
        con.Open()
        Dim dad As New SqlDataAdapter(strSql, con)
        dtb.Clear()
        dad.Fill(dtb)
        dad.Dispose()
        con.Close()
          
        Dim textToSearch As String = dr7.Text.ToLower()
        ListBox1.Visible = False
        If String.IsNullOrEmpty(textToSearch) Then Return

        Dim result As String() = (From i In dtb Where i.Trim().Contains(textToSearch) Select i).ToArray()

        If result.Length = 0 Then Return
        ListBox1.Items.Clear()
        ListBox1.Items.AddRange(result)
        ListBox1.Visible = True

What I tried to do , that I replaced "collection" as array to "dtb" as DataTable and the error appeared for this line , and I do not know handle this code to be working as well :

 Dim result As String() = (From i In dtb Where i.Trim().Contains(textToSearch) Select i).ToArray()

Upvotes: 0

Views: 79

Answers (1)

Mary
Mary

Reputation: 15091

I have separated the data access code from the user interface code.

Using...End Using blocks ensure that you database objects are closed and disposed even if there is an error. Don't declare your connection outside the method where it is used. You created your DataTable 3 lines above. Why would you need to Clear it? The DataAdapter will open and close the connection if it finds it closed. Otherwise, it will leave it open.

As to the linq code. You want to loop through data rows. DataTable does not implement IEnumerable but it provides an extension method to return an enumerable (AsEnumerable). The first column of the DataTable is a Field(Of String) with index 0.

Private dr7 As New TextBox
Private Function GetValuesForListBox(SearchString As String) As String()
    Dim strSql = "SELECT distinct t2 FROM add_emp where  t2 like @dr7;"
    Dim dtb As New DataTable
    Using con As New SqlConnection("Your connection string"),
            dad As New SqlDataAdapter(strSql, con)
        dad.SelectCommand.Parameters.Add("@dr7", SqlDbType.VarChar).Value = $"%{SearchString}%"
        dad.Fill(dtb)
    End Using 'Closes and disposes Connection and disposes DataAdapter

    Dim result = (From row As DataRow In dtb.AsEnumerable
                  Select row.Field(Of String)(0)).ToArray
    Return result
End Function

Private Sub FillListBox()
    If String.IsNullOrWhiteSpace(dr7.Text) Then Return
    Dim Result = GetValuesForListBox(dr7.Text.Trim)
    If Result.Length = 0 Then Return
    ListBox1.Visible = False
    ListBox1.Items.Clear()
    ListBox1.Items.AddRange(Result)
    ListBox1.Visible = True
End Sub

Upvotes: 1

Related Questions