Member Avatar for needanswer

I want to search the records from the textbox and display the records to the datagridview, if there are no records, just display empty on the datagridview.

this is not working:

Dim sqlsearch As String
sqlsearch = "SELECT * FROM setting WHERE mname LIKE '%" & TextBox.Text
Dim adapter As New OleDbDataAdapter(sqlsearch, con1)
Dim dt As New DataTable("setting")
adapter.Fill(dt)
Form2.DataGridView1.DataSource = dt

RefreshDGV()

THANKS.

  • 4 Contributors
  • 8 Replies
  • 683 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by pritesh2010

Dani AI

Generated

A reliable fix is to stop building the SQL by string concatenation and use a parameterized OleDbCommand + OleDbDataAdapter. That removes the common syntax mistakes seen earlier in this thread (missing quotes/percent signs noted by and the concatenation attempts from ), prevents SQL injection, and guarantees a DataTable (empty when no matches) that can be bound directly to the DataGridView.

Dim sqlsearch As String = "SELECT * FROM [setting] WHERE [mname] LIKE ?;"
Using cmd As New OleDbCommand(sqlsearch, con1)
    cmd.Parameters.AddWithValue("?", "%" & TextBox1.Text.Trim() & "%")
    Using da As New OleDbDataAdapter(cmd)
        Dim dt As New DataTable()
        da.Fill(dt)
        Form2.DataGridView1.AutoGenerateColumns = True
        Form2.DataGridView1.DataSource = dt
    End Using
End Using

Notes and troubleshooting:

  • OleDb uses positional parameters (the ? placeholder). The wildcard is supplied as part of the parameter value. For Access SQL there is a dialect nuance: ANSI-92 uses %/_, while ANSI-89 uses */?. If no rows appear, replace the % in the parameter with * and re-run.
  • An empty DataTable binds as an empty grid; to force a refresh, clear any previous binding first (set DataSource to Nothing) or check dt.Rows.Count to detect zero-results.
  • When binding across forms (assigning to Form2.DataGridView1), ensure Form2 is an initialized instance and its controls are available; otherwise bind after the form is created/shown.
  • Avoid AddWithValue only if precise parameter types matter; otherwise it is fine for simple text searches.

This approach addresses the original syntax errors, answers ’s request for clearer diagnostics (inspect dt.Rows.Count and parameter values), and is more robust than inline string concatenation.

Recommended Answers

All 8 Replies

Member Avatar for Luc001

Hi,

You can find an example here.

Member Avatar for pritesh2010

in you code there is mistakes quotes so write true code. write your code like this

sqlsearch = "SELECT * FROM [setting] WHERE [mname] LIKE ' " & TextBox.Text & " ' ;"

try this code.

Member Avatar for needanswer

Hi,

You can find an example here.

It is great, but I want to know how to display them in datagridview.

Member Avatar for needanswer

Anyway,thanks all, actually it is still not working.

Edited by needanswer because: n/a
Member Avatar for Alexpap

Could you tell me the exact error Message?

Member Avatar for needanswer

Could you tell me the exact error Message?

Actually, the error is on

sqlsearch = "SELECT * FROM setting WHERE mname LIKE '%" & TextBox.Text

This is making that couldn't do the display, I changed it to

sqlsearch = "SELECT * FROM setting WHERE mname LIKE '%" 
& TextBox1.Text & "%'"

IT is definitely working.

Edited by needanswer because: n/a
Member Avatar for pritesh2010

remove the first % after the like keyword and try. if you are working with ms access hen don't write '%' it will not work for the ms access
instead of try this

sqlsearch = "SELECT * FROM [setting] WHERE [mname] LIKE ' " & trim(TextBox.Text) & " ' ;"
Member Avatar for pritesh2010

if you want to bind datagrid view with dataset then write this code

''write connection and  open it
   Dim str As String
   dim     ds as DataSet
        str = "SELECT * FROM [setting] WHERE [mname] LIKE ' " & trim(TextBox.Text) & " ' ;"
        da = New SqlDataAdapter(str, con) ''its sqlDataadapter
        da.Fill(ds, "setting")  ''fill  with your table
        dgv2.DataSource = ds  ''dgv2 is a datagridview name
        dgv2.DataMember = "setting"

try this code for datagrid

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.