I have a web based project using VB 2010 that gets data from sql 2005 server and populates a gridview which was dropped onto the web page from the toolbox.

My problem is i need to use a variable in the VB code and have searched and read hundreds of solutions but none work.

variables are dist and users, so if i enter 200m for dist and John for users then it works.

Here is the VB code which does not work using variables

SqlDataSource1.SelectCommand = "SELECT event, athname, D_ate, TIME1 FROM TableName

WHERE ( event='&dist&') And (athname = '&users&')"


I have tried different formatting characters for the variable ie @ ? etc.


This does work using literals

SqlDataSource1.SelectCommand = "SELECT event, athname, D_ate, TIME1 FROM TableName

WHERE ( event='200m') And (athname = 'John')"


I am totally stuck any help would be appreciated

Maybe you are forgetting double quotes on both side of the variable:

SqlDataSource1.SelectCommand = SELECT event, athname, D_ate, TIME1 FROM TableName WHERE event='" & dist & "' AND athname = '" & users & "'"

I don't know if you got it solved yet taxmybrain, but if you are following Mitja's suggestion you would also need a " before Select event

Thus making it:

SqlDataSource1.SelectCommand = "SELECT event, athname, D_ate, TIME1 FROM TableName WHERE event='" & dist & "' AND athname = '" & users & "'"

This assumes dist and users are your variables that have information that you would like to pass into sql.

try below query
...WHERE ( event like '&%dist%&') or (athname like '&%users%&')