Programatically change an SQLDataSource

Question

User1110448527 posted

 

Greetings

I am attempting to change the data source on the fly. I have checked out some code samples on the forum and have created the following piece of code.

The SQL that results will run in the database but here it always gives an error.

Currently it is giving the following error message

Invalid column name 'SO'.

SO is not a column it is a value that the field [CLASS] can contain. The SQL string in ls_querystring resolves to

SELECT [HORSE_ID], [NAME], [CLASS] FROM [SMALLSTUD] WHERE [CLASS] = "SO" ORDER BY [HORSE_ID]

Dim ls_querystring As String

ls_querystring = "SELECT [HORSE_ID], [NAME], [CLASS] FROM [SMALLSTUD] WHERE [CLASS] = " & Chr(34) & "SO"& Chr(34) & " ORDER BY [HORSE_ID]"

IberianSQLDataSource.SelectCommand = ls_querystring

Answer 1

User-862012937 posted

if you want it to be SELECT [HORSE_ID], [NAME], [CLASS] FROM [SMALLSTUD] WHERE [CLASS] = "SO" ORDER BY [HORSE_ID]

then use

ls_querystring = "SELECT [HORSE_ID], [NAME], [CLASS] FROM [SMALLSTUD] WHERE [CLASS] = 'SO' ORDER BY [HORSE_ID]"

notice the single quotes around SO

Now how are you changing the "data source on the fly"?

Are you wanting to change datasources or the SQL query?

Answer 2

User1110448527 posted

Many thanks for that.

What i intend to do is allow selection criteria to be modified by the user based on a combination of radio buttons and/or check boxes. I want to give the user flexibility but controlled flexibility. Somewhat like a benevolent dictator.

What I will be changing is the SQL query that drives that datasource.

I've been using chr(34) in VB for years to create my string delimiters. Looks like I need to change to CHR(39). Nested quotes get quite complex at times when you are attempting to build query strings. I find the CHR() function very convenient.

Again many thanks