2 Replies - 2069 Views - Last Post: 11 October 2012 - 08:05 AM Rate Topic: -----

#1 gns1497  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 27-June 12

Problem updating INT field in MS SQL 2008 database

Posted 10 October 2012 - 01:06 PM

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

This is the error produced:
 
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.net 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         
    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:
<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:
<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!

Is This A Good Question/Topic? 0
  • +

Replies To: Problem updating INT field in MS SQL 2008 database

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Problem updating INT field in MS SQL 2008 database

Posted 11 October 2012 - 04:01 AM

The easiest way to see what is the problem is to manually execute the command in SqlServer Manangement Studio. You have to ways to do this:
1. Debugging - Before sending the command, take the value and run in
2. use SQL Profiler(I don't think it is available in Express Edition). Using Sql Profiler, you will see all the commands that run on that server and can make easily filters to get to your problem.

If you still have problems identifing the cause, post that command and we'll try to help you from that point.
Was This Post Helpful? 0
  • +
  • -

#3 zeeshanef  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 72
  • Joined: 14-April 12

Re: Problem updating INT field in MS SQL 2008 database

Posted 11 October 2012 - 08:05 AM

Dim strfldRankNew As Int32 = RTrim(LTrim(e.NewValues("fldRank")))
Dim strfldOnCallAnalystNew As Int32 = RTrim(LTrim(e.NewValues("fldOnCallAnalyst")))


What will happen if new value is blank, or new value has some Chars.
you should make sure that newvalue is Number before storing it into int or int32 data types.

you can use Validators Control in your EditItemTemplate.

This post has been edited by zeeshanef: 11 October 2012 - 08:06 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1