Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: Invalid character found in a character string argument of the function "INTEGER".

    I do not have direct access to the server to find the actual version info. I only know it is running on an AIX box.

    I am trying to fix someone elses work, not sure where the problem lies. I have a small web portal that works fine for browsing the data, but when trying to do an update gives the above error.

    I have this code in the <script> section of the page:
    Code:
    protected void DetailsView_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
        {
            // Iterate though the values entered by the user and HTML encode 
            // the values. This helps prevent malicious values from being 
            // stored in the data source.
            for (int i = 0; i < e.NewValues.Count; i++)
            {
                if (e.NewValues[i] != null)
                {
                    e.NewValues[i] = Server.HtmlEncode(e.NewValues[i].ToString());
                }
            }
        }
    and this code in the body of the page:
    Code:
    <asp:SqlDataSource 
            ID="sqlDetails" runat="server" 
            Connectionstring="<%$ Connectionstrings:apples %>"
            ProviderName="<%$ Connectionstrings:apples.ProviderName %>" 
            
            SelectCommand="
                select 
                 a.account_id,
                 a.account_code,
                 a.account_name,
                 a.account_policy,
                 a.account_target,
                 a.account_workitem,
                 a.start_date,
                 a.kit_date,
                 a.upgrade_date,
                 a.approval_date,
                 a.sunset_date,
                 a.alert_flag,
                 a.cirats_flag,
                 a.report_flag,
                 a.sample_flag,
                 a.sample_rate,
                 d.department_id,
                 d.department_code
                from
                 account a,
                 department d
                where d.department_id = a.department_id
                and a.account_code = ?
            " 
            
            UpdateCommand="
                update account set 
                 account_name = ?,
                 account_policy = ?,
                 account_target = ?,
                 account_workitem = ?,
                 kit_date = ?,
                 upgrade_date = ?,
                 approval_date = ?,
                 sunset_date = ?,
                 alert_flag = ?,
                 cirats_flag = ?,
                 report_flag = ?,
                 sample_flag = ?,
                 sample_rate = ?,
                 department_id = ?
                where account_id = ?" 
    			
    			<UpdateParameters>
                <asp:Parameter Name="account_name" Type="string" />
                <asp:Parameter Name="account_policy" Type="string" />
                <asp:Parameter Name="account_target" Type="Int16" />
                <asp:Parameter Name="account_workitem" Type="string" />
                <asp:Parameter Name="kit_date" Type="DateTime" />
                <asp:Parameter Name="upgrade_date" Type="DateTime" />
                <asp:Parameter Name="approval_date" Type="DateTime" />
                <asp:Parameter Name="sunset_date" Type="DateTime" />
                <asp:Parameter Name="alert_flag" Type="string" />
                <asp:Parameter Name="cirats_flag" Type="string" />
                <asp:Parameter Name="report_flag" Type="string" />
                <asp:Parameter Name="sample_flag" Type="string" />
                <asp:Parameter Name="sample_rate" Type="Int16" />         
                <asp:Parameter Name="department_id" Type="Int16" />
                <asp:Parameter Name="account_id" Type="Int16" />
            </UpdateParameters>
    I have never seen this way of building an SQL statement before. Strangely, sometimes the update works, and we can go into the database and update the field directly with no problem. I think there may be an issue with some of the fields having null values, but at this point I'm grasping at straws.

    Any help will be appreciated.
    Last edited by ksmclane; 11-17-10 at 12:48. Reason: Additional Info

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Somewhere you are trying to put a non-numeric character into an integer table column. You will need to verify that the column data types in the table account match the parameter type definitions in your ASP code.

  3. #3
    Join Date
    Nov 2010
    Posts
    4
    Yes, I was able to figure that much out for myself. I have had the data types checked and they all match up.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Now all you need to do is to figure out into which of the four integer fields you are inserting non-numeric characters. Hint: a space (0x20) is a non-numeric character.

  5. #5
    Join Date
    Nov 2010
    Posts
    4
    Funny thing is, we are only updating a date field. All I can think is there is already something in the data, but that doesn't really make a whole lot of sense. Several of the Integer fields are empty to begin with. I just did an experiment on a backup copy. I populated all fields and still got the same error even though I made sure to enter integers where appropriate. The wonderful stack trace doesn't give any useful info such as which field is barfing.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ksmclane View Post
    Funny thing is, we are only updating a date field.
    Not sure why this is funny, but, according to the UPDATE statement you posted earlier, you are updating multiple columns of that table, possibly all of them.

    I don't know how ASP handles data you retrieve from the table initially, but I'm almost sure that, unlike DB2, it does not distinguish between a NULL value and an empty string. If one of the integer columns contains a NULL value, it may be treated as an empty string in ASP, and if you later try to put it (the empty string, that is) back into the column DB2 won't be able to cast it into an integer. This is just one of the possible explanations.

  7. #7
    Join Date
    Nov 2010
    Posts
    4
    I meant funny strange, not funny ha-ha. , turns out I found another page with the same function using a master details data structure that had the same capability, and it works! I still haven't found the problem with the first page but it is no longer so urgent. I will have to do a stare and compare and see if I can find the why of it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •