Member Avatar for Sevyt

I was quickly creating a program to search through a access database.
The idea is to filter the names and figure out the file location in the physical archive.
Though my query wizard is being rather annoying right now.

I did not use the manual connection like a lot of people do, but just used the add data sources option.
The access database fields are all text, except the id field.

Hopefully somone can give me a wakeup call and tell me what is going on because i am confused.

Img of query builder and error when trying to finish.

  • 3 Contributors
  • 10 Replies
  • 234 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Sevyt

Dani AI

Generated

Brief diagnosis and what to try next (building on and ): the save-time error usually comes from mixing dialects — parameter markers and wildcard characters differ between providers. Access (Jet/ACE) used through OleDb expects positional parameter markers (?) and — by default — Access wildcards (* and ?). SQL Server / SqlClient uses @name and %. If the query builder generated or tested a SQL string that used the SQL‑Server style (@... or %) but the TableAdapter/command is using OleDb for an Access file, the runtime will choke when saving or executing.

A reliable pattern is: build the WHERE dynamically only for non-empty textboxes, use ? placeholders in the command text, and append the Access wildcard (*) into the parameter value. Example (VB.NET + OleDb):

' build parameterized Access query (OleDb). placeholders are ? and parameters must be added in that order
Dim whereParts As New List(Of String)
Dim cmd As New OleDb.OleDbCommand()

If txtName.Text.Trim() <> "" Then
  whereParts.Add("[Name] LIKE ?")
  cmd.Parameters.AddWithValue("p1", txtName.Text & "*")
End If
If txtFirstName.Text.Trim() <> "" Then
  whereParts.Add("[FirstName] LIKE ?")
  cmd.Parameters.AddWithValue("p2", txtFirstName.Text & "*")
End If
If txtFamilyNumber.Text.Trim() <> "" Then
  whereParts.Add("[FamilyNumber] = ?")
  cmd.Parameters.AddWithValue("p3", txtFamilyNumber.Text)
End If
If txtFilingCabinet.Text.Trim() <> "" Then
  whereParts.Add("[Filing cabinet] LIKE ?")
  cmd.Parameters.AddWithValue("p4", txtFilingCabinet.Text & "*")
End If

Dim sql As String = "SELECT * FROM MyTable" & If(whereParts.Count > 0, " WHERE " & String.Join(" AND ", whereParts), "")
cmd.CommandText = sql

Additional tips: bracket any field names that contain spaces or reserved words (e.g. [Filing cabinet]). Verify the connection/provider in the DataSet/TableAdapter: if it’s SqlClient then @params and % are fine; if it’s OleDb for Access use ? and *. In the DataSet designer inspect the generated CommandText/parameters, replace any @name tokens with ?, and add parameters in the same order as the ? placeholders. This approach avoids the query-builder syntax mismatch and keeps the filters optional and safe.

Recommended Answers

All 10 Replies

Member Avatar for Reverend Jim

Could you possibly just post the table structure and tell us what you are trying to retrieve?

Member Avatar for Sevyt

The table structure from the accdb is like this

Sorry got access in dutch on this PC.
The 4 in the red box are supposed to be used to filter.(name, firstname, familynumber, Filing cabinet)
But in the first post i only had the Name, since i was trying to figure out why i was getting the error.

Member Avatar for Eternal Newbie

Not an expert (yes, a newbie!), but, are you using Access database? Then 'LIKE @' was wrong, use 'LIKE yourString' instead will do the trick (or not?).

Member Avatar for Sevyt

Yes thats from a access database.
Like Name(which i used in the above post) would not really do alot, except just show everything in the database that has data in the Name column.
But since that always has one it would just show everything.
When i use Like @Name (have to put @ prefix characters) then you can filter.
For example if do Ae% it would show every name starting with Ae in the Name column.

The filter basicaly works when testing in the query builder, but when saving it you get the error i showed in the screenshot.
Going back to the query after ignoring the error will show the error again but with a empty query builder screen.

Member Avatar for Eternal Newbie

Yuck! I did meant:

LIKE *Name*

Or:

LIKE *@Name*

But the code was auto-corrected later :P
If I'm still wrong, I might need a sample and trying doing this my own :D

Member Avatar for Sevyt

Never used it like that, but does not work the way i tried it.
Though you might do a few things different.

Member Avatar for Reverend Jim

Tell me in English (not code or pictures) what you want your query to do.

Member Avatar for Sevyt

The query is supposed to filter the database.
It is supposed to filter on Name, Firstname, FamilyNumber, Filling cabinet.
Got a textbox for each of those fields and of course a button to perform the filter, if one or all textboxes have text.

I have done it before like that for something else i once made.
But for whatever reason the @ in "Like @Name" is no longer accepted.
Even though the query works when tested in the builder.

Member Avatar for Reverend Jim

It is supposed to filter on Name, Firstname, FamilyNumber, Filling cabinet.

A wildcard in access is "?" to match a single character or "*" to match any sequence of characters. An example query using a wildcard on the name would be

SELECT * FROM mytable 
 WHERE Name LIKE 'M*'

which would select all names starting with M. Because you want to use multiple fields it would look like

Dim qry As String = "SELECT * FROM mytable " _
                  & " WHERE Name          LIKE '" & txtName.text & "'" _
                  & "   AND FirstName     LIKE '" & txtFirstName.Text & "'" _
                  & "   AND FamilyNumber  LIKE '" & txtFamilyNumber.Text & "'" _
                  & "   AND FilingCabinet LIKE '" & txtFilingCabinet.Text & "'"

I suggest using parameterized queries if you are going to allow the user to specify the search strings. See here for how to do that.

Member Avatar for Sevyt

alright thanks Rev, gonna have a look through all of that.

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.