Input string was not in a correct format.

Question

User-1333904898 posted

Hi all -

I'm hoping this is a simple question. I'm trying to insert a row (from a gridview footer) using the codebehind file. I found some samples of this around the web and kind of pieced together my own version of it.  I'm throwing an 'Input string was not in a correct format' error when trying to actually insert the row though.

I'm new to VB and am not sure what I may have done wrong. Any ideas?

Imports System.Data

Partial Class Profile
    Inherits System.Web.UI.Page

    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
        If e.CommandName = "InsertNew" Then
            Dim intCUSTOMER_ID As Int32 = Convert.ToInt32(GridView1.DataKeyNames(0))
            Dim strSERVICE_DESC As String = DirectCast(GridView1.FooterRow.FindControl("InsertServiceDesc"), TextBox).Text()
            Dim intSERVICE_FEE As Decimal = Convert.ToDecimal(DirectCast(GridView1.FooterRow.FindControl("InsertServiceFee"), TextBox).Text())

            SqlDataSource2.InsertParameters("SERVICE_DESC").DefaultValue = strSERVICE_DESC
            SqlDataSource2.InsertParameters("SERVICE_FEE").DefaultValue = intSERVICE_FEE
            SqlDataSource2.InsertParameters("CUSTOMER_ID").DefaultValue = intCUSTOMER_ID
            SqlDataSource2.Insert()
        End If
    End Sub


End Class

 

Answer 1

User-1226263862 posted

Do all your textboxes have data in them when submitting the info for insert?  Instead of trying to take the text directly out using DirectCast try this:

Dim txtDesc As TextBox = TryCast(GridView1.FooterRow.FindControl("InsertServiceDesc"), TextBox)
Dim txtFee As TextBox = TryCast(GridView1.FooterRow.FindControl("InsertServiceFee"), TextBox)

Dim strDesc As String = Nothing
Dim decFee As Decimal = 0.0

If Not String.IsNullOrEmpty(txtDesc.Text) Then
    strDesc = txtDesc.Text
End If

If Not String.IsNullOrEmpty(txtFee.Text) Then
    decFee = Convert.ToDecimal(txtFee.Text)
End If

SqlDataSource2.InsertParameters("SERVICE_DESC").DefaultValue = strDesc
SqlDataSource2.InsertParameters("SERVICE_FEE").DefaultValue = decFee

You may also want to check the datatype prior to insert as well.  This can be done with ASP.NET validation controls in the template fields of the gridview very easy.  You can also put some required field validators in there too.

Answer 2

User-1333904898 posted

Thanks for the quick response. I'm getting closer...

I'm trying also to write out a field called CUSTOMER_ID. This field is in the DataKeyNames element of my DataGrid. It's a Int32 type field in my database. It's a required field, that is non-entereable/non-visible to the end-user.  

SQL Table:

Field Name	Data Type
ID	Integer (primary key)
SERVICE_DESC	VarChar(Max)
SERVICE_FEE	Decimal
CUSTOMER_ID	Integer

Here's what my GridView Looks like

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2"
                            DataKeyNames="CUSTOMER_ID" Width="100%" ShowFooter="True" 
                            OnRowCommand="GridView1_RowCommand">
                            <Columns>
                                <asp:TemplateField HeaderText="Commands">
                                    <ItemTemplate>
                                        <asp:LinkButton runat="server" ID="Edit" Text="Edit" CommandName="Edit" />
                                        <asp:LinkButton runat="server" ID="Delete" Text="Delete" CommandName="Delete" />
                                    </ItemTemplate>
                                    <EditItemTemplate>
                                        <asp:LinkButton runat="server" ID="Update" Text="Update" CommandName="Update" />
                                        <asp:LinkButton runat="server" ID="Cancel" Text="Cancel" CommandName="Cancel" />
                                    </EditItemTemplate>
                                    <FooterTemplate>
                                        <asp:LinkButton runat="server" ID="Insert" Text="Insert" CommandName="InsertNew" />
                                    </FooterTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Service Description" SortExpression="SERVICE_DESC">
                                    <EditItemTemplate>
                                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("SERVICE_DESC") %>'></asp:TextBox>
                                    </EditItemTemplate>
                                    <ItemTemplate>
                                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("SERVICE_DESC") %>'></asp:Label>
                                    </ItemTemplate>
                                    <FooterTemplate>
                                        <asp:TextBox runat="server" ID="InsertServiceDesc" Text='<%# Bind("SERVICE_DESC") %>' />
                                    </FooterTemplate>
                                </asp:TemplateField>
                                <asp:TemplateField HeaderText="Fee" SortExpression="SERVICE_FEE">
                                    <EditItemTemplate>
                                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("SERVICE_FEE") %>'></asp:TextBox>
                                    </EditItemTemplate>
                                    <ItemTemplate>
                                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("SERVICE_FEE", "{0:C}") %>'></asp:Label>
                                    </ItemTemplate>
                                    <FooterTemplate>
                                        <asp:TextBox runat="server" ID="InsertServiceFee" Text='<%# Bind("SERVICE_FEE") %>' />
                                    </FooterTemplate>
                                </asp:TemplateField>
                            </Columns>
                            <AlternatingRowStyle BackColor="LightGray" />
                        </asp:GridView>

 

 

Answer 3

User-1226263862 posted

Are you trying to get the newly inserted ID or ones which are already created? 

If you are trying to get a newly inserted ID try this:

'grab the output parameter value and assign it to a variable in my page class [serviceId]
Protected Sub SqlDataSource2_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)
    serviceId = CInt(DirectCast(e.Command.Parameters("serviceIdparam"), IDbDataParameter).Value)
End Sub

' You insert the output parameter in the Inserting event
Protected Sub SqlDataSource2_Inserting(ByVal sender As Object, ByVal e As SqlDataSourceCommandEventArgs)
    Dim param As New System.Data.SqlClient.SqlParameter()
    param.DbType = DbType.Int32
    param.Direction = ParameterDirection.Output
    param.ParameterName = "serviceIdparam"
    
    e.Command.Parameters.Add(param)
End Sub

After your insert is complete put this statement at the end in order for the output parameter to return a value:

SET @serviceId = Scope_Identity() 

 

Answer 4

User-1333904898 posted

I'm trying to get at the CUSTOMER_ID that I'm currently viewing.

My gridview pulls the SERVICE_DESC and SERVICE_FEE determined by the CUSTOMER_ID in a FormView from above.  This way I only show the Service/Fee data for a specific customer. In my SQLDataSource I use a control parameter to get at the CUSTOMER_ID from the formview.

<SelectParameters>
                        <asp:ControlParameter ControlID="FormView1" DefaultValue="" Name="CUSTOMER_ID" PropertyName="SelectedValue"
                            Type="Int32" />
                    </SelectParameters>

I'm wonder if I should instead try to load CUSTOMER_ID into a session state and reference it from there?

 <%  Session("CUSTOMER_ID") = ??? %> 

 

 

Answer 5

User-1333904898 posted

Ok - I got it figured out. Your previous post helped me track down a few other Asp.Net discussions and I was able to piece this together. Thanks a ton for your help.

For others - Here's the working VB code.

Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs)
        If e.CommandName = "InsertNew" Then
            Dim intId As Integer = FormView1.SelectedValue.ToString()
            Dim txtDesc As TextBox = TryCast(GridView1.FooterRow.FindControl("InsertServiceDesc"), TextBox)
            Dim txtFee As TextBox = TryCast(GridView1.FooterRow.FindControl("InsertServiceFee"), TextBox)

            Dim strDesc As String = Nothing
            Dim decFee As Decimal = 0.0

            If Not String.IsNullOrEmpty(txtDesc.Text) Then
                strDesc = txtDesc.Text
            End If

            If Not String.IsNullOrEmpty(txtFee.Text) Then
                decFee = Convert.ToDecimal(txtFee.Text)
            End If

            SqlDataSource2.InsertParameters("SERVICE_DESC").DefaultValue = strDesc
            SqlDataSource2.InsertParameters("SERVICE_FEE").DefaultValue = decFee
            SqlDataSource2.InsertParameters("CUSTOMER_ID").DefaultValue = intId
            SqlDataSource2.Insert()
        End If
    End Sub

 

Answer 6

User1364706731 posted

Refer this link

http://www.asp.net/(S(pdfrohu0ajmwt445fanvj2r3))/learn/data-access/tutorial-15-vb.aspx