locked
Programatically change an SQLDataSource RRS feed

  • 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

    Tuesday, July 12, 2011 7:39 PM

Answers

  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 12, 2011 7:46 PM

All replies

  • 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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 12, 2011 7:46 PM
  • 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

     

    Tuesday, July 12, 2011 8:27 PM