[CLOSED] Form Panel, Store and SQLDatasource with insert, update and delete methods

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    [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).

    <%@ 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]
  2. #2
    Issued solved!

    The SqlDatasourse Select Command should include the IDProperty Column 'courseid' Sigh. Would have been nice if the store threw an exceptions.
    Last edited by garyawalker; Jul 19, 2015 at 9:16 PM.
  3. #3
    Hello @garyawalker,

    Thank you for sharing a solution!

    Would have been nice if the store threw an exceptions.
    Sorry, I didn't get the scenario clearly. Do you mean we could check/analyze an SqlDataSource SelectCommand SQL code?
  4. #4
    Daniil

    Regarding my statement.
    Would have been nice if the store threw an exceptions.
    I meant when I call the save() method on the store as follows
     <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;}" />
    The store didn't report that it couldn't save because a column was missing from the underlying DataSource.

    But. It's ok. Just have to ensure it's wired up correctly.

    If I hadn't say it before. EXT.NET is an excellent solution. Brilliant work Guys Brilliant. Thanks a million.
  5. #5
    The store didn't report that it couldn't save because a column was missing from the underlying DataSource.
    But what does happen instead? Ideally, to come up with something here I need a runnable test case.

    If I hadn't say it before. EXT.NET is an excellent solution. Brilliant work Guys Brilliant. Thanks a million.
    Thank you very much for the feedback! Such a feedback really helps to keep working hard :)
    Last edited by Daniil; Jul 21, 2015 at 6:54 AM.

Similar Threads

  1. [CLOSED] Store problem with jsonp proxy when insert, update,delete
    By mtsig in forum 2.x Legacy Premium Help
    Replies: 12
    Last Post: Jul 25, 2013, 7:02 AM
  2. Replies: 1
    Last Post: Apr 13, 2013, 1:31 AM
  3. [CLOSED] Insert, Update, Delete in CalendarPanel using ObjectDataSource
    By asiaesolutions in forum 2.x Legacy Premium Help
    Replies: 3
    Last Post: Feb 21, 2013, 11:53 AM
  4. Replies: 0
    Last Post: Sep 02, 2010, 3:58 AM
  5. Replies: 0
    Last Post: Jun 10, 2010, 1:18 AM

Posting Permissions