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

ksmclane · #1 (**permalink**) 11-17-10, 11:43

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.

n_i · #2 (**permalink**) 11-17-10, 13:45

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.

ksmclane · #3 (**permalink**) 11-17-10, 13:52

Yes, I was able to figure that much out for myself. I have had the data types checked and they all match up.

n_i · #4 (**permalink**) 11-17-10, 14:07

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.

ksmclane · #5 (**permalink**) 11-17-10, 14:16

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.

n_i · #6 (**permalink**) 11-17-10, 16:42

Quote:

Originally Posted by ksmclane

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.

ksmclane · #7 (**permalink**) 11-17-10, 17:04

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech