Not able to Fill Dataset using Access OleDB

Question

Have been trying this for more than a few hours now. I am trying fill a dataset using Access Query. the query works fine in Access but does not fill anything in the Dataset. The query is a filter of a view. Example:-

Select Column1  as MyList 
from (Select * from mytable 
where Column1 like "'*" & Textbox1.Text &"*'"
or Column2 like "'*" & Textbox1.Text &"*'" 
or Column3 like "'*" & Textbox1.Text &"*'")
union all
Select Column2 
from (Select * from mytable 
where Column1 like "'*" & Textbox1.Text &"*'"
or Column2 like "'*" & Textbox1.Text &"*'"
or Column3 like "'*" & Textbox1.Text &"*'")
union all 
Select Column3
from (Select * from mytable 
where Column1 like "'*" & Textbox1.Text &"*'" 
or Column2 like "'*" & Textbox1.Text &"*'" 
or Column3 like "'*" & Textbox1.Text &"*'")

I use the set the query as a string and use DataAdapter to fill in the Dataset. But this doesn't work. I don't get any error. The data just doesn't show up in the Dataset.

Code:-

Dim da as new OledbAdapter(sqlstring,myconn)
Dim ds as New Dataset

myconn.open()
da.fill(ds,"TableName")
myconn.close()

I believe this has something to do with access query when the lookup table is a view. Any suggestions/workarounds would be appreciated.

Cheers, Aamir

Why do you have a double single quote at the end of each line? Replace it with &"*'" & _ — Steve, Mar 15 '16 at 13:52
just came back late and typed it in. sorry mate. Have fixed that — Mera Naam Parker, Mar 15 '16 at 14:13
_Dim cmd = "SELECT ....... WHERE Column1 Like '*" & textBox1.Text & "*' or Column2 Like '*" & textBox2.Text & "*' ......... "_ — Steve, Mar 15 '16 at 14:21
However all this mess of string concatenation should be totally removed and a parameterized query used to replace the actual text — Steve, Mar 15 '16 at 14:22
With OleDb, you must use ANSI wildcards in your `Like` comparisons, so substitute `%` for `*` — HansUp, Mar 15 '16 at 15:55

score 0 · Accepted Answer · edited May 23 '17 at 12:07

Instead of concatenating strings you should use a parameterized query. Concatenations often produces incorrect syntax. Sometime this is caused by a missing quote or other simple typing error, sometimes the cause is the presence of a single quote inside the value concatenated to the query text. Finally, albeit more difficult to exploit in MS-Access, string concatenation leads to Sql Injection attacks.

Try to use a parameterized query like this

Dim cmd = "Select Column1  as MyList from (Select * from mytable " & _
    "where Column1 like @p1 or Column2 like @p2 or Column3 like @p3) " & _
"union all " & _
    "Select Column2 from (Select * from mytable " & _
    "where Column1 like @p4 or Column2 like @p5 or Column3 like @p6) " & _
"union all " & _
    "Select Column3 from (Select * from mytable " & _
    "where Column1 like @p7 or Column2 like @p8 or Column3 like @p9)"

Dim da as new OledbAdapter(cmd,myconn)
Dim text1 = "%" & textBox1.Text & "%"
da.SelectCommand.Parameters.Add("@p1", OleDbType.VarWChar).Value = text1
da.SelectCommand.Parameters.Add("@p2", OleDbType.VarWChar).Value = text1
da.SelectCommand.Parameters.Add("@p3", OleDbType.VarWChar).Value = text1
da.SelectCommand.Parameters.Add("@p4", OleDbType.VarWChar).Value = text1
da.SelectCommand.Parameters.Add("@p5", OleDbType.VarWChar).Value = text1
da.SelectCommand.Parameters.Add("@p6", OleDbType.VarWChar).Value = text1
da.SelectCommand.Parameters.Add("@p7", OleDbType.VarWChar).Value = text1
da.SelectCommand.Parameters.Add("@p8", OleDbType.VarWChar).Value = text1
da.SelectCommand.Parameters.Add("@p9", OleDbType.VarWChar).Value = text1
Dim ds as New Dataset

myconn.open()
da.fill(ds,"TableName")

Notice that I have been forced to add 9 parameters to the SelectCommand parameters collection also if you have really just one value. This is caused by the OleDb provider that doesn't support named parameters and requires a value for every parameter placeholder (the @pX) present in the query text. In any case this approach is a lot safer, cleaner and easier to maintain

Thanks Steve. That worked. The only difference I noticed was % used in the textbox string variable as compared to * in my string query. Surprised it still worked with % for wildcard search — Mera Naam Parker, Mar 16 '16 at 07:51

Not able to Fill Dataset using Access OleDB

1 Answers1