У меня есть хранимая процедура с select statment, которая имеет параметр, как я могу использовать ее с ASP.NET
i have stored procedure with select statment that has parameter how can i use it with asp.net when i try to insert data using the web form data added to the data base but when i wnat to retrive this data back from database i got this error "System.Data.SqlClient.SqlException: 'Procedure or function 'SelectData' expects parameter '@productName', which was not supplied."
<pre><%@ Page Language="C#" AutoEventWireup="true" trace="true" CodeBehind="WebForm5.aspx.cs" Inherits="Learn_Ado.WebForm5" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td><asp:Label ID="Label1" runat="server" Text="Product Name"></asp:Label></td>
<td><asp:TextBox ID="Product" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td><asp:Label ID="Label2" runat="server" Text="Unit Price"></asp:Label></td>
<td><asp:TextBox ID="UnitPrice" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td><asp:Button ID="Button1" runat="server" Text="Sumbit" OnClick="Button1_Click" /></td>
</tr>
</table>
<asp:Label ID="record" runat="server"></asp:Label>
<asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
<AlternatingRowStyle BackColor="#DCDCDC" />
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#F1F1F1" />
<SortedAscendingHeaderStyle BackColor="#0000A9" />
<SortedDescendingCellStyle BackColor="#CAC9C9" />
<SortedDescendingHeaderStyle BackColor="#000065" />
</asp:GridView>
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
namespace Learn_Ado
{
public partial class WebForm5 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings["MSSQLDATABASE"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("Procedure", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@productName", Product.Text);
cmd.Parameters.AddWithValue("@unitPrice", UnitPrice.Text);
con.Open();
int Record = cmd.ExecuteNonQuery();
record.Text = Record.ToString();
}
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SelectData";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = con;
con.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
GridView1.DataSource = rdr;
GridView1.DataBind();
}
}
}
}
}
}
<pre>-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE SelectData
-- Add the parameters for the stored procedure here
@productName varchar(1000),
@unitPrice int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select * from Products
where productName = @productName
AND unitPrice = @unitPrice;
END
Что я уже пробовал:
то что я пробовал я сделал sql
<pre>-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE SelectData
-- Add the parameters for the stored procedure here
@productName varchar(1000),
@unitPrice int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select productName , unitPrice from Products;
END
но я думаю, что этот дозент предотвратит sql-инъекцию