I have inherited some ASP.NET code that I need to update which has resulted in my needing to change the ASP SqlDataSource's UpdateCommandType from a string (hard coded SQL Update statement) to a stored procedure containing the Update statement.
The string executes fine and uses parameters that are bound to controls in a details view (I know this is not best practice and it pains me having to work with data connections from the client side...! But I dont have time to re-write all the data connections for this and many pages just yet).
Anyway, I have just changed the UpdateCommand to a stored procedure that does the same thing and I just get the error
Input String was not in a correct format.
when I try to update on the page.
I can supply code if requested, but it is big & horrible so I am tentatively asking if anyone has any initial ideas? I will put a few small bits below though. I have been looking at the UpdateParameterCollection as I wonder if the parameter collection is getting cached anywhere - but cannot see anything.
I have controls bound to the DataSource items like so:
<EditItemTemplate>
<asp:CheckBox ID="chkNonReview" runat="server" Checked='<%# Bind("NonReview") %>' />
</EditItemTemplate>
And the SqlDataSource has been changed from this...
<asp:SqlDataSource ID="dsEventDV" runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnString %>"
DeleteCommand="DELETE FROM [I_TRAINEE_EVENTS] WHERE [EVENTID] = @EVENTID"
InsertCommand="INSERT_TRAINEE_EVENTS_ED3"
InsertCommandType="StoredProcedure"
OnInserted="DSEvent_Inserted"
SelectCommand="Get_Candidate_Events_I3"
SelectCommandType="StoredProcedure"
UpdateCommand="UPDATE I_TRAINEE_EVENTS (etc...)"
.....
to this:
<asp:SqlDataSource ID="dsEventDV" runat="server"
ConnectionString="<%$ ConnectionStrings:MyConnString %>"
DeleteCommand="DELETE FROM [I_TRAINEE_EVENTS] WHERE [EVENTID] = @EVENTID"
InsertCommand="INSERT_TRAINEE_EVENTS_ED3"
InsertCommandType="StoredProcedure"
OnInserted="DSEvent_Inserted"
SelectCommand="Get_Candidate_Events_I3"
SelectCommandType="StoredProcedure"
UpdateCommand="UPDATE_TRAINEE_EVENTS"
UpdateCommandType="StoredProcedure">
.....
With the new UpdateCommand values.
The update parameters:
<UpdateParameters>
<asp:Parameter Name="EVENTID" Type="Int32"/>
<asp:Parameter Name="EVENTTYPEID" Type="Int32"/>
<asp:Parameter Name="EVENTDATE" Type="DateTime"/>
<asp:Parameter Name="STAFFID" Type="Int32"/>
<asp:Parameter Name="REVIEWNO" Type="Int32"/>
<asp:Parameter Name="COMMENTS" Type="String"/>
<asp:Parameter Name="DESTINYVERIFIED" Type="Int32"/>
<asp:Parameter Name="DESTINYVERIFIEDBY" Type="Int32"/>
<asp:Parameter Name="DESTINYVERIFIEDDATE" Type="DateTime"/>
<asp:Parameter Name="REASONFORSUSPENSIONID" Type="Int32"/>
<asp:Parameter Name="RETURNTOWORKDATE" Type="DateTime"/>
<asp:Parameter Name="ContactDetailsUpdated" Type="Int32"/>
<asp:Parameter Name="RETENTION_STATUS_ID" Type="Int32"/>
<asp:Parameter Name="RETENTION_REASON_ID" Type="Int32"/>
<asp:Parameter Name="NonReview" Type="Int32"/>
<asp:Parameter Name="FalsifiedEventReason" Type="Int32"/>
<asp:Parameter Name="SusReqReasonID" Type="Int32"/>
<asp:Parameter Name="SusReqReturnDate" Type="DateTime"/>
</UpdateParameters>
The stored procedure declaration is as follows:
CREATE PROCEDURE [dbo].[UPDATE_TRAINEE_EVENTS]
@EVENTID int,
@EVENTTYPEID int,
@EVENTDATE datetime,
@STAFFID int,
@REVIEWNO int,
@COMMENTS varchar(2100),
@DESTINYVERIFIED int,
@DESTINYVERIFIEDBY int,
@DESTINYVERIFIEDDATE datetime,
@REASONFORSUSPENSIONID int,
@RETURNTOWORKDATE datetime,
@ContactDetailsUpdated int,
@RETENTION_STATUS_ID int,
@RETENTION_REASON_ID int,
@NonReview int,
@FalsifiedEventReason int,
@SusReqReasonID int,
@SusReqReturnDate datetime
AS
......
I have also run SQL Server Profiler against the session to see what was being passed to the database, however the error comes in before anything hits the database which appears to suggest the problem is within the ASP.NET side of things.
