Data source controls and parameter type conversion

Q1 – In the code example below runtime converts a value between two
incompatible types:

  • How to display specific database entries into a textbox on a WinForm application
  • SQL Query to insert values from one table into another
  • how to find who locks who in SQL Server 2005
  • How to call C# function in stored procedure
  • Sql Server - Index on nvarchar field
  • SQL to move rows up or down in two-table arrangement
  •   <SelectParameters>
     <asp:Parameter Name="City" Type="Int32" />
     </SelectParameters>
    
    
    
    protected void SqlDataSource2_Selecting(object sender,
           SqlDataSourceSelectingEventArgs e)
    {e.Command.Parameters["@City"].Value = "100";}
    

    Exception I got:

    “Conversion failed when converting the nvarchar value ‘Seattle’ to
    data type int.”

    A) The above exception suggests that runtime did manage to convert a
    value of type String into a value of type Int32, and thus the
    exception happened on SqlServer?!

    B) Since String and Int32 are incompatible types, why did runtime
    perform conversion from String to Int32 in the first place?

    Doesn’t the fact that we’re dealing with incompatible type make
    runtime “realize” that app most likely has a bug, similary to the way
    compiler “realizes” that it’s dealing ( in the code below ) with two
    incompatible types:

    String s = ”something”;
    int i = (int)s; //error
    

    Q2:

    A)
    public void GetEmployee( int EmployeeID );

    <asp:ObjectDataSource  SelectMethod=”GetEmployee” …>
      <SelectParameters>
        <asp:ControlParameter Name = ”EmployeeID” ...>
      </SelectParameters>
    

    If for whatever reason EmployeeID parameter is NULL, ObjectDataSource
    will convert Null to zero and passed it as argument to GetEmployee()
    method.

    Why does runtime make such a conversion? Wouldn’t throwing an
    exception made more sense?

    B) “Use the ConvertEmptyStringToNull property to specify whether an
    empty string value is automatically converted to null when the data
    field is updated in the data source.”

    I don’t quite understand the usefulness of this property. Why would
    empty string indicate that we want null to be inserted into source’s
    data field? I’d assume that this data field is of type String? Then
    why not also have ConvertZeroInt32ToNull etc?

    Q3:

    <asp:SqlDataSource ID="sourceEmployees" runat="server"
         ProviderName="System.Data.SqlClient"
         ConnectionString="<%$ ConnectionStrings:Northwind %>"
         SelectCommand="SELECT EmployeeID, FirstName,LastName,
         Title, City FROM Employees WHERE City=@City">
             <SelectParameters>
                 <asp:Parameter Name="City"/>
             </SelectParameters>
    </asp:SqlDataSource> 
    

    A) I assume that when you don’t specify of which type Parameter
    instance “City” is, it is automatically of type Object, which means it
    can later be assigned value of any type. Thus if “City” is later ( say
    inside SqlDataSource2_Selecting() event handler ) assigned a value of
    a wrong type, this wrong assignment will only be detected on Sql
    server, and not before ( of course Sql server will report that error
    back to web server )?

    B) If we create a SqlParameter instance of type NVarChar(20) and want
    to pass this parameter to a stored procedure, will Ado.net pass to a
    stored procedure just the value of this parameter, or will it also
    somehow inform the procedure the exact type of this parameter ( which
    is NVarChar(20))?

    thanx

  • Cant read .bak files
  • SQL Server 2000 - Query a Table’s Foreign Key relationships
  • How to debug stored procedures in SQL server 2008 without CLR?
  • Microsoft® ODBC Driver 11 for SQL Server® on RedHat Linux with PHP - gives an error when bind parameter with PDO for stored procedures
  • SQLSRV and multiple selects in Stored Procedure
  • Remove and split data into multiple columns in select statement
  • 3 Solutions collect form web for “Data source controls and parameter type conversion”

    This is a SQL Server error. You’ve hit a datatype precedence issue.

    The clue is here:

    SELECT EmployeeID, FirstName,LastName, Title, City FROM Employees WHERE
    City=@City --here exactly
    

    The City column is nvarchar
    The parameter is explicitly set to int

    Adding it all together, you get:

    SELECT EmployeeID, FirstName,LastName, Title, City FROM Employees WHERE
    City=100
    

    By the rules of datatype precedemce, the database engine tries to change all the values for City to “int”. And fails, of course.

    For your points though:

    • Q1: Same issue. Nowhere have you told SQL Server that the “100” should be string
    • Q2: Empty string casts to zero in SQL. Send dbnull.value if you want a SQL null
    • Q3a. SQL has no object data type. It looks at 100 and decides “int”
    • Q3b. Yes, use a stored proc and define it correctly.

    Overall, SQL Server is behaving exactly as advertised. In this case, you simply are not giving enough information.

    Edit: Stored proc usage

    You tell SQL what the data type is when you add the parameter to the parameters collection. You define (and create) the stored proc to have the nvarchar parameter.

    I’ll just try to answer your first question —
    “Conversion failed when converting the nvarchar value ‘Seattle’ to data type int.”

    Since I don’t see “Seattle” mentioned anywhere, I’d guess that this error is actually coming from the stored procedure you’re calling — ie. whatever you’re using @City for in the proc is the problem. It sounds like instead of doing something like “where CityID=@City”, you’re doing a “where CityName=@City”, and it’s complaining that it can’t convert one of the values of CityName (“Seattle”) to an integer to compare it with @City.

    I’d guess that this error is actually coming from the stored procedure you’re calling — ie. whatever you’re using @City for in the proc is the problem. It sounds like instead of doing something like “where CityID=@City”, you’re doing a “where CityName=@City”, and it’s complaining that it can’t convert one of the values of CityName (“Seattle”) to an integer to compare it with @City.

    I used incompatible types on purpose just to get my point across –> which is ‘why does runtime even bother to try to convert a value from one incompatible type to another, since the fact that user assigned string to variable of type integer, suggests that it is most likely a bug and not something user did knowingly’?

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.