Jul 19, 2015, 7:03 PM
[CLOSED] Form Panel, Store and SQLDatasource with insert, update and delete methods
Hi Guys
For some reason I am not able to figure out why when I delete or make a change to a record it doesn't have any effect. I am however able to insert a new record. Can you look at my codes to help find issue(s).
For some reason I am not able to figure out why when I delete or make a change to a record it doesn't have any effect. I am however able to insert a new record. Can you look at my codes to help find issue(s).
<%@ Control Language="VB" AutoEventWireup="false" CodeFile="editCourses.ascx.vb" Inherits="registrar_editCourses" %>
<%@ Register assembly="Ext.Net" namespace="Ext.Net" tagprefix="ext" %>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
DeleteCommand="DELETE FROM tblCOURSECLASSES WHERE (courseid = @courseid)"
InsertCommand="INSERT INTO tblCOURSECLASSES
(year,
term,
subjectid,
teacherid,
classid,
seats,
hours,
billcodeid)
VALUES
(@year,
@term,
@subjectid,
@teacherid,
@classid,
@seats,
@hours,
@billcodeid);
SELECT @newId = @@Identity;"
SelectCommand="SELECT year,
term,
subjectid,
teacherid,
classid,
seats,
hours,
billcodeid
FROM tblCOURSECLASSES
WHERE courseid = @courseid"
UpdateCommand="UPDATE tblCOURSECLASSES SET
year = @year,
term = @term,
subjectid = @subjectid,
teacherid = @teacherid,
classid = @classid,
seats = @seats,
hours = @hours,
billcodeid = @billcodeid
WHERE courseid = @courseid"
OnInserted="SqlDataSource2_Inserted">
<SelectParameters>
<asp:Parameter Name="courseid" Type="Int32" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="courseid" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="year" Type="String" />
<asp:Parameter Name="term" Type="String" />
<asp:Parameter Name="classid" Type="String" />
<asp:Parameter Name="teacherid" Type="Int32" />
<asp:Parameter Name="subjectid" Type="Int32" />
<asp:Parameter Name="seats" Type="Int32" />
<asp:Parameter Name="hours" Type="Int32" />
<asp:Parameter Name="billcodeid" Type="Int32" />
<asp:Parameter Name="courseid" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="year" Type="String" />
<asp:Parameter Name="term" Type="String" />
<asp:Parameter Name="classid" Type="String" />
<asp:Parameter Name="teacherid" Type="Int32" />
<asp:Parameter Name="subjectid" Type="Int32" />
<asp:Parameter Name="seats" Type="Int32" />
<asp:Parameter Name="hours" Type="Int32" />
<asp:Parameter Name="billcodeid" Type="Int32" />
<asp:Parameter Name="courseid" Type="Int32" />
<asp:Parameter Direction="Output" Name="newId" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
<ext:Store
ID="Store2"
runat="server"
DataSourceID="SqlDataSource2"
OnAfterRecordInserted="Store2_AfterRecordInserted">
<Reader>
<ext:JsonReader IDProperty="courseid">
<Fields>
<ext:RecordField Name="courseid" />
<ext:RecordField Name="year" />
<ext:RecordField Name="term" />
<ext:RecordField Name="classid" />
<ext:RecordField Name="subjectid" />
<ext:RecordField Name="teacherid" />
<ext:RecordField Name="seats" />
<ext:RecordField Name="hours" />
<ext:RecordField Name="billcodeid" />
</Fields>
</ext:JsonReader>
</Reader>
<SortInfo Field="courseid" Direction="ASC" />
<Listeners>
<DataChanged
Handler="var record = this.getAt(0) || {};#{FormPanel1}.getForm().loadRecord(record);"
Delay="10"
/>
</Listeners>
</ext:Store>
<ext:Checkbox runat="server" Hidden="true" ID="chkAdd" />
<ext:Window
ID="CourseDetailWindow"
runat="server"
Title="ADD/EDIT COURSE"
Width="400" Height="300"
Modal="true"
Hidden="true"
Layout="FitLayout">
<Items>
<ext:FormPanel ID="FormPanel1"
runat="server"
Border="false"
Layout="FitLayout" Padding="5">
<Items>
<ext:Panel ID="Panel1" runat="server" Layout="FormLayout" Title="DETAILS" Border="false" DefaultAnchor="100%">
<Items>
<ext:FieldSet ID="FieldSet1"
runat="server"
Title=""
Layout="AnchorLayout" Collapsible="false"
DefaultAnchor="100%">
<Items>
<ext:Container ID="Container4" runat="server"
Layout="HBoxLayout">
<Items>
<ext:ComboBox
ID="txtYear"
runat="server"
DataIndex="year"
AllowBlank="false"
FieldLabel="Period"
EmptyText="[YEAR]"
Margins="0 5 5 0"
Flex="2">
</ext:ComboBox>
<ext:ComboBox
ID="txtTerm"
runat="server"
DataIndex="term"
AllowBlank="false"
EmptyText="[TERM]"
Margins="0 0 5 0"
Flex="1">
</ext:ComboBox>
</Items>
</ext:Container>
<ext:ComboBox
ID="txtSubjectid"
runat="server"
DataIndex="subjectid"
AllowBlank="false"
FieldLabel="Subject" Margins="0 5 5 0">
</ext:ComboBox>
<ext:ComboBox
ID="txtTeacherid"
runat="server"
DataIndex="teacherid"
AllowBlank="false"
FieldLabel="Teacher" Margins="0 5 5 0">
</ext:ComboBox>
<ext:TextField
ID="txtClassid"
runat="server"
FieldLabel="Class ID"
DataIndex="classid"
AllowBlank="true" Margins="0 0 5 0"
EmptyText="[Name for group]"
Flex="1"
/>
<ext:TextField
ID="txtseats"
runat="server"
FieldLabel="Seats"
DataIndex="seats"
AllowBlank="true" Margins="0 0 5 0"
EmptyText="Seats"
Flex="1"
/>
<ext:TextField
ID="txtHours"
runat="server"
FieldLabel="Hours"
DataIndex="hours"
AllowBlank="true" Margins="0 0 5 0"
EmptyText="Hours"
Flex="1"
/>
<ext:ComboBox
ID="txtbillcodeid"
runat="server"
DataIndex="billcodeid"
AllowBlank="false"
FieldLabel="Fee" Margins="0 5 5 0">
</ext:ComboBox>
</Items>
</ext:FieldSet>
</Items>
</ext:Panel>
</Items>
<TopBar>
<ext:Toolbar ID="Toolbar1" runat="server">
<Items>
<ext:Button ID="btnAdd" Text="Clear" Icon="Add" runat="server">
<Listeners>
<Click Handler="#{FormPanel1}.getForm().reset(); #{chkAdd}.checked = true;" />
</Listeners>
</ext:Button>
<ext:Button ID="btnSave" Text="Save/Update" Icon="Disk" runat="server">
<Listeners>
<Click Handler="if (#{chkAdd}.checked == false) {#{FormPanel1}.getForm().updateRecord(#{Store2}.getAt(0)); #{Store2}.save();} else {#{Store2}.addRecord(#{FormPanel1}.getForm().getFieldValues(false, 'dataIndex'),false, true); #{Store2}.save(); #{chkAdd}.checked = false;}" />
</Listeners>
<DirectEvents>
<Click OnEvent="Save" >
<EventMask Msg="Saving..." ShowMask="true" />
<ExtraParams>
<ext:Parameter Name="data" Value="#{Store2}.getChangedData()" Mode="Raw" />
</ExtraParams>
</Click>
</DirectEvents>
</ext:Button>
<ext:Button ID="btnDelete" Text="Delete" Icon="Delete" runat="server">
<Listeners>
<Click Handler="Ext.Msg.confirm('Delete Record', 'Are you sure?', function(btn) {if (btn == 'yes') {#{Store2}.remove(#{Store2}.getAt(0)); #{Store2}.save();#{FormPanel1}.getForm().reset();}});" />
</Listeners>
</ext:Button>
</Items>
</ext:Toolbar>
</TopBar>
</ext:FormPanel>
</Items>
</ext:Window>
And code behind VS2010
Imports Ext.Net
Imports System.Data.SqlClient
Partial Class registrar_editCourses
Inherits System.Web.UI.UserControl
Private insertedValue As String
Dim sn As String
Public Sub Show()
Me.CourseDetailWindow.Show()
Me.FormPanel1.Reset()
End Sub
Public Sub getCourseDetails(courseid As Long)
'Dim sn As String = "[JFLL]"
SqlDataSource2.ConnectionString = ConnectionManager.GetConnection(sn)
SqlDataSource2.SelectParameters("courseid").DefaultValue = courseid
Store2.DataBind()
If Not courseid = -1 Then
Me.chkAdd.Checked = False
Else
Me.chkAdd.Checked = True
End If
End Sub
Protected Sub Store2_RefreshData(sender As Object, e As StoreRefreshDataEventArgs)
Me.Store2.DataBind()
End Sub
Protected Sub Save(sender As Object, e As DirectEventArgs)
End Sub
Protected Sub Store2_AfterRecordInserted(sender As Object, e As AfterRecordInsertedEventArgs)
'The deleted and updated records confirms automatic (depending AffectedRows field)
'But you can override this in AfterRecordUpdated and AfterRecordDeleted event
'For insert we should set new id for refresh on client
'If we don't set new id then old id will be used
If Not String.IsNullOrEmpty(insertedValue) Then
e.Keys.Add("courseid", insertedValue)
'e.Confirmation.ConfirmRecord(insertedValue)
insertedValue = ""
End If
End Sub
Protected Sub SqlDataSource2_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs)
'use e.AffectedRows for ensure success action. The store read this value and set predefined Confirm depend on e.AffectedRows
'The Confirm can be granted or denied in OnRecord....ed event
insertedValue = If(e.Command.Parameters("@newId").Value IsNot Nothing, e.Command.Parameters("@newId").Value.ToString(), "")
End Sub
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
sn = Session("sn")
'sn = "[JFLL]"
SqlDataSource2.ConnectionString = ConnectionManager.GetConnection(sn)
If Not IsPostBack Then
Dim sql As String
Dim dr As SqlDataReader
Try
'get default year & term
sql = "select cyear, cterm from tblControl"
dr = GenericDAL.Getdr(sn, sql)
dr.Read()
Me.txtTerm.Text = dr("cterm").ToString
Me.txtYear.Text = dr("cyear").ToString
'years
sql = "select year from years order by year"
dr = GenericDAL.Getdr(sn, sql)
While dr.Read
Dim li As New Ext.Net.ListItem
li.Text = dr("YEAR")
li.Value = dr("YEAR")
Me.txtYear.Items.Add(li)
End While
dr.Close()
'yearid
sql = String.Format("select yearid from years where year = '{0}'", Me.txtYear.Text)
dr = GenericDAL.Getdr(sn, sql)
dr.Read()
Dim yearid As Integer = dr("yearid")
dr.Close()
'term
sql = String.Format("select description from terms where yearid={0} order by termorder", yearid)
dr = GenericDAL.Getdr(sn, sql)
While dr.Read
Dim li As New Ext.Net.ListItem
li.Text = dr("description")
li.Value = dr("description")
Me.txtTerm.Items.Add(li)
End While
dr.Close()
'subjects
sql = String.Format("select subjectid, description from subjects order by description")
dr = GenericDAL.Getdr(sn, sql)
While dr.Read
Dim li As New Ext.Net.ListItem
li.Text = dr("description")
li.Value = dr("subjectid")
Me.txtSubjectid.Items.Add(li)
End While
dr.Close()
'teachers
sql = String.Format("select teacherid, lastname + ', ' + firstname as teacher_name from teachers where status='active' order by lastname, firstname")
dr = GenericDAL.Getdr(sn, sql)
While dr.Read
Dim li As New Ext.Net.ListItem
li.Text = dr("teacher_name")
li.Value = dr("teacherid")
Me.txtTeacherid.Items.Add(li)
End While
dr.Close()
Catch ex As SqlException
Ext.Net.X.Msg.Alert("Connection Failure", ex.Message.ToString).Show()
End Try
End If
End Sub
End Class
Last edited by Daniil; Jul 20, 2015 at 4:28 PM.
Reason: [CLOSED]