How do I post SQL data using submit button in asp.net c# razor

Question

Thanks in advance.

Can anyone please help me get this button working so I can display a row from the Northwind database 'only' when the button is clicked.

I have been doing webgrid tutorials; however, in each tutorial the grid/ webgrid is always showing before it is searched.

It would be great if I could learn how to use a button to show data from a row in the Northwind database in a list/label/table or grid 'only' when a form is submitted.

Keeping it simple, I have this:

@{
    Layout = "~/_Layout.cshtml";
    Page.Title = "Compare";

   var db = Database.Open("Northwind");
   var selectedData = db.Query("SELECT * FROM Products");

<h1>Products</h1>

 <form action="" method="post">
    <div>
      <input type="Submit" value="Get Products" /><br/> <br/> 
    </div>
 </form>

    <ol>
       @foreach(var row in selectedData)
       {
        <li><a href="#">@row.ProductName</a></li>
       }
     </ol>
  }

EDIT: In Mike's tutorial (at: http://www.mikesdotnetting.com/Article/211/Adding-A-Footer-To-The-Razor-WebGrid) Mike uses a webgrid in Razor to access the Northwind database and display orders.

I would like to use form data to access the database with a submit button; is this possible please? If so how? Thanks.

Short answer: Don't try to do that in razor. That's what you have controllers for. — p.s.w.g
– p.s.w.g, Commented Mar 30, 2013 at 0:01

met.lord · Accepted Answer · 2013-03-30 06:16:42Z

1

If you're using ASP.NET MVC, the point of using it is to divide the application in three kind of components... The view which is what you have up there, the controller which is the one that handles your requests from and to the models, and finally the models which is where you should place your query in the first place.

There's no point of using MVC if you're not using it all.

answered Mar 30, 2013 at 6:16

met.lord

6182 gold badges10 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DaniB · Accepted Answer · 2013-03-30 15:45:36Z

Thanks to everyone for your comments. I managed to add a simple button to the Razor code from Mikes tutorial (mentioned in my above post) and it works.

Here is Mike's code with the button I added:

@{
    Layout = "~/_Layout.cshtml";
    Page.Title = "WebGrid.Table method";

    var db = Database.Open("Northwind");
    var sql = "SELECT OrderId FROM Orders";
    var orders = db.Query(sql).Select(o => new SelectListItem {
        Value = o.OrderId.ToString(), 
        Text = o.OrderID.ToString(),
        Selected = o.OrderID.ToString() == Request["OrderID"]
    });

    WebGrid grid = null;
    var orderTotal = 0f;

    if(IsPost){
        sql = @"SELECT p.ProductName, o.UnitPrice, o.Quantity, 
                (o.UnitPrice * o.Quantity) - (o.UnitPrice * o.Quantity * o.Discount) As TotalCost 
                FROM OrderDetails o INNER JOIN Products p ON o.ProductID = p.ProductID 
                WHERE o.OrderID = @0";

        var orderDetails = db.Query(sql, Request["OrderID"]);
        orderTotal = orderDetails.Sum(o => (float)o.TotalCost);

        grid = new WebGrid(orderDetails, canPage: false, canSort: false);
    }
}
<h1>@Page.Title</h1>
<form method="post">
    @Html.DropDownList("OrderID", orders)
    <input type="Submit" value="Get Products" /><br/> <br/> 
</form>

@if(grid != null){
    @grid.Table(
            columns: grid.Columns(
                grid.Column("ProductName", "Product", style: "_220"),
                grid.Column("UnitPrice", "Price", style: "_60", format: @<text>@item.UnitPrice.ToString("c")</text>),
                grid.Column("Quantity", style: "_90"),
                grid.Column("TotalCost", "Total Cost", style: "_90", format: @<text>@item.TotalCost.ToString("c")</text>)
            ), 
            footer: @<table class="footer">
                         <tr>
                             <td class="_220">Total</td>
                             <td colspan="2" class="_150">&nbsp;</td>
                             <td class="_90">@orderTotal.ToString("c")</td>
                         </tr>
                    </table>);
}

If you really answered your own question, then mark this as the answer; however, please remember what the others said. Please don't put lots of code in your *.cshtml file. If you put too much code in your *.cshtml file, you are defeating the purpose of MVC and your code might start looking like classic old school ASP but with razor syntax.

Shawn Eary · Accepted Answer · 2013-03-30 07:51:00Z

0

Here's a crude example I hacked together.

When you first load the page at http://yourDomain/Product/Index, you get nothing but a Text Box and Search Button. When you enter in a few letters into the TextBox and click the Search Button, you get a list of the Northwind Products that start with the inputted letters.

Here is the contents of the ~/Controllers/ProductController.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MvcApplication1.Controllers
{
    public class ProductController : Controller
    {
        //
        // GET: /Product/

        public ActionResult Index(String startString)
        {
            List<Models.Product> theProducts;
            using (
                Models.NorthwindEntities NWC = 
                new Models.NorthwindEntities()
            )
            {
                 theProducts =
                    (from e in NWC.Products
                     where e.ProductName.StartsWith(startString)
                    select e).ToList();
            }
            return View(theProducts);
        }

    }
}

and here is the contents of the ~/Views/Product/Index.cshtml file:

@model List<MvcApplication1.Models.Product>

@{
    ViewBag.Title = "Product";
}

<h2>Product</h2>

@* Use a very crude means to dump matching products out *@ 
@foreach (MvcApplication1.Models.Product p in Model) {
    @Html.Raw(p.ProductName + "<br />");
}
<br />
@* See this for example use of form and submit button
http://weblogs.asp.net/raduenuca/archive/2011/03/13/asp-net-mvc-cascading-dropdown-lists-tutorial-part-2-cascading-using-normal-form-post-html-beginform-helper.aspx *@
@using (Html.BeginForm("Index", "Product", FormMethod.Get)) { 
    @Html.Raw("Enter Search Text Here:<br />");
    <input type="text" name="startString" id="txtSearch" /><br />
    <input type="submit" value="Click Hear to Search" />
}

answered Mar 30, 2013 at 7:51

Shawn Eary

7522 gold badges7 silver badges28 bronze badges

2 Comments

DaniB Over a year ago

Hello Shawn. Thanks for the support. It needs to be black and white for me; I was clearly not understanding Razor fully. I have created two folders in the website: Views and Controllers. I have created two files too: Index.cshtml and ProductController.cs and I have placed the code inside as per your instructions. I am getting this error though: Unknown element 'mvcapplication1.models.product' or element is missing or cannot be placed here. What am I missing please? Thanks again.

Shawn Eary Over a year ago

The models.product object was generated from EntityFramework. It's usually a bit easier to code database queries via EntityFramework than it is to use straight SQL. When you use EF, you can use Linq to Entities to query the database which is a lot safer and easier to read than plain old SQL. I didn't post the EF code here because it is generally created automatically by the EF Designer.

Chris Singleton · Accepted Answer · 2022-04-19 20:25:40Z

0

You can connect to a container that connects directly via SQL Server data connection using the onclick event in your input or button type="submit". This button calls the class Insert method, the container class would be from what I am showing: ContYardDogAdmin

onclick=Insert(ContYardDogAdmin ContYardDogData)

Example: <button id="NewRowSubmitButton" runat="server" type="submit" onclick=Insert(ContYardDogAdmin ContYardDogData)>Submit

answered Apr 19, 2022 at 20:25

Chris Singleton

1651 gold badge1 silver badge14 bronze badges

Collectives™ on Stack Overflow

How do I post SQL data using submit button in asp.net c# razor

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related