Issue with session parameter "Must declare the scalar variable"

Question

I have an issue with this code.

    <asp:SqlDataSource ID="ReservationRadSchedualerSqlDataSource" OnDeleting="ReservationRadSchedualerSqlDataSource_Deleting"  runat="server" ConnectionString="<%$ ConnectionStrings:NMRconstr %>"
        SelectCommand="GetReservationAppointments" SelectCommandType="StoredProcedure"
        InsertCommand="INSERT INTO [ReservationAppointments] (Subject , Description, Start, [End], RecurrenceRule , Username , MachinePKID) VALUES (@Subject , @Description, @Start, @End, @RecurrenceRule , @username , @macPKID)"
        UpdateCommand="EXEC UpdateReservationAppointment @Subject , @Description , @Start , @End , @RecurrenceRule , @username , @ID , @macPKID"                      
        DeleteCommand="EXEC DeleteAppointmentByUser @ID , @username"                       
        >
        <UpdateParameters>
            <asp:ControlParameter ControlID="ReservationScedhualerSelectMachineRadDropDownList" Name="macPKID" PropertyName="SelectedValue" Type="Int32" />
        </UpdateParameters>
        <SelectParameters>
            <asp:ControlParameter ControlID="ReservationScedhualerSelectMachineRadDropDownList" Name="macPKID" PropertyName="SelectedValue" Type="Int32" />
        </SelectParameters>
        <InsertParameters>
            <asp:SessionParameter Name="username" SessionField="username" Type="string" />
            <asp:ControlParameter ControlID="ReservationScedhualerSelectMachineRadDropDownList" Name="macPKID" PropertyName="SelectedValue" Type="Int32" />
        </InsertParameters>
        <DeleteParameters>
            <asp:SessionParameter Name="username" SessionField="username" Type="string" />
        </DeleteParameters>
    </asp:SqlDataSource>

In the delete command parameters, the session username parameter causes an error "Must declare the scalar variable "@username"."

I tried to figure out what I did wrong but I cant find it. The weird thing is that it works for INSERT and UPDATE commands, but not for the DELETE command.

I will be happy to get your help to figure out what is causing this error.

Thanks.

Answer 1

Everything seems in order with the .Net side. Can you post the SQL for the DeleteAppointmentByUser function? Suspect the issue is in there.

Answer 2

i found the answer.

seems asp.net sql SqlDataSource not allow the same name of parameter into the same section.

the delete and insert have both the same session parameter with the name Name="username" . i just need to change the name of one of them and it work.

i just ask why asp.net not alert with this issue before compilation.

thanks guys for the help.

Answer 3

Perhaps you can try a workaround :

<DeleteParameters>
   <asp:Parameter Name="username" Type="String" />
</DeleteParameters>

Add this attribute to your SQL Data Source :

OnDeleting="On_Deleting"

Then in the back end code :

protected void On_Deleting(Object sender, SqlDataSourceCommandEventArgs e) 
{
    e.Command.Parameters["@username"].Value = // value from session
}