将数据从网格插入SQL数据库 [英] Inserting data from grid into SQL Database
本文介绍了将数据从网格插入SQL数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在BtnClick上，我将RADGRID中的所有数据插入到SQL数据库中.但这只是为所有记录插入零".
请帮助我代码中缺少的内容.我的代码如下.
VB.NET-
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)
推荐答案
如果仔细查看代码，您会发现它们都分配了0.
If you look into your code closely you will notice that they are all assigned 0.
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
Dim str0 As String = (item.Cells(0).Text.Trim)
If String.IsNullOrEmpty(str0) Then
str0 = "0"
End if
这篇关于将数据从网格插入SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
