Search with oledb

StewartBW 745 Reputation points
2024-06-17T05:12:04.8333333+00:00

Hello

I use Access Database Engine and OleDbReader to search within my mdb database:

New OleDbCommand("SELECT * From Blah WHERE TheID = ?" + strCondition, conn)

strCondition can vary based on the user input:

strCondition = " AND ([strFieldName] = '" + termTextBox.Text.Trim.Replace("'", "''") + "')"
or
strCondition = " AND ([strFieldName] Like '%" + termTextBox.Text.Trim.Replace("'", "''") + "%')"
or...

Need to use parameterized queries to pass termTextBox.Text to command and have no idea how to proceed?

Any help or idea please?

Thanks in advance :)

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,642 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 42,746 Reputation points
    2024-06-17T05:45:21.2633333+00:00
  2. Jiachen Li-MSFT 28,001 Reputation points Microsoft Vendor
    2024-06-18T06:31:23.68+00:00

    Hi @StewartBW ,

    When using OleDbCommand with parameters in your SQL query, you can specify parameters using placeholders (?) in your SQL statement. However, OleDb does not support named parameters; instead, it uses positional parameters. This means the parameters must be added in the exact order they appear in the SQL query.

    Check OleDbParameterCollection.AddWithValue(String, Object) Method.

    Using cmd1 As New OleDbCommand("SELECT * From Blah WHERE TheID = ? AND ([strFieldName] Like '%" + termTextBox.Text.Trim.Replace("'", "''") + "%')" + strCondition, conn)
    cmd1.Parameters.AddWithValue("@TheID", theIDValue) ' Replace theIDValue with the actual value for TheID
    ...
End Using

    Best Regards.

    Jiachen Li

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  3. StewartBW 745 Reputation points
    2024-06-19T00:53:31.6533333+00:00

    Hello and thanks so much, I meant how to pass the termTextBox.Text via parameter?

    0 comments