Getting ID of Inserted Appointment

[Steele]

Hi All,
I have the scheduler setup to use an 

Telerik.Web.UI.XmlSchedulerProvider

 

to look after its bookings/appointments.  This works well in that the provider handles all the inserts/updates/deletes for me.
I do, however, have a need to send a notification email to the Admin when new appointments get inserted or updated.  In this email I want to provide a link to the scheduler, passing the appointment ID in the QueryString so that the appointment in question can be highlighted.  This all works great for updates, as the ID is available in the 

 

RadScheduler1_AppointmentUpdate

Method in the e.Appointment.ID property.
The same cannot be said for 

 

RadScheduler1_AppointmentInsert

 

as the ID is null - I suppose the insert of the xmlProvider has not happened yet.

So my question is, at what point/event can I obtain the ID of the newly created Appointment?

Thanks for any help.
Steele.

[Veselin Vasilev]

Hi Steele,

You can inherit the XmlSchedulerProvider and override the Insert method.

Here is a sample code:

    protected void Page_Load(object sender, EventArgs e)
    {
        MyXmlSchedulerProvider provider = new MyXmlSchedulerProvider(Server.MapPath("~/App_Data/Appointments.xml"), true);
        RadScheduler1.Provider = provider;
    }

    public class MyXmlSchedulerProvider : XmlSchedulerProvider
    {
        public override void Insert(RadScheduler owner, Appointment appointmentToInsert)
        {
            base.Insert(owner, appointmentToInsert);
            string currentID = appointmentToInsert.ID.ToString();
            //send the email here
        }

        public MyXmlSchedulerProvider(string dataFileName, bool persistChanges) : base(dataFileName, persistChanges)
        {

        }
    }

I hope this will get you started.

Greetings,
Veselin Vasilev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Steele]

Thank you.
Seems simple enough.
In the meantime I have started a MySQL attempt as well (as I am not sure of the scalability XML will provide me), so I will let you know how it goes.
Again, much appreciated!

[Steele]

Talking of MySQL, I though I would be able to get the value of the inserted id from an output parameter declared in the datasources Inserted() method.  But it did not seem to be the case (output parameter is null).

My datasource looks like :

    <asp:SqlDataSource ID="sdsApt" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>"
        DeleteCommand="DELETE FROM appointment WHERE (id = @id)"
        InsertCommand="INSERT INTO appointment(subject, start, end, trainerid) VALUES (@subject, @start, @end, @trainerid); SELECT @poId := last_insert_id();"
        oninserted="SqlDataSource1_Inserted"
        ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>"
        SelectCommand="select * from appointment"


        UpdateCommand="UPDATE appointment SET subject = @subject, start = @start, end = @end WHERE (id = @id)">
        <InsertParameters>
            <asp:Parameter Name="subject" Type="String" />
            <asp:Parameter Name="start" Type="DateTime" />
            <asp:Parameter Name="end" Type="DateTime" />
            <asp:Parameter Name="trainerid" Type="Int32" />
            <asp:Parameter Direction="Output" Name="poId" Type="Int32" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="subject" Type="String" />
            <asp:Parameter Name="start" Type="DateTime" />
            <asp:Parameter Name="end" Type="DateTime" />
            <asp:Parameter Name="id" Type="Int32" />
        </UpdateParameters>
        <DeleteParameters>
            <asp:Parameter Name="id" Type="Int32" />
        </DeleteParameters>
    </asp:SqlDataSource>

As you can see I have poId as an output parameter which is assigned the last_insert_id() in the insert query.
But when I try to fetch the value :

        protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
        {

            lblMessage.Text = e.Command.Parameters["poId"].Value.ToString();
        }

It is still null.  Am I doing something wrong here?  Or do I need to still extend the provider in a similar way as to what you showed me for the XML solution?
Thanks,
Steele.

[Steele]

Ok, I got a solution, but not the one I thought!
Basically, I don't use an output parameter and do the following in the code behind :

        protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
        {
           if (e.Command is MySql.Data.MySqlClient.MySqlCommand)
            {
                lblMessage.Text = ((MySql.Data.MySqlClient.MySqlCommand)(e.Command)).LastInsertedId.ToString();
            }
        }

And removed the

SELECT @poId := last_insert_id()

From the InsertCommand of the SQLDataSource.
What I was missing was the dll reference in the project to MySql.Data.dll so I could do the casting.
I still don't know how to use an output parameter though, but at least it works.
Steele.

[Veselin Vasilev]

Hello Steele,

I believe that this article will shed more light to you.

Best wishes,
Veselin Vasilev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

[Zheng Gong]

Hi Steele, I tried the one you used but I couldn't get it work. Could you please help me on that? I used MySQL5.0 and asp.net 3.5. Thanks!