#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2012
    Posts
    6
    Rep Power
    0

    Question Error updating INT field in database


    Having problems updating INT field in MS SQL Server 2008 database from .NET web application using ASP and VB.net.

    The error I get on the page is:

    Code:
    Server Error in '/DISCuSS' Application.
    --------------------------------------------------------------------------------
    
    Input string was not in a correct format. 
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    
    Exception Details: System.FormatException: Input string was not in a correct format.
    
    Source Error: 
    
    
    Line 81:         Dim strfldRankNew As Int32 = RTrim(LTrim(e.NewValues("fldRank")))
    Line 82: 
    Line 83:         Dim strfldOnCallAnalystOld As Int32 = RTrim(LTrim(e.OldValues("fldOnCallAnalyst")))
    Line 84:         Dim strfldOnCallAnalystNew As Int32 = RTrim(LTrim(e.NewValues("fldOnCallAnalyst")))
    Line 85: 
     
    
    Source File: C:\Users\gns1497\Documents\Visual Studio 2008\WebSites\DISCuSS\Dictionaries.aspx.vb    Line: 83 
    
    Stack Trace: 
    
    
    [FormatException: Input string was not in a correct format.]
       Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat) +204
       Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value) +99
    
    [InvalidCastException: Conversion from string "" to type 'Integer' is not valid.]
       Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value) +257
       Dictionaries.GvOnCall_Update(Object sender, GridViewUpdateEventArgs e) in C:\Users\gns1497\Documents\Visual Studio 2008\WebSites\DISCuSS\Dictionaries.aspx.vb:83
       System.Web.UI.WebControls.GridView.OnRowUpdating(GridViewUpdateEventArgs e) +133
       System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +720
       System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704
       System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
       System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
       System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
       System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
       System.Web.UI.WebControls.ImageButton.OnCommand(CommandEventArgs e) +111
       System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) +176
       System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
    My VB Code
    Code:
    Function SendSQLCommand(ByVal tsSQLCmd As String, ByVal tsqlConn As SqlConnection) As Integer
            Dim iRetVal As Integer = -1
            Dim sqlCmd As New SqlCommand(tsSQLCmd, tsqlConn)
            If tsqlConn.State <> ConnectionState.Open Then
                tsqlConn.Open()
            End If
            Try
                iRetVal = sqlCmd.ExecuteNonQuery()
            Catch ex As Exception
                Throw ex
            End Try
            Return iRetVal
        End Function
        Protected Sub GvOnCall_Update(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
            'Dim MgrPass As String
            Dim keyValue As Integer = e.Keys("fldOCID")
    
            Dim strfldOnCallGroupOld As String = RTrim(LTrim(e.OldValues("fldOnCallGroup").ToString()))
            Dim strfldOnCallGroupNew As String = RTrim(LTrim(e.NewValues("fldOnCallGroup").ToString()))
    
            Dim strfldStartOnCallOld As String = RTrim(LTrim(e.OldValues("fldStartOnCall").ToString()))
            Dim strfldStartOnCallNew As String = RTrim(LTrim(e.NewValues("fldStartOnCall").ToString()))
    
            Dim strfldEndOnCallOld As String = RTrim(LTrim(e.OldValues("fldEndOnCall").ToString()))
            Dim strfldEndOnCallNew As String = RTrim(LTrim(e.NewValues("fldEndOnCall").ToString()))
    
            Dim strfldRankOld As Int32 = RTrim(LTrim(e.OldValues("fldRank")))
            Dim strfldRankNew As Int32 = RTrim(LTrim(e.NewValues("fldRank")))
    
            Dim strfldOnCallAnalystOld As Int32 = RTrim(LTrim(e.OldValues("fldOnCallAnalyst")))
            Dim strfldOnCallAnalystNew As Int32 = RTrim(LTrim(e.NewValues("fldOnCallAnalyst")))
    
    
    
            'MgrPass = InputBox("Please enter the manager password.", "Password Required")
    
            'If MgrPass = "manage" Then
            gvOnCall_Updated(keyValue, strfldOnCallGroupNew, strfldStartOnCallNew, strfldEndOnCallNew, strfldRankNew, strfldOnCallAnalystNew)
            'Else
            'MsgBox("Only CSS managers may edit DISCuSS.", MsgBoxStyle.Critical, "Authentication Failed")
            'End If
    
            'http://support.microsoft.com/kb/301248
            'http://www.codeproject.com/Questions/334429/how-to-create-a-combobox-in-grid-view-control
    
        End Sub
    
        Protected Sub gvOnCall_Updated(ByVal fldOCID As Integer, ByVal fldOnCallGroup As String, ByVal fldStartOnCall As String, ByVal fldEndOnCall As String, _
                                       ByVal fldRank As Integer, ByVal fldOncallAnalyst As Integer)
            Dim sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("DiscussSQLConnectionString").ConnectionString)
            Dim sSql As XElement
            sSql = <sql>
                Update [tblOncallAnalystSchedule] SET [fldOnCallGroup] = '<%= fldOnCallGroup %>', 
                       [fldStartOnCall] = '<%= [fldStartOnCall] %>',[fldEndOnCall] = '<%= [fldEndOnCall] %>', 
                       [fldRank] = '<%= [fldRank] %>', [fldOnCallAnalyst] = '<%= [fldOncallAnalyst] %>'
                Where  [fldOCID] = '<%= [fldOCID] %>' </sql>
    
            'MsgBox(Err.Description, MsgBoxStyle.Critical, "SQL Error")
    
            SendSQLCommand(sSql.Value, sqlConn)
            sqlConn.Close()
        End Sub
    I use this same "SendSQLCommand" function with another form and it works fine. I pass the values from one sub to the other in the other form as well and that works fine. It just doesn't work on this form when performing an update and it appears that its due to the INT field. In SQL Server 2008 Mgmt Studio the field shows as type "INT", but I cant define the field as "INT" on the asp side. I have to use INT16, INT32, etc.


    ASP Code for the Data Source:
    Code:
    <asp:SqlDataSource ID="dsOncallGroup" runat="server" 
            ConnectionString="<%$ ConnectionStrings:DiscussSQLConnectionString %>" 
                            
            SelectCommand="SELECT tblOnCallAnalystSchedule.fldOnCallGroup, tblOnCallAnalystSchedule.fldStartOnCall, 
                            tblOnCallAnalystSchedule.fldEndOnCall, tblOnCallAnalystSchedule.fldRank, tblOnCallAnalystSchedule.fldOnCallAnalyst,
                            tblOnCallAnalystSchedule.fldOCID, tblEmployee.fldEmpLastName +', ' + tblEmployee.fldEmpFirstName as 'Analyst'
                           FROM tblEmployee INNER JOIN tblOnCallAnalystSchedule 
                           ON tblEmployee.fldEmpID = tblOnCallAnalystSchedule.fldOnCallAnalyst 
                           WHERE (tblOnCallAnalystSchedule.fldOnCallGroup = @fldOnCallGroup) 
                           AND (tblOnCallAnalystSchedule.fldEndOnCall &gt; GETDATE()) 
                           ORDER BY tblOnCallAnalystSchedule.fldOnCallGroup, tblOnCallAnalystSchedule.fldEndOnCall, 
                           tblOnCallAnalystSchedule.fldRank" 
            OldValuesParameterFormatString="original_{0}" 
            ConflictDetection="CompareAllValues" 
            DeleteCommand="DELETE FROM [tblOnCallAnalystSchedule] WHERE [fldOnCallGroup] = @original_fldOnCallGroup" 
            InsertCommand="INSERT INTO [tblOnCallAnalystSchedule] ([fldOnCallGroup], [fldStartOnCall], [fldEndOnCall], [fldRank], [fldOnCallAnalyst]) VALUES (@fldOnCallGroup, @fldStartOnCall, @fldEndOnCall, @fldRank, @fldOnCallAnalyst)" 
            
            
            
            UpdateCommand="UPDATE [tblOnCallAnalystSchedule] SET [fldOnCallGroup] = @fldOnCallGroup, [fldStartOnCall] = @fldStartOnCall, 
                           [fldEndOnCall] = @fldEndOnCall, [fldRank] = @fldRank, 'Analyst' = @'Analyst'
                           WHERE [fldOCID] = @fldOCID">
                           
            <SelectParameters>
                <asp:ControlParameter ControlID="cboOncallGroup" Name="fldOnCallGroup" 
                    PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
            <DeleteParameters>
                <asp:Parameter Name="original_fldOnCallGroup" Type="String" />
                <asp:Parameter DbType="DateTime2" Name="original_fldStartOnCall" />
                <asp:Parameter DbType="DateTime2" Name="original_fldEndOnCall" />
                <asp:Parameter Name="original_fldRank" Type="Int32" />
                <asp:Parameter Name="original_fldOnCallAnalyst" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="fldOnCallGroup" Type="String" ConvertEmptyStringToNull="false" />
                <asp:Parameter DbType ="DateTime2" Name="fldStartOnCall" />
                <asp:Parameter DbType ="DateTime2" Name="fldEndOnCall" />
                <asp:Parameter Name= "fldRank" Type="Int32" />
                <asp:Parameter Name= "fldOnCallAnalyst" Type="Int32" />
                <asp:Parameter Name="original_fldOnCallGroup" Type="String" ConvertEmptyStringToNull="false" />
                <asp:Parameter DbType="DateTime2" Name="original_fldStartOnCall" />
                <asp:Parameter DbType="DateTime2" Name="original_fldEndOnCall" />
                <asp:Parameter Name= "original_fldRank" Type="Int32" />
                <asp:Parameter Name= "original_fldOnCallAnalyst" Type="Int32" />
                <asp:Parameter Name= "fldOCID" Type="Int32" />
                <asp:Parameter Name= "original_fldOCID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="fldOnCallGroup" Type="String" />
                <asp:Parameter DbType="DateTime2" Name="fldStartOnCall" />
                <asp:Parameter DbType="DateTime2" Name="fldEndOnCall" />
                <asp:Parameter Name="fldRank" Type="Int32" />
                <asp:Parameter Name="fldOnCallAnalyst" Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>
    Here's the ASP code for my gridview that I am using to display the data:
    Code:
    <asp:Gridview ID="gvOnCallSchedule" runat="server" AllowSorting="True" AllowPaging="True" showfooter="true"
                OnRowUpdating="GvOnCall_Update" onRowCommand="gvOnCallSchedule_RowCommand" DataKeyNames="fldOCID"
                BackColor="Aqua" DataSourceID="dsOncallGroup" Font-Bold="True" 
                Font-Italic="False" Font-Overline="False" Font-Strikeout="False" 
                Font-Underline="False" ForeColor="#333333" HorizontalAlign="Justify" 
                CellPadding="4" GridLines="None" PageSize="20" AutoGenerateColumns ="false" Allowusertoaddrows="true" 
                OnSelectedIndexChanged="cboOnCallGroup_SelectedIndexChanged">
                
                <Columns>
                    <asp:TemplateField ShowHeader="false"> 
                        <FooterTemplate>
                            <asp:ImageButton ID="AddButton" runat="server" CommandName="Insert" ImageURL="~/images/New.png" 
                                 Text="Add" ToolTip="Add New On Call Record" />                
                        </FooterTemplate>
                        
                        <EditItemTemplate>
                                <asp:ImageButton ID="UpdateButton" runat="server" CausesValidation="False" CommandName="Update" 
                                        ImageUrl="~/images/Save.png" Text="Update" ToolTip="Update" />
                                        &nbsp;
                                <asp:ImageButton ID="CancelButton" runat="server" CausesValidation="false" CommandName="Cancel"
                                        ImageURL="~/images/Cancel.png" Text="Cancel" ToolTip="Cancel" />
                        </EditItemTemplate>
                        
                        <ItemTemplate>
                            <asp:ImageButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" 
                                        ImageUrl="~/images/Edit.gif" Text="Edit" ToolTip="Edit" />
                                        &nbsp;
                            <asp:ImageButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" 
                                        ImageUrl="~/images/Delete.png" Text="Delete" ToolTip="Delete" />
                        </ItemTemplate>
                        <ItemStyle Wrap="false" />   
                    </asp:TemplateField>                          
                                                                      
                    <asp:TemplateField headertext="On Call Group">
                        <ItemTemplate>
                            <asp:DropDownList ID="ddlOCGroup" width="250px" runat="server" datavaluefield="fldOnCallGroup" Enabled="false"
                                DataSourceID= "dsListbox" SelectedValue="<%# Bind('fldOnCallGroup') %>"> 
                            </asp:DropDownList>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:DropDownList ID="ftrddlOCGroup" width="250px" runat="server" datavaluefield="fldOnCallGroup"
                                DataSourceID= "dsListbox" SelectedValue="<%# Bind('fldOnCallGroup') %>"> 
                            </asp:DropDownList>
                        </FooterTemplate>
                    </asp:TemplateField>
                    
                    
                    <asp:TemplateField headertext="Start Date/Time">
                        <ItemTemplate>
                            <asp:Textbox ID="txtStartOnCall" runat="server" Text='<%# Bind("fldStartOnCall") %>'> 
                                </asp:Textbox>
                            <asp:Image ID="calpopup" runat="server" ImageUrl="~/images/calendar2.png" />
                            <asp:CalendarExtender ID="StartOnCallCal" runat="server" TargetControlID="txtStartOnCall" 
                                    PopupButtonID="calpopup" PopupPosition="Right" format="MM/dd/yyyy hh:mm:ss tt">
                            </asp:CalendarExtender>
                        </ItemTemplate>
                        <FooterTemplate>              
                            <asp:Textbox ID="txtStartOnCallFtr" runat="server" Text='<%# Bind("fldStartOncall") %>'> 
                                </asp:Textbox>
                            <asp:Image ID="calpopupFtr" runat="server" ImageUrl="~/images/calendar2.png" />    
                            <asp:CalendarExtender ID="StartOnCallCalFtr" runat="server" TargetControlID="txtStartOnCallFtr" 
                                    PopupButtonID="calpopupFtr" PopupPosition="Right" format="MM/dd/yyyy hh:mm:ss tt">
                            </asp:CalendarExtender>
                            
                        </FooterTemplate> 
                    </asp:TemplateField>
                    
                    <asp:TemplateField headertext="End Date/Time">
                        <ItemTemplate>
                            <asp:Textbox ID="txtEndOnCall" runat="server" Text='<%# Bind("fldEndOnCall") %>'> 
                                </asp:Textbox>
                            <asp:Image ID="calpopup2" runat="server" ImageUrl="~/images/calendar2.png" />
                            <asp:CalendarExtender ID="EndOnCallCal" runat="server" TargetControlID="txtEndOnCall" 
                                    PopupButtonID="calpopup2" PopupPosition="Right" format="MM/dd/yyyy hh:mm:ss tt">
                            </asp:CalendarExtender>
                        </ItemTemplate>
                        <FooterTemplate>              
                            <asp:Textbox ID="txtEndOnCallFtr" runat="server" Text='<%# Bind("fldEndOnCall") %>'> 
                                </asp:Textbox>
                            <asp:Image ID="calpopup2Ftr" runat="server" ImageUrl="~/images/calendar2.png" />    
                            <asp:CalendarExtender ID="EndOnCallCalFtr" runat="server" TargetControlID="txtEndOnCallFtr" 
                                    PopupButtonID="calpopup2Ftr" PopupPosition="Right" format="MM/dd/yyyy hh:mm:ss tt">
                            </asp:CalendarExtender>
                            
                        </FooterTemplate> 
                    </asp:TemplateField>
                    
                                                   
                    <asp:TemplateField headertext="Call Order">
                        <ItemTemplate>
                            <asp:Textbox ID="lblCallOrder" runat="server" Text='<%# Bind("fldRank") %>'> 
                                </asp:Textbox>
                        </ItemTemplate>
                        <FooterTemplate>              
                            <asp:Textbox ID="txtCallOrderFtr" runat="server" Text='<%# Bind("fldOnCallGroup") %>'> 
                                </asp:Textbox>
                        </FooterTemplate> 
                    </asp:TemplateField>
                    
                                                                     
                    <asp:TemplateField HeaderText="On Call Analyst">
                        <ItemTemplate>
                            <asp:DropDownList ID="ddlAnalyst" runat="server" datavaluefield="Analyst"
                                DataSourceID= "dsAnalyst" SelectedValue="<%# Bind('Analyst') %>"> 
                            </asp:DropDownList>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:DropDownList ID="ddlAnalyst" runat="server" datavaluefield="Analyst"
                                DataSourceID= "dsAnalyst" SelectedValue="<%# Bind('Analyst') %>"> 
                            </asp:DropDownList>
                        </FooterTemplate>
                    </asp:TemplateField>
                    
       
                    
                </Columns>
                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <EditRowStyle BackColor="#7C6F57" />
                <AlternatingRowStyle BackColor="White" />
                
            </asp:Gridview>

    Any help is greatly appreciated. Thanks!
  2. #2
  3. I do .NET for a living
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Sep 2003
    Location
    Florida
    Posts
    1,549
    Rep Power
    211
    First, what does the field e.OldValues("fldOnCallAnalyst") contain at the time of the error? If you don't know, you can do a response.write followed by a response.end statement just before the line that errors.

    Next, in the asp.net framework, Integer is what you're looking for (in this case Int32 is the same thing).
    Also, instead of using RTrim(LTrim, you can simply use Trim: Trim(e.OldValues("fldOnCallAnalyst")).

    If you know the field contains an integer (and is not null), then you should probably do something like this:
    Dim strfldOnCallAnalystOld As Int32 = Integer.Parse(e.OldValues("fldOnCallAnalyst"))

    If the field may contain a null value, then you should check for that before doing the parse:
    Dim strfldOnCallAnalystOld As Int32 = IIf(e.OldValues("fldOnCallAnalyst") is DBNull.Value, 0, Integer.Parse(e.OldValues("fldOnCallAnalyst")))
    This will return a zero if the field is null, else it will return the actual integer value contained in the field.
    Roger (.NET MCP)

Similar Threads

  1. Checkbox activates another field in a GUI???
    By MaximusPrime in forum .NET Development
    Replies: 9
    Last Post: November 14th, 2012, 12:43 PM
  2. Sum field questions
    By Romper in forum Microsoft Access Help
    Replies: 12
    Last Post: October 2nd, 2012, 08:04 PM
  3. Linked tables not updating
    By bcoleburn in forum Microsoft Access Help
    Replies: 2
    Last Post: December 9th, 2010, 10:05 PM
  4. ADODB.Field error '800a0bcd'
    By msuqi in forum ASP Development
    Replies: 5
    Last Post: February 9th, 2006, 11:46 AM
  5. HTTP 500.100 - Internal Server Error - ASP error
    By satsteve in forum Microsoft IIS
    Replies: 12
    Last Post: October 20th, 2004, 05:56 PM

IMN logo majestic logo threadwatch logo seochat tools logo