SQL String not working to populate datagrid

destino

New member
Joined
Feb 16, 2012
Messages
2
Programming Experience
10+
Greetings

I am just starting out with data access and vb net.

I have the following code that works fine if the value of textbox txt_HorseName.Text is not populated, the if statement does evaluate to true and the where clause is not added to the SQL string.

The SQL string is valid for both strings that include the where clause and do not include the where clause. If i copy the resultant string as output to the immediate window to an access query and run it in Access it returns the correct result however the datagrid does not appear at all in my application.

If I clear out the textbox the entire table is displayed.

I'm just confused as to why the query is working without the where clause and not working with the where clasue when the syntax of the resultant queries are both correct as far as I can tell.

This is the string with out the text box populated.
"SELECT Horses.Name, Horses.Horseid, Horses.Regno, Horses.HorseType, Horses.Sex FROM Horses ORDER BY Horses.Name ;"

This is the string with the text box populated.
"SELECT Horses.Name, Horses.Horseid, Horses.Regno, Horses.HorseType, Horses.Sex FROM Horses WHERE Horses.Name like "*BLACK BEAUTY*" ORDER BY Horses.Name ;"

Any help would be appreciated.

Thanks

Dim ls_querystring As String
ls_querystring = "SELECT Horses.Name, "
ls_querystring = ls_querystring & "Horses.Horseid, "
ls_querystring = ls_querystring & "Horses.Regno, "
ls_querystring = ls_querystring & "Horses.HorseType, "
ls_querystring = ls_querystring & "Horses.Sex, "
ls_querystring = ls_querystring & "Horses.Colour, "
ls_querystring = ls_querystring & "Horses.Ownerid, "
ls_querystring = ls_querystring & "Horses.Sireno, "
ls_querystring = ls_querystring & "Horses.Damno "
ls_querystring = ls_querystring & "FROM Horses "

If txt_HorseName.Text <> "" Then
ls_querystring = ls_querystring & "WHERE Horses.Name like " & Chr(34) & "*" & txt_HorseName.Text & "*" & Chr(34) & " "
End If

ls_querystring = ls_querystring & "ORDER BY Horses.Name ;"

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=horsebookJune2011.mdb")
Dim da As New OleDbDataAdapter(ls_querystring, cn)

Dim dt As New DataTable

da.Fill(dt)
da.Dispose()
cn.Dispose()
Me.MasterHorseGridview.DataSource = dt
Me.MasterHorseGridview.DataBind()
 
Last edited:

destino

New member
Joined
Feb 16, 2012
Messages
2
Programming Experience
10+
Looks as if I've solved the problem.

It appears that In ASP.net you need to use the SQL wildcards

So the following code is working.

ls_querystring = ls_querystring & "Horses.Damno "
ls_querystring = ls_querystring & "FROM Horses "
If txt_HorseName.Text <> "" Then
ls_querystring = ls_querystring & "WHERE Horses.Name Alike " & Chr(34) & "%" & txt_HorseName.Text & "%" & Chr(34) & " " ' & Chr(34) & "*" & txt_HorseName.Text & "*" & Chr(34) & " "
End If
ls_querystring = ls_querystring & " ORDER BY Horses.Name ;"
 
Top Bottom