Custom menu and custom page

ASP.NET Maker General Discussion (ASP.NET Maker)
1

Good afternoon,
what’s the best way to add a custom menu entry and associate a custom page where we would like to add a combo for for selecting one option and let the user confirm with a button click that will execute a stored procedure on the attached database?Many thank for your suggestion.Best regards,
John

2

You need to create your own custom file. Please refer help file topic “Custom Files” as a start.

3

I did the custom file with select list and button.How can i invoke the stored procedure then?

I think it is a common problem and there isn’t a clear example in the help.Thanks.

4

You can just use the Razor codes in your custom page to handle the request. For example:@{
var req = Get(“req”);
}
@if (req == “1”) {
//… process form here
} else {
//… request form here
}

5

Sorry,
ow does this fit with having a select list and a button that needs to be used to call the stored procedure:

Please select Year:

2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035

Inizialize
What's the syntax for calling the stored procedure? In the help there is only samplke for doing a select.Regards, John
6

Read:
https://discourse.hkvstore.com/t/calling-ms-sql-store-procedure/736/1

7

Hi,
when i try to implement as suggested i got following error:The name ‘GetStoredProcCommand’ does not exist in the current contextHere it is my implementation:

@{
    var selyear = string.Empty;
    if (Request.HasFormContentType)
    {
        selyear = Request.Form["selyear"];
    }
}
<div style="margin-top:30px;">
    <form method="post">
        <div>
            <select name="selyear" class="selectpicker show-tick">
                <option hidden selected>Please select year...</option>
                <option>2021</option>
                <option>2022</option>
                <option>2023</option>
                <option>2024</option>
                <option>2025</option>
                <option>2026</option>
                <option>2027</option>
                <option>2028</option>
                <option>2029</option>
                <option>2030</option>
                <option>2031</option>
                <option>2032</option>
                <option>2033</option>
                <option>2034</option>
                <option>2035</option>
            </select>
        </div>
        <div><input type="submit" /></div>
    </form>
</div>

    @if (!string.IsNullOrEmpty(selyear))
    {


        // var dbhelper = GetConnection("DB");
        // var cmd = dbhelper.GetStoredProcCommand("sp_Tenant_InitYearCalendar");

        var cmd = GetStoredProcCommand("sp_Tenant_InitYearCalendar");

        cmd.Parameters.Add("Tenant", SqlDbType.Int).Value = 1;
        cmd.Parameters.Add("YearNum", SqlDbType.Int).Value = selyear;
        cmd.Parameters.Add("retval", SqlDbType.Int).Value = 0;
        cmd.Open();
        cmd.ExecuteNonQuery();

    <p>You choose year: @selyear!</p>
    }

What’s wrong?BR,
John

8

Change GetStoredProcCommand to:var cmd = Connection.GetCommand(“…”);
cmd.CommandType = CommandType.StoredProcedure;
//…

9

Hello,
I fixed the db connection using the dbhelper.This is now a working set:

@{ var selyear = string.Empty;
    if (Request.HasFormContentType)
    {
        selyear = Request.Form["selyear"];
    } }
<div style="margin-top:30px;">
    <form method="post">
        <div>
            <select name="selyear" class="selectpicker show-tick">
                <option hidden selected>Please select year...</option>
                <option>2021</option>
                <option>2022</option>
                <option>2023</option>
                <option>2024</option>
                <option>2025</option>
                <option>2026</option>
                <option>2027</option>
                <option>2028</option>
                <option>2029</option>
                <option>2030</option>
                <option>2031</option>
                <option>2032</option>
                <option>2033</option>
                <option>2034</option>
                <option>2035</option>
            </select>
        </div>
        <div><input type="submit" /></div>
    </form>
</div>

@if (!string.IsNullOrEmpty(selyear))
{

    int iYear = 0;

    if (int.TryParse(selyear, out iYear))
    {
        try
        {
            var dbhelper = GetConnection("DB");

            var cmd = dbhelper.GetCommand("sp_Tenant_InitYearCalendar");
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("Tenant", MySqlDbType.Int32).Value = 1;
            cmd.Parameters.Add("YearNum", MySqlDbType.VarChar).Value = selyear;
            cmd.Parameters.Add("retval", MySqlDbType.Int32);
            cmd.Parameters["retval"].Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();

            <p>Sucessfully added year: @selyear!</p> 

        }
        catch (Exception ex)
        {
            <p>Problem adding year! @ex.Message</p>
        }
     }

 }

I hope this could help someone else.Many thanks for helping.Best regards,
John

10

very usefull. this is what i’m looking for