« .NET for Domino Developers Part 1
.NET for Domino Developers Part 2 »

.NET issues with ODBC SqlDataSource

While creating the next installment of .NET Development for Domino Developers, I ran across I real stickler of an issue that kept me up at night longer than I wanted to be. It is this simple: when you are using a SqlDataSource object in .NET and you are using an ODBC provider, all of the documentation about the SQL statements that data bound controls use is not completely correct.


The SqlDataSource performs inserts and updates for the developer by using SQL statements with placeholders that will be filled with the data from the insert/update. In all of the documentation, and every single help page on the web, the syntax for these placeholders is “@yourFormFieldName”. So an insert statement in the data source would look like this:

INSERT INTO yourTable (fieldName1, fieldName2) VALUES (@yourFormFieldName1, @yourFormField2)

In a nutshell, The “@” syntax is only appropriate with SQL server. If you use an ODBC provider the only placeholder you can use is “?”. So the same SQL statement looks like this:

INSERT INTO yourTable (fieldName1, fieldName2) VALUES (?,?)

Then the form control that is using the SqlDataSource needs to list the fields in the same order as the SQL statement. I will touch on this in the next installment, but I wanted to put something into the web-search realm for any others that might have the same issue.

This entry was posted on Wednesday, December 21st, 2005 at 3:55 pm and is filed under Microsoft, Web Development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

5 Responses to “.NET issues with ODBC SqlDataSource”

  1. chr Says:
    January 6th, 2006 at 11:44 am

    Yes you are right. But if you run a update insted the ‘?’ doesn’t work.
    You want to run:

    UPDATE my_table SET title = ‘asdasd’ WHERE ID = 1

    If you want it dynamic you would run

    UPDATE my_table SET title = ? WHERE ID = ?

    But that doesn’t work, it doesn’t accept the ‘?’ here ID.

    UPDATE my_table SET title = ? where ID = 1

    That works fine.
    Any ide on how you get the UPDATE to work ?

  2. Administrator Says:
    January 6th, 2006 at 1:11 pm

    You need to make sure that your query parameters are in the same order as the “?” symbols.

    http://www.crossedconnections.org/w/wp-images/chap3/update_sql.png

  3. chr Says:
    January 7th, 2006 at 8:02 am

    Hmm, not getting it to work.

    ”
    ProviderName=”" SelectCommand=”SELECT * FROM tb_movies ORDER BY added DESC, title, episodes, discnr” UpdateCommand=”UPDATE tb_movies SET title=? WHERE id=?” >

    Shouldn’t that be right?
    I update it from a GridView (BoudField).

  4. chr Says:
    January 7th, 2006 at 8:07 am

    Hmm, everything didn’t post, check your mail insteed )=

  5. IQ-Warrior Says:
    July 5th, 2006 at 6:00 am

    “SELECT field1, field2 FROM myTable WHERE field3 = @field3″
    Why don’t you use own function to replace the string “@field3″ with its desired value?

    Public Function setParam(ByVal sqlCommand as String, ByVal filedName as string, ByVal fieldValue as String, ByVal paramType as String)

    ‘here comes some code to handle different paramTypes, eg. strings, integers, dates, etc…
    ‘ …
    ‘then finally:

    sqlCommand = sqlCommand.Replace(filedName, fieldValue)
    return sqlCommand
    End Function

    ‘and you can use it like this

    mySqlDataSource.SelectCommand = setParam(mySqlDataSource.SelectCommand, “@field3″, “John Smith”, “string”)

Leave a Reply