SqlParameter questions RRS feed

  • Question

  • Hi - I'm trying out parameterized queries and am encountering some odd limitations.

    1. How to you get the generated SQL out of the SqlCommand?   sqlCommand.CommandText returns only the original parameterized string.  It's hard to trace the code and rerun the query manually without this.

    2.  The assigned value cannot be unassigned or null  (null reference)  or the runtime will throw the exception:
    ---> System.Data.SqlClient.SqlException: Parameterized Query '(@file_class nvarchar(8),@file_label nvarchar(9)
    ,@file_origname ' expects parameter @file_attr3, which was not supplied.

    It's strange to me that it would not convert a null to a DBNull (or whatever) when generating the actual SQL since obviously null is an appropriate state at the application level  (as opposed to database level).   Maybe I'm not understanding the purpose of the DBNull value, but it seems highly awkward and unnecessary to have to trap and convert it.

    Also, the exception will be thrown even if the SQL doesn't refer to the parameter.

    thanks for any help!

    Monday, October 1, 2007 6:11 PM

All replies

  • Based on exception, it looks like your code did not create all the required parameters. Even if parameter contains NULL value, you still need to declare it and add to the collection of the parameters. To check what is wrong, you would need to post your code here.

    You also cannot get generated SQL statement back, because it just does not exist. Provider does not generate new SQL statement, but passes parameters separately to the database engine.


    Monday, October 1, 2007 10:41 PM
  • To send null value trough SqlParameter all you need to do is to declare the parameter, but don't set it's value. The error tells you that you didn't declare the parameter @file_attr3. So declare this parameter but don't set the value. To get the command that is executing on the server you need to use Sql Profiler.

    Monday, October 1, 2007 11:02 PM