Jun 10, 2010, 1:18 AM
How To Insert, delete, update with Store and sqlDataSource
Dear All,
How to insert, delete, update with Store and SqlDataSource for table with primary keys contain many fields?
Here the page :
I get the values for flddate, fldshift, fldmachineno, fldphase and fldopno from outside the gridpanel, but i have to get the value for fldPHPin from the current selectedrow of gridpanel.
How to do it?
Currently if i want to save the data i always get message error "<style type="text/css">body{border:0;margin:0;padding:3px;height:98% ;cursor:text;}</style>System.Exception: Cannot insert the value NULL into column 'fldPHPIn', table
'Offset.dbo.tblShiftIn'; column does not allow nulls. INSERT fails. ..."
Regards,
How to insert, delete, update with Store and SqlDataSource for table with primary keys contain many fields?
Here the page :
<ext:TabPanel ID="TabPanel1" runat="server" ActiveTabIndex="8" Height="750px"
Collapsible="True" IDMode="Legacy" Title="TabPanel1">
<Tabs>
<ext:Tab ID="Tab_testGP" runat="server" IDMode="Legacy" Title="Tab">
<TopBar>
<ext:Toolbar runat="server">
<Items>
<ext:Button ID="Button3" runat="server" Text="Insert" Icon="Add">
<Listeners>
<Click Handler="#{GridPanel1}.insertRecord(0, {});#{GridPanel1}.getView().focusRow(0);#{GridPanel1}.startEditing(0, 0);" />
</Listeners>
</ext:Button>
<ext:Button ID="Button1" runat="server" Text="Save" Icon="Disk">
<Listeners>
<Click Handler="#{GridPanel1}.save();" />
</Listeners>
</ext:Button>
<ext:Button ID="Button2" runat="server" Text="Delete selected records" Icon="Delete">
<Listeners>
<Click Handler="#{GridPanel1}.deleteSelected();" />
</Listeners>
<AjaxEvents>
<Click OnEvent="DeleteCurrentRowGP" >
<ExtraParams>
<ext:Parameter Name="fldPHPInValue" Value="Ext.encode(#{GridPanel1}.getRowsValues({selectedOnly:true}))" Mode="Raw" />
</ExtraParams>
</Click>
</AjaxEvents>
</ext:Button>
<ext:Button ID="Button4" runat="server" Text="Refresh" Icon="ArrowRefresh">
<Listeners>
<Click Handler="#{GridPanel1}.reload();" />
</Listeners>
</ext:Button>
</Items>
</ext:Toolbar>
</TopBar>
<body>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:OffsetConnectionString1 %>"
DeleteCommand="DELETE FROM [tblShiftIn] WHERE [fldDate] = @fldDate AND [fldShift] = @fldShift AND [fldMachineNo] = @fldMachineNo AND [fldPhase] = @fldPhase AND [fldOPNo] = @fldOPNo AND [fldPHPIn] = @fldPHPIn"
InsertCommand="INSERT INTO [tblShiftIn] ([fldDate], [fldShift], [fldMachineNo], [fldPhase], [fldOPNo], [fldPHPIn], [fldInKg], [fldInQty], [fldInProcLine], [fldInCFactor]) VALUES (@fldDate, @fldShift, @fldMachineNo, @fldPhase, @fldOPNo, @fldPHPIn, @fldInKg, @fldInQty, @fldInProcLine, @fldInCFactor)"
SelectCommand="SELECT [fldDate], [fldShift], [fldMachineNo], [fldPhase], [fldOPNo], [fldPHPIn], [fldInKg], [fldInQty], [fldInProcLine], [fldInCFactor] FROM [tblShiftIn] WHERE (([fldDate] = @fldDate) AND ([fldShift] = @fldShift) AND ([fldMachineNo] = @fldMachineNo) AND ([fldPhase] = @fldPhase) AND ([fldOPNo] = @fldOPNo))"
UpdateCommand="UPDATE [tblShiftIn] SET [fldInKg] = @fldInKg, [fldInQty] = @fldInQty, [fldInProcLine] = @fldInProcLine, [fldInCFactor] = @fldInCFactor WHERE [fldDate] = @fldDate AND [fldShift] = @fldShift AND [fldMachineNo] = @fldMachineNo AND [fldPhase] = @fldPhase AND [fldOPNo] = @fldOPNo AND [fldPHPIn] = @fldPHPIn">
<SelectParameters>
<asp:ControlParameter ControlID="txtDate" DbType="DateTime" Name="fldDate"
PropertyName="Text" />
<asp:ControlParameter ControlID="ddlShift" Name="fldShift"
PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="lblMachineNo" Name="fldMachineNo"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtPhase" Name="fldPhase" PropertyName="Text"
Type="Int16" />
<asp:ControlParameter ControlID="txtOPNo" Name="fldOPNo" PropertyName="Text"
Type="String" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter DbType="DateTime" Name="fldDate" />
<asp:Parameter Name="fldShift" Type="String" />
<asp:Parameter Name="fldMachineNo" Type="String" />
<asp:Parameter Name="fldPhase" Type="Int16" />
<asp:Parameter Name="fldOPNo" Type="String" />
<asp:Parameter Name="fldPHPIn" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="fldInKg" Type="Double" />
<asp:Parameter Name="fldInQty" Type="Double" />
<asp:Parameter Name="fldInProcLine" Type="String" />
<asp:Parameter Name="fldInCFactor" Type="Single" />
<asp:Parameter DbType="DateTime" Name="fldDate" />
<asp:Parameter Name="fldShift" Type="String" />
<asp:Parameter Name="fldMachineNo" Type="String" />
<asp:Parameter Name="fldPhase" Type="Int16" />
<asp:Parameter Name="fldOPNo" Type="String" />
<asp:Parameter Name="fldPHPIn" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter DbType="DateTime" Name="fldDate" />
<asp:Parameter Name="fldShift" Type="String" />
<asp:Parameter Name="fldMachineNo" Type="String" />
<asp:Parameter Name="fldPhase" Type="Int16" />
<asp:Parameter Name="fldOPNo" Type="String" />
<asp:Parameter Name="fldPHPIn" Type="String" />
<asp:Parameter Name="fldInKg" Type="Double" />
<asp:Parameter Name="fldInQty" Type="Double" />
<asp:Parameter Name="fldInProcLine" Type="String" />
<asp:Parameter Name="fldInCFactor" Type="Single" />
</InsertParameters>
</asp:SqlDataSource>
<ext:Store ID="Store1" runat="server"
DataSourceID="sqlDataSource1"
OnBeforeRecordInserted="Store1_BeforeRecordInserted">
<Reader>
<ext:JsonReader ReaderID="fldPHPIn">
<Fields>
<ext:RecordField Name="fldDate" />
<ext:RecordField Name="fldShift" />
<ext:RecordField Name="fldMachineNo" />
<ext:RecordField Name="fldPhase" />
<ext:RecordField Name="fldOpNo" />
<ext:RecordField Name="fldPHPIn" />
<ext:RecordField Name="fldInKg" />
<ext:RecordField Name="fldInQty" />
</Fields>
</ext:JsonReader>
</Reader>
</ext:Store>
<ext:GridPanel ID="GridPanel1" runat="server"
StoreID="Store1"
Height="300">
<ColumnModel runat="server">
<Columns>
<ext:Column ColumnID="fldPHPin" DataIndex="fldPHPIn" Header="PHP #" >
<Editor>
<ext:TextField runat="server" ID="txtPHPin_gp" />
</Editor>
</ext:Column>
</Columns>
<Columns>
<ext:Column ColumnID="fldInQty" DataIndex="fldInQty" Header ="IN (Qty)">
<Editor>
<ext:TextField runat="server" ID="TextField1" />
</Editor>
</ext:Column>
</Columns>
<Columns>
<ext:Column ColumnID="fldInKg" DataIndex="fldInKg" Header ="IN (kg)">
<Editor>
<ext:TextField runat="server" ID="txtShift_gp" />
</Editor>
</ext:Column>
</Columns>
</ColumnModel>
<SelectionModel>
<ext:RowSelectionModel ID="RowSelectionModel1" runat="server">
</ext:RowSelectionModel>
</SelectionModel>
</ext:GridPanel>
<br />
</body>
</ext:Tab>
</Tabs>
</ext:TabPanel>
And the code behind:Protected Sub SqlDataSource1_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Deleted
Store1.DataBind()
End Sub
Protected Sub SqlDataSource1_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Deleting
e.Command.Parameters("@fldDate").Value = Me.txtDate.Text
e.Command.Parameters("@fldShift").Value = Me.ddlShift.SelectedValue.ToString
e.Command.Parameters("@fldMachineNo").Value = Me.lblMachineNo.Text
e.Command.Parameters("@fldPhase").Value = Me.txtPhase.Text
e.Command.Parameters("@fldOPNo").Value = Me.txtOPNo.Text
End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
'insertedDateValue = If(e.Command.Parameters("@fldDate").Value IsNot Nothing, e.Command.Parameters("@flddate").Value.ToString(), "")
Store1.DataBind()
End Sub
Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting
e.Command.Parameters("@fldDate").Value = Me.txtDate.Text
e.Command.Parameters("@fldShift").Value = Me.ddlShift.SelectedValue.ToString
e.Command.Parameters("@fldMachineNo").Value = Me.lblMachineNo.Text
e.Command.Parameters("@fldPhase").Value = Me.txtPhase.Text
e.Command.Parameters("@fldOPNo").Value = Me.txtOPNo.Text
e.Command.Parameters("@fldInProcLine").Value = "BOARD"
End Sub
Protected Sub SqlDataSource1_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Updated
Store1.DataBind()
End Sub
Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating
e.Command.Parameters("@fldDate").Value = Me.txtDate.Text
e.Command.Parameters("@fldShift").Value = Me.ddlShift.SelectedValue.ToString
e.Command.Parameters("@fldMachineNo").Value = Me.lblMachineNo.Text
e.Command.Parameters("@fldPhase").Value = Me.txtPhase.Text
e.Command.Parameters("@fldOPNo").Value = Me.txtOPNo.Text
End Sub
the table i try to insert, update and deleting data is tblShiftIn which have (fldDate, fldShift, fldMachineno, fldPhase, fldOPno and fldPHPIn) as Primary Key.I get the values for flddate, fldshift, fldmachineno, fldphase and fldopno from outside the gridpanel, but i have to get the value for fldPHPin from the current selectedrow of gridpanel.
How to do it?
Currently if i want to save the data i always get message error "<style type="text/css">body{border:0;margin:0;padding:3px;height:98% ;cursor:text;}</style>System.Exception: Cannot insert the value NULL into column 'fldPHPIn', table
'Offset.dbo.tblShiftIn'; column does not allow nulls. INSERT fails. ..."
Regards,