Input string was not in a correct format.

[rohit]

Hi, 

I am saving my grid data into sql database. But i getting error:
Exception Details: System.FormatException: Input string was not in a correct format.
Source Error:

Line 361: cmd.Parameters("@DOC_Portions_Served_Adults_ALC").Value = str14 Line 362: con.Open() Line 363: cmd.ExecuteNonQuery() Line 364: Line 365: con.Close()

Stack Trace:

[FormatException: Input string was not in a correct format.] System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +9594283 System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +119 System.String.System.IConvertible.ToInt32(IFormatProvider provider) +46 System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +385 System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +5034544 [FormatException: Failed to convert parameter value from a String to a Int32.] System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +5033757 System.Data.SqlClient.SqlParameter.GetCoercedValue() +32 System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +103 System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters) +126 System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc) +73 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137 DSS_Project.ProductionRecordmain.AddGrid() in C:\Documents and Settings\911313\My Documents\Visual Studio 2010\Projects\DSS Project\ProductionRecordmain.aspx.vb:363 DSS_Project.ProductionRecordmain.btnsave_Click(Object sender, EventArgs e) in C:\Documents and Settings\911313\My Documents\Visual Studio 2010\Projects\DSS Project\ProductionRecordmain.aspx.vb:190 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112 System.Web.UI.WebControls.Button.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) +5563

Public Sub btnsave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsave.Click
        AddGrid()
    End Sub
Public Sub AddGrid()
        For Each item As GridDataItem In GD_Prod.Items

            ''''''getting the bound fields values 
            Dim str0 As String = item("port_recipe_num").Text
            Dim str1 As String = item("DESC_ALT").Text
            Dim str2 As String = item("port_no_servings").Text
            Dim str5 As String = item("STD_NUM_OF_SERVING").Text
            Dim str13 As String = item("POSStudents").Text
            Dim str14 As String = item("POSAdultsALC").Text
     
            ''''''getting the template fields value   
            Dim tx3 As TextBox = DirectCast(item.FindControl("TxtSTUPort"), TextBox)
            Dim tx4 As TextBox = DirectCast(item.FindControl("TxtAAPort"), TextBox)
            Dim tx6 As TextBox = DirectCast(item.FindControl("TxtFPort"), TextBox)
            Dim tx7 As TextBox = DirectCast(item.FindControl("TxtQoFUsed"), TextBox)
            Dim tx8 As TextBox = DirectCast(item.FindControl("TxtPPort"), TextBox)
            Dim tx9 As TextBox = DirectCast(item.FindControl("TxtLOPort"), TextBox)
            Dim tx10 As TextBox = DirectCast(item.FindControl("TxtBFPort"), TextBox)
            Dim tx11 As DropDownList = DirectCast(item.FindControl("ddlLOCode"), DropDownList)
            Dim tx12 As TextBox = DirectCast(item.FindControl("TxtTSPort"), TextBox)

            Dim str3 As String = (tx3.Text)
            Dim str4 As String = (tx4.Text)
            Dim str6 As String = (tx6.Text)
            Dim str7 As String = (tx7.Text)
            Dim str8 As String = (tx8.Text)
            Dim str9 As String = (tx9.Text)
            Dim str10 As String = (tx10.Text)
            Dim str11 As String = (tx11.SelectedItem.Value)
            Dim str12 As String = (tx12.Text)

            Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("FConnectionString").ConnectionString)
            Dim cmd As New SqlCommand("P_FN_PR_InsertGridData", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@Pkey", Data.SqlDbType.Char)
            cmd.Parameters("@Pkey").Value = lblPKey.Text.Trim
            cmd.Parameters.Add("@PL_Recipe_Number", Data.SqlDbType.Char)
            cmd.Parameters("@PL_Recipe_Number").Value = str0
            cmd.Parameters.Add("@PL_Recipe_Name", Data.SqlDbType.Char)
            cmd.Parameters("@PL_Recipe_Name").Value = str1
            cmd.Parameters.Add("@PL_Portion_Size", Data.SqlDbType.Char)
            cmd.Parameters("@PL_Portion_Size").Value = str2
            cmd.Parameters.Add("@PL_Student_Portions_Planned", Data.SqlDbType.Int)
            cmd.Parameters("@PL_Student_Portions_Planned").Value = str3
            cmd.Parameters.Add("@PL_Adults_ALC__Portions_Planned", Data.SqlDbType.Int)
            cmd.Parameters("@PL_Adults_ALC__Portions_Planned").Value = str4
            cmd.Parameters.Add("@PL_Total__Portions_Planned", Data.SqlDbType.Int)
            cmd.Parameters("@PL_Total__Portions_Planned").Value = str5
            cmd.Parameters.Add("@PL_Factor_Portions", Data.SqlDbType.Char)
            cmd.Parameters("@PL_Factor_Portions").Value = str6
            cmd.Parameters.Add("@DOC_Quantity_Of_Food_Used", Data.SqlDbType.Char)
            cmd.Parameters("@DOC_Quantity_Of_Food_Used").Value = str7
            cmd.Parameters.Add("@DOC_Portions_Prepared ", Data.SqlDbType.Int)
            cmd.Parameters("@DOC_Portions_Prepared ").Value = str8
            cmd.Parameters.Add("@DOC_Portions_Brought_Forward ", Data.SqlDbType.Int)
            cmd.Parameters("@DOC_Portions_Brought_Forward ").Value = str9
            cmd.Parameters.Add("@DOC_Portions_Leftover", Data.SqlDbType.Int)
            cmd.Parameters("@DOC_Portions_Leftover").Value = str10
            cmd.Parameters.Add("@DOC_Leftover_Code", Data.SqlDbType.Char)
            cmd.Parameters("@DOC_Leftover_Code").Value = str11
            cmd.Parameters.Add("@DOC_Total_Portions_Served", Data.SqlDbType.Int)
            cmd.Parameters("@DOC_Total_Portions_Served").Value = str12
            cmd.Parameters.Add("@DOC_Portions_Served_Students", Data.SqlDbType.Int)
            cmd.Parameters("@DOC_Portions_Served_Students").Value = str13
            cmd.Parameters.Add("@DOC_Portions_Served_Adults_ALC", Data.SqlDbType.Int)
            cmd.Parameters("@DOC_Portions_Served_Adults_ALC").Value = str14
            con.Open()
            cmd.ExecuteNonQuery ()
            con.Close()
Next
End Sub

Stored Procedure:
ALTER PROCEDURE [DBO].P_FN_PR_InsertGridData
(
@Pkey char(15) ,
@PL_Recipe_Number char(4),
@PL_Recipe_Name char(20),
@PL_Portion_Size char(6),
@PL_Student_Portions_Planned int,
@PL_Adults_ALC__Portions_Planned int,
@PL_Total__Portions_Planned int,
@PL_Factor_Portions char(10),
@DOC_Quantity_Of_Food_Used char(10),
@DOC_Portions_Prepared int,
@DOC_Portions_Brought_Forward int,
@DOC_Portions_Leftover int,
@DOC_Leftover_Code char(3),
@DOC_Total_Portions_Served int,
@DOC_Portions_Served_Students int,
@DOC_Portions_Served_Adults_ALC int
)
AS
INSERT INTO FNProdRecDetails (Date_Loc_Type, PL_Recipe_Number, PL_Recipe_Name, PL_Portion_Size, PL_Student_Portions_Planned, PL_Adults_ALC__Portions_Planned, 
PL_Total__Portions_Planned, PL_Factor_Portions, DOC_Quantity_Of_Food_Used, DOC_Portions_Prepared, DOC_Portions_Brought_Forward, DOC_Portions_Leftover,
DOC_Leftover_Code, DOC_Total_Portions_Served, DOC_Portions_Served_Students, DOC_Portions_Served_Adults_ALC)
VALUES (@Pkey , @PL_Recipe_Number, @PL_Recipe_Name, @PL_Portion_Size, @PL_Student_Portions_Planned, @PL_Adults_ALC__Portions_Planned,
@PL_Total__Portions_Planned, @PL_Factor_Portions, @DOC_Quantity_Of_Food_Used, @DOC_Portions_Prepared, @DOC_Portions_Brought_Forward, @DOC_Portions_Leftover,
@DOC_Leftover_Code, @DOC_Total_Portions_Served, @DOC_Portions_Served_Students, @DOC_Portions_Served_Adults_ALC)

[Shinu]

Hello,

You are getting this error, beacuse of inserting  string value into integer field. You need to convert the data into correct format before inserting. For eg :

VB.NET:

Dim tx3 As TextBox = DirectCast(item.FindControl("TxtSTUPort"), TextBox)
'accessing value from TextBox
Dim str3 As Integer = Convert.ToInt16(tx3.Text)
'converting to correct format(since 'PL_Student_Portions_Planned' is an integer field in db)
.  .   .  .  .  .   .  . .
'store the integer data to db
cmd.Parameters.Add("@PL_Student_Portions_Planned", System.Data.SqlDbType.Int)
cmd.Parameters("@PL_Student_Portions_Planned").Value = str3

-Shinu.

[rohit]

Hi Shinu,

I tried what you suggest, but still same error.
I tried many mehtod to convert into integer.
VAL, CONVERT.TOINT32, INTEGER.PARSE, INTEGER.TRYPARSE, CINT

[Tsvetina]

Hi,

Have you tried debugging your code to confirm that the conversion has gone through successfully and the value which you are passing as a parameter is already of type Int?

Kind regards,
Tsvetina
the Telerik team

[rohit]

Can anyone send me a sample project/example for saving radgrid with bound columns and item template coulmns (with textbox and dropdownlist)

[Princy]

Hello Rohit,

Here is the sample code that I tried in my application. hope this helps.

ASPX:

<telerik:RadGrid ID="RadGrid1" AutoGenerateColumns="False" runat="server" DataSourceID="SqlDataSource1">
    <MasterTableView CommandItemDisplay="Top" DataKeyNames="EmployeeID">
        <Columns>
            <telerik:GridBoundColumn DataField="EmployeeID"  UniqueName="EmployeeID">
            </telerik:GridBoundColumn>
            <telerik:GridTemplateColumn>
                <ItemTemplate>
                    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                    <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"
                        DataTextField="user_role" DataValueField="user_role">
                    </asp:DropDownList>
                </ItemTemplate>
            </telerik:GridTemplateColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>
<br />
<asp:Button ID="Button1" runat="server" Text="Save" OnClick="Button1_Click" />

Vb.Net:

Protected Sub Button1_Click(sender As Object, e As EventArgs)
    Dim item As GridDataItem = DirectCast(RadGrid1.Items(0), GridDataItem)
    'accessing first grid item
    Dim id As Integer = Convert.ToInt16(item("EmployeeID").Text)
    Dim txtname As TextBox = DirectCast(item.FindControl("TextBox1"), TextBox)
    Dim name As String = txtname.Text
    Dim list As DropDownList = DirectCast(item.FindControl("DropDownList1"), DropDownList)
    Dim user As String = list.SelectedValue
    Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
    Dim cmd As New SqlCommand("INSERT INTO Employees (EmployeeID,FirstName,user_role) values(@EmployeeID,@FirstName,@user_role) ", con)
    cmd.Parameters.Add(New SqlParameter("@EmployeeID", id))
    cmd.Parameters.Add(New SqlParameter("@FirstName", name))
    cmd.Parameters.Add(New SqlParameter("@user_role", user))
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
End Sub

Thanks,
Princy.

[rohit]

I am saving grid data. Grid contains one textbox in item template column. i am binding grid on grid_load event
and saving grid on btn_save_click event.
Bound columns data is saving,and i can see values in debug.
But problem is item template(textbox) values are not saving, while debuging it shows me value = "".
Please help.
Any help is appreciated. Below is code i am using.

VB Code:
Public Sub btnsave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsave.Click
AddG()
End Sub
Public Sub AddG()
For Each item As GridDataItem In GD_Prod.Items
Dim tx3 As TextBox = DirectCast(item("PL_STUDENT_Portions_Planned").FindControl("TxtSTUPort"), TextBox)
Dim str3 As string = tx3.Text
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("FConnectionString").ConnectionString)
Dim cmd As New SqlCommand("P_FN_PR_InsertGridData", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@PL_Student_Portions_Planned", System.Data.SqlDbType.VarChar)
cmd.Parameters("@PL_Student_Portions_Planned").Value = str3
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Next
End Sub

ASPX Code:

<telerik:RadGrid ID="GD_Prod" runat="server" AllowPaging="True" ShowHeader="False"
AutoGenerateColumns="False" GridLines="None" Height="173px" Skin="Outlook" 
Width="1107px" PageSize="1">
<MasterTableView>
<Columns>
<telerik:GridTemplateColumn UniqueName="PL_STUDENT_Portions_Planned" 
>
<ItemTemplate>
<asp:TextBox ID="TxtSTUPort" runat="server" Height="16px"
Width="30px" ></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
ControlToValidate="TxtSTUPort" ErrorMessage="*" ForeColor="#CC3300" 
SetFocusOnError="True" Display="Dynamic"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" 
ControlToValidate="TxtSTUPort" ErrorMessage="Number" ForeColor="#CC3300" 
SetFocusOnError="True" ValidationExpression="[0-9]*" Display="Dynamic" 
Font-Size="6px"></asp:RegularExpressionValidator>
</ItemTemplate>
<HeaderStyle Width="19px" />
</telerik:GridTemplateColumn>
</Columns>

</telerik:RadGrid>
<asp:Button ID="btnsave" runat="server" Height="20px" Text="SAVE" Width="50px"
ToolTip="Click to Save " BackColor="#DFEFFF" ForeColor="#003399" />

Stored Proc:
ALTER PROCEDURE [DBO].P_FN_PR_InsertGridData
(
@PL_Student_Portions_Planned varchar(10) = 0
)
AS
begin
INSERT INTO FNProdRecDetails (PL_Student_Portions_Planned)
values(@PL_Student_Portions_Planned)
End
Return

[Tsvetina]

Hello Rohit,

Since this question is already being discussed in the separate forum thread that you have opened for it, I am closing this one in order to avoid duplicate posts on the subject. In case you have any observations or further questions to ask on that matter, please post in the other thread.

Best wishes,
Tsvetina
the Telerik team