Having problems updating INT field in MS SQL Server 2008 database from .NET web application using ASP and VB.net.
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
'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:
<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 > 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" />
<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" />
<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>
|