Inserting data from grid into SQL Database

Question

0.00/5 (No votes)

See more:

On BtnClick, I am inserting all data from my RADGRID into my SQL database. But it was just inserting 'zeros' for all records.
Please help me what i am missing in the code. My code is below.
VB.NET-

Public Sub AddGrid()
        For Each item As GridDataItem In GD_Prod.Items
            'getting the bound fields values 
         Dim str0 As String = Nothing
            If String.IsNullOrEmpty(str0) Then
                str0 = "0"
            Else
                str0 = (item.Cells(0).Text.Trim)
            End If
            Dim str1 As String = Nothing
            If String.IsNullOrEmpty(str1) Then
                str1 = "0"
            Else
                str1 = (item.Cells(1).Text.Trim)
            End If
            Dim str2 As String = Nothing
            If String.IsNullOrEmpty(str2) Then
                str2 = "0"
            Else
                str2 = (item.Cells(2).Text.Trim)
            End If
            Dim str5 As String = Nothing
            If String.IsNullOrEmpty(str5) Then
                str5 = "0"
            Else
                str5 = Integer.Parse(item.Cells(5).Text.Trim)
            End If
            Dim str13 As String = Nothing
            If String.IsNullOrEmpty(str13) Then
                str13 = "0"
            Else
                str13 = Integer.Parse(item.Cells(13).Text.Trim)
            End If
            Dim str14 As String = Nothing
            If String.IsNullOrEmpty(str14) Then
                str14 = "0"
            Else
                str14 = Integer.Parse(item.Cells(14).Text.Trim)
            End If
            ''''''getting the template fields value   
            Dim tx3 As TextBox = DirectCast(item.Cells(3).FindControl("TxtSTUPort"), TextBox)
            Dim tx4 As TextBox = DirectCast(item.Cells(4).FindControl("TxtAAPort"), TextBox)
            Dim tx6 As TextBox = DirectCast(item.Cells(6).FindControl("TxtFPort"), TextBox)
            Dim tx7 As TextBox = DirectCast(item.Cells(7).FindControl("TxtQoFUsed"), TextBox)
            Dim tx8 As TextBox = DirectCast(item.Cells(8).FindControl("TxtPPort"), TextBox)
            Dim tx9 As TextBox = DirectCast(item.Cells(9).FindControl("TxtLOPort"), TextBox)
            Dim tx10 As TextBox = DirectCast(item.Cells(10).FindControl("TxtBFPort"), TextBox)
            Dim tx11 As DropDownList = DirectCast(item.Cells(11).FindControl("ddlLOCode"), DropDownList)
            Dim tx12 As TextBox = DirectCast(item.Cells(12).FindControl("TxtTSPort"), TextBox)    
            ' inserting the template values 
            Dim str3 As String = Nothing
            If String.IsNullOrEmpty(str3) Then
                str3 = "0"
            Else
                str3 = CType((tx3.Text.Trim), Integer)
            End If
            Dim str4 As String = Nothing
            If String.IsNullOrEmpty(str4) Then
                str4 = "0"
            Else
                str4 = CType((tx4.Text.Trim), Integer)
            End If
            Dim str6 As String = Nothing
            If String.IsNullOrEmpty(str6) Then
                str6 = "0"
            Else
                str6 = CType((tx6.Text.Trim), Integer)
            End If
            Dim str7 As String = Nothing
            If String.IsNullOrEmpty(str7) Then
                str7 = "0"
            Else
                str7 = CType((tx7.Text.Trim), Integer)
            End If
            Dim str8 As String = Nothing
            If String.IsNullOrEmpty(str8) Then
                str8 = "0"
            Else
                str8 = CType((tx8.Text.Trim), Integer)
            End If
            Dim str9 As String = Nothing
            If String.IsNullOrEmpty(str9) Then
                str9 = "0"
            Else
                str9 = CType((tx9.Text.Trim), Integer)
            End If
            Dim str10 As String = Nothing
            If String.IsNullOrEmpty(str10) Then
                str10 = "0"
            Else
                str10 = CType((tx10.Text.Trim), Integer)
            End If
            Dim str11 As String = Nothing
            If String.IsNullOrEmpty(str11) Then
                str11 = "0"
            Else
                str11 = CType((tx11.SelectedValue), Integer)
            End If
            Dim str12 As String = Nothing
            If String.IsNullOrEmpty(str12) Then
                str12 = "0"
            Else
                str12 = CType((tx12.Text.Trim), Integer)
            End If
 Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("FNConnectionString").ConnectionString)
            Dim cmd As New SqlCommand("P_FN_PR_InsertGridData", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@Pkey", SqlDbType.Char).Value = (lblPKey.Text).Trim
            cmd.Parameters.Add("@PL_Recipe_Number", SqlDbType.Char).Value = str0
            cmd.Parameters.Add("@PL_Recipe_Name", SqlDbType.Char).Value = str1
            cmd.Parameters.Add("@PL_Portion_Size", SqlDbType.Char).Value = str2
            cmd.Parameters.Add("@PL_Student_Portions_Planned", SqlDbType.Int).Value = CType((str3), Integer)
            cmd.Parameters.Add("@PL_Adults_ALC__Portions_Planned", SqlDbType.Int).Value = CType((str4), Integer)
            cmd.Parameters.Add("@PL_Total__Portions_Planned", SqlDbType.Int).Value = str5
            cmd.Parameters.Add("@PL_Factor_Portions", SqlDbType.Char).Value = CType((str6), Char)
            cmd.Parameters.Add("@DOC_Quantity_Of_Food_Used", SqlDbType.Char).Value = CType((str7), Char)
            cmd.Parameters.Add("@DOC_Portions_Prepared ", SqlDbType.Int).Value = CType((str8), Integer)
            cmd.Parameters.Add("@DOC_Portions_Brought_Forward ", SqlDbType.Int).Value = CType((str9), Integer)
            cmd.Parameters.Add("@DOC_Portions_Leftover", SqlDbType.Int).Value = CType((str10), Integer)
            cmd.Parameters.Add("@DOC_Leftover_Code", SqlDbType.Char).Value = CType((str11), Char)
            cmd.Parameters.Add("@DOC_Total_Portions_Served", SqlDbType.Int).Value = CType((str12), Integer)
            cmd.Parameters.Add("@DOC_Portions_Served_Students", SqlDbType.Int).Value = CType((str13), Integer)
            cmd.Parameters.Add("@DOC_Portions_Served_Adults_ALC", SqlDbType.Int).Value = CType((str1), Integer)
            con.Open()
            'cmd.ExecuteNonQuery()
            Dim rows As Integer = (cmd.ExecuteNonQuery)
            con.Close()
            If rows = 1 Then
                MsgBox("Saved!")
            Else
                MsgBox("Failed to save!")
            End If
        Next
    End Sub
 Public Sub btnsave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsave.Click
        AddGrid()
End Sub

ASPX-
<telerik:RadGrid ID="GD_Prod" runat="server" AllowPaging="True" ShowHeader="False"
                    AutoGenerateColumns="False"  GridLines="None"
                    Height="173px" Skin="Outlook" Width="1107px">
                    <ClientSettings AllowColumnsReorder="True">
                        <Selecting AllowRowSelect="True" />
                        <Scrolling AllowScroll="True" UseStaticHeaders="True" />
<Selecting AllowRowSelect="True"></Selecting>
<Scrolling AllowScroll="True" UseStaticHeaders="True"></Scrolling>
                    </ClientSettings>
                    <MasterTableView >
<RowIndicatorColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</RowIndicatorColumn>
<ExpandCollapseColumn>
<HeaderStyle Width="20px"></HeaderStyle>
</ExpandCollapseColumn>
<CommandItemSettings ExportToPdfText="Export to Pdf"></CommandItemSettings>
    <Columns>
        <telerik:GridBoundColumn DataField="port_recipe_num" HeaderText="Recipe #" SortExpression="port_recipe_num"
            UniqueName="port_recipe_num" 
            DataType="System.Char" 
            >
            <HeaderStyle Width="17px" />
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="DESC_ALT" HeaderText="Description" SortExpression="DESC_ALT"
            UniqueName="DESC_ALT"  
            DataType="System.Char" >
            <HeaderStyle Width="36px" />
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="port_no_servings" HeaderText="Portion Size" SortExpression="port_no_servings"
            UniqueName="port_no_servings" 
            DataType="System.Char" >
            <HeaderStyle Width="16px" />
        </telerik:GridBoundColumn>
        <telerik:GridTemplateColumn UniqueName="PL_STUDENT_Portions_Planned" 
             DataType="System.Int32" 
            DataField="PL_STUDENT_Portions_Planned" >
            <ItemTemplate>
                <asp:TextBox ID="TxtSTUPort" DataField="PL_STUDENT_Portions_Planned" runat="server"
                    Height="16px" Width="30px"  
                    ></asp:TextBox>
            </ItemTemplate>
            <HeaderStyle Width="19px" />
        </telerik:GridTemplateColumn>
        <telerik:GridTemplateColumn UniqueName="PL_Adults_ALC_Portions_Planned" 
            DataType="System.Int32" 
            DataField="PL_Adults_ALC_Portions_Planned" 
            >
            <ItemTemplate>
                <asp:TextBox ID="TxtAAPort" DataField="PL_Adults_ALC_Portions_Planned" runat="server"
                    Height="16px" Width="30px" ></asp:TextBox>
            </ItemTemplate>
            <HeaderStyle Width="19px" />
        </telerik:GridTemplateColumn>
        <telerik:GridBoundColumn DataField="STD_NUM_OF_SERVING" HeaderText="Total Potions Planned"
            SortExpression="STD_NUM_OF_SERVING" UniqueName="STD_NUM_OF_SERVING" 
            DataType="System.Int32" 
            >
            <HeaderStyle Width="14px" />
        </telerik:GridBoundColumn>
        <telerik:GridTemplateColumn UniqueName="PL_Factor_Portions" DataType="System.Char" 
            DataField="PL_Factor_Portions"
            >
            <ItemTemplate>
                <asp:TextBox ID="TxtFPort" DataField="PL_Factor_Portions" runat="server" Height="16px"
                    Width="30px"></asp:TextBox>
            </ItemTemplate>
            <HeaderStyle Width="18px" />
        </telerik:GridTemplateColumn>
        <telerik:GridTemplateColumn UniqueName="DOC_Quantity_Of_Food_Used" 
            DataType="System.Char" 
            DataField="DOC_Quantity_Of_Food_Used" >
            <ItemTemplate>
                <asp:TextBox ID="TxtQoFUsed" DataField="DOC_Quantity_Of_Food_Used" runat="server"
                    Height="16px" Width="30px" ></asp:TextBox>
            </ItemTemplate>
            <HeaderStyle Width="20px" />
        </telerik:GridTemplateColumn>
        <telerik:GridTemplateColumn UniqueName="DOC_Portions_Prepared" 
            DataType="System.Int32" 
            DataField="DOC_Portions_Prepared" 
            >
            <ItemTemplate>
                <asp:TextBox ID="TxtPPort" DataField="DOC_Portions_Prepared" runat="server" Height="16px"
                    Width="30px" ></asp:TextBox>
            </ItemTemplate>
            <HeaderStyle Width="16px" />
        </telerik:GridTemplateColumn>
        <telerik:GridTemplateColumn UniqueName="DOC_Portions_Brought_Forward" 
            DataType="System.Int32" 
            DataField="DOC_Portions_Brought_Forward" >
            <ItemTemplate>
                <asp:TextBox ID="TxtBFPort" DataField="DOC_Portions_Brought_Forward" runat="server"
                    Height="16px" Width="30px" ></asp:TextBox>
            </ItemTemplate>
            <HeaderStyle Width="16px" />
        </telerik:GridTemplateColumn>
        <telerik:GridTemplateColumn UniqueName="DOC_Portions_LeftOver" 
            DataType="System.Int32" 
            DataField="DOC_Portions_LeftOver" >
            <ItemTemplate>
                <asp:TextBox ID="TxtLOPort" DataField="DOC_Portions_LeftOver" runat="server" Height="16px"
                    Width="30px" ></asp:TextBox>
            </ItemTemplate>
            <HeaderStyle Width="16px" />
        </telerik:GridTemplateColumn>
        <telerik:GridTemplateColumn UniqueName="DOC_LeftOver_Code"  DataType="System.Char" 
            DataField="DOC_LeftOver_Code"  
            >
            <ItemTemplate>
                <asp:DropDownList ID="ddlLOCode" DataField="DOC_LeftOver_Code" runat="server" Font-Size="10px"
                    Height="16px" Width="46px" AppendDataBoundItems="True" 
                    >
                    <asp:ListItem>CF</asp:ListItem>
                    <asp:ListItem>DIS</asp:ListItem>
                    <asp:ListItem>ALC</asp:ListItem>
                    <asp:ListItem>RTB</asp:ListItem>
                    <asp:ListItem>RTF</asp:ListItem>
                </asp:DropDownList>
            </ItemTemplate>
            <HeaderStyle Width="16px" />
        </telerik:GridTemplateColumn>
        <telerik:GridTemplateColumn UniqueName="DOC_Total_Portions_Served" 
            DataType="System.Int32" 
            DataField="DOC_Total_Portions_Served" >
            <ItemTemplate>
                <asp:TextBox ID="TxtTSPort" DataField="DOC_Total_Portions_Served" runat="server"
                    Height="16px" Width="30px" ></asp:TextBox>
            </ItemTemplate>
            <HeaderStyle Width="16px" />
        </telerik:GridTemplateColumn>
        <telerik:GridBoundColumn UniqueName="POSStudents" DataField="POSStudents" HeaderText="POSStudents" 
            SortExpression="POSStudents" DataType="System.Int32" 
            >
            <HeaderStyle Width="18px" />
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn UniqueName="POSAdultsALC" DataField="POSAdultsALC" HeaderText="POSAdultsALC" 
            SortExpression="POSAdultsALC" DataType="System.Int32" 
           >
            <HeaderStyle Width="16px" />
        </telerik:GridBoundColumn>
    </Columns>
    <PagerStyle AlwaysVisible="True" />
</MasterTableView>
                </telerik:RadGrid>
<asp:Button ID="btnsave" runat="server" Height="20px" Text="SAVE" Width="50px"
                        ToolTip="Click to Save " BackColor="#DFEFFF" ForeColor="#003399" />

Stored_Procedure-
CREATE 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)

Posted 19-Jan-11 16:19pm

Rohit.Net100

Updated 19-Jan-11 16:28pm

Yusuf

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Yusuf · Accepted Answer · 2011-01-19T16:33:00

Solution 1

If you look into your code closely you will notice that they are all assigned 0.

VB

Dim str0 As String = Nothing
            If String.IsNullOrEmpty(str0) Then
                str0 = "0"
            Else
                str0 = (item.Cells(0).Text.Trim)
            End If
            Dim str1 As String = Nothing
            If String.IsNullOrEmpty(str1) Then
                str1 = "0"
            Else

Did you mean to do it this way

VB

Dim str0 As String = (item.Cells(0).Text.Trim)
If String.IsNullOrEmpty(str0) Then
  str0 = "0"
End if

Posted 19-Jan-11 16:33pm

Yusuf

Comments

Venkatesh Mookkan 19-Jan-11 22:59pm

Proposed Answer. Good call.

Rohit.Net100 20-Jan-11 8:14am

I changed my code as per your suggestion.
But now i got an FormatException of Input String was not in correct format.
Error was in line-
Dim str5 As String = Integer.Parse(item.Cells(5).Text.Trim)
If String.IsNullOrEmpty(str5) Then
str5 = "0"
End If
Itried many method to convert string in integer, but nothing worked

Sebastian Br. 20-Jan-11 8:21am

You try to assign an integer to a String variable. Is your above code snippet right?

Yusuf 20-Jan-11 9:08am

Do you know you can reply to comments now? Hover on the comment and you will see the reply icon.