C#中的StoredProcedure，从文本框中传递参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18326946/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
StoredProcedure in C#, passing parameters from text boxes
提问by Andrey
I have very simple page just wan to find out how to pass data from text boxes into my procedure. I am kind of new with passing parameters from code behind in to DB,I have one page for that code is
我有一个非常简单的页面，只是想了解如何将文本框中的数据传递到我的程序中。我对将参数从后面的代码传递到 DB 有点陌生，我有一页该代码是
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="LastName" runat="server"></asp:TextBox>
<asp:TextBox ID="FirstName" runat="server"></asp:TextBox>
<asp:TextBox ID="Phone1" runat="server"></asp:TextBox>
<asp:TextBox ID="Phone2" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TicketsConnectionString %>" SelectCommand="SELECT * FROM [Employee]"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
and aspx code
和aspx代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
string connectionString = ConfigurationManager.ConnectionStrings["TicketsConnectionString"].ConnectionString;
protected void Button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("InsertIntoEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = LastName.Text;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar);
cmd.Parameters.Add("@Phone1", SqlDbType.VarChar);
cmd.Parameters.Add("@Phone2", SqlDbType.VarChar);
cmd.Parameters["@LastName"].Value = LastName.Text;
cmd.Parameters["@FirstName"].Value = FirstName.Text;
cmd.Parameters["@Phone1"].Value = Phone1.Text;
cmd.Parameters["@Phone2"].Value = Phone2.Text;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
web.config looks like this
web.config 看起来像这样
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="TicketsConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename="C:\Users\Asya\Documents\Visual Studio 2012\WebSites\WebSite6\App_Code\Tickets.mdf";Integrated Security=True;Connect Timeout=30"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.5"/>
<httpRuntime targetFramework="4.5"/>
</system.web>
</configuration>
When i pass simple data in to my prosedure it throws error
当我将简单数据传递给我的 prosedure 时，它会引发错误
An SqlParameter with ParameterName '@LastName' is not contained by this SqlParameterCollection.
How to resolve this problem? Because on DB side procedure works perfectly As I saed before I am really new in to it. and I fixed previous problems but here another one
如何解决这个问题？因为在 DB 端程序完美运行正如我之前所说的那样，我真的很陌生。我解决了以前的问题，但这里又一个
The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.FormatException: The string was not recognized as a valid DateTime. There is an unknown word starting at index 0.
Source Error:
Line 34: cmd.Parameters["@Phone2"].Value = Phone2.Text;
Line 35: con.Open();
Line 36: cmd.ExecuteNonQuery();
Line 37: con.Close();
Line 38:
Procedure which i use
我使用的程序
procedure InsertIntoEmployee
@LastName Nvarchar (25) ,
@FirstName Nvarchar (25) ,
@Phone1 Nvarchar (25) ,
@Phone2 Nvarchar (25)
as
insert into Employee (LastName , FirstName,Phone1,Phone2)values (@LastName,@FirstName,@Phone1,@Phone2);
when i run prosedure on Db side like
EXEC InsertIntoEmployee N'петров', N'петр', 99999999,nullit works perfectly but from asp.net side its throwing errors
当我在 Db 端运行 prosedure 时，EXEC InsertIntoEmployee N'петров', N'петр', 99999999,null它运行良好，
但从 asp.net 端它抛出错误
采纳答案by Ramesh Rajendran
Try this
尝试这个
protected void Button1_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("InsertIntoEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@LastName", SqlDbType.NVarChar(MAX)).Value = LastName.Text;
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar(MAX)).Value = FirstName.Text;
cmd.Parameters.Add("@Phone1", SqlDbType.NVarChar(MAX)).Value = Phone1.Text;
cmd.Parameters.Add("@Phone2", SqlDbType.NVarChar(MAX)).Value = Phone2.Text;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
// gvQuarterlyReport.DataBind();
}
回答by OnoSendai
Correct me if I'm wrong, but it seems that this line
如果我错了，请纠正我，但似乎这条线
cmd.Parameters.Add("@LastName ", SqlDbType.Date);
Have an extra space after LastName. Should it be
在LastName之后有一个额外的空间。应该是
cmd.Parameters.Add("@LastName", SqlDbType.Date);
Instead?
反而？
回答by Adriaan Stander
You need to remove the space from "@LastName "
您需要从“@LastName”中删除空格
Something like
就像是
cmd.Parameters.Add("@LastName", SqlDbType.Date);
But that will just transfer the error to the nest line as @FirstNameis also not in the parameter list.
但这只会将错误转移到嵌套行，因为@FirstName它也不在参数列表中。
In fact, after you have correct @LastName
事实上，在你有正确的@LastName 之后
all 3 these line will cause problems
所有这 3 条线都会导致问题
cmd.Parameters["@FirstName"].Value = FirstName.Text;
cmd.Parameters["@Phone1"].Value = Phone1.Text;
cmd.Parameters["@Phone2"].Value = Phone2.Text
回答by Christian.K
The other answers about the space are correct, of course. Note that you can avoid such issues (or at least have them consistently wrong ;-) if you assign the parameters values right away, like so:
当然，关于空间的其他答案是正确的。请注意，如果您立即分配参数值，则可以避免此类问题（或至少使它们始终错误；-），如下所示：
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = LastName.Text;
Furthermore you used SqlDbType.Datefor your @LastNameparameter. Unless you have a very weird schema, that is most likely wrong. Hence, I used SqlDbType.VarCharabove.
此外，您用于SqlDbType.Date您的@LastName参数。除非您有一个非常奇怪的架构，否则这很可能是错误的。因此，我在SqlDbType.VarChar上面使用。
