使用storedprocedure将数据插入数据库的问题？ [英] problems on inserting data into database using storedprocedure?

问题描述

我试过的代码如下所示，你也可以查看这个代码

  string  cs = ConfigurationManager .ConnectionStrings [  SampleConnectionString]。ConnectionString; 
 
 使用（SqlConnection con =  new  SqlConnection（cs））
 使用（SqlCommand cmd =  new  SqlCommand（  spRegisterUser，con））
 {
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.AddWithValue（  @ Username，txtusername.Text）; 
 cmd.Parameters.AddWithValue（  @ Password，txtpassword.Text）; 
 cmd.Parameters.AddWithValue（  @ Email，txtemail.Text）; 
 con.Open（）; 
  int  count =（ int ）cmd.ExecuteScalar（）; 
  if （count == -1）
 {
 Label1.Text =   用户ID不可用请选择另一个; 
} 
  else  
 {
 Response.Redirect（ 〜/ Login.aspx）; 
} 

和存储过程是



< pre lang =SQL> CREATE 过程 spRegisterUser
@Username nvarchar （ 50 ），
@ Password nvarchar （ 50 ），
@ Email nvarchar （ 50 ）
作为
开始
声明 @ Count int
声明 @ Returncount int

选择 @ Count = COUNT（用户名）
来自 tblUsers 其中用户名= @用户名

如果 @ Count > 0
开始
设置 @ Returncount = -1
结束
Else
开始
set @ Returncount = 1
插入 < span class =code-keyword> into tblUsers values （ @ Username ， @ Password ， @ Email ）
结束
选择 @ Returncount as ReturnValue
结束

web.config中的Connectionstring是



< add name =SampleConnectionStringconnectionstring =Data Source = HEMANTH-PC;< br mode =hold =/>初始目录=样本;用户ID = sa;密码= abc>





问题是数据未存储在数据库中甚至没有得到任何异常和警告帮助我找到问题。

提前谢谢

解决方案

我不知道您的表设计，我只能猜测，但您可能正在使用身份字段作为实际的行ID - 并且由于您没有在INSERT语句中指定列，因此SQL将尝试按数字顺序插入它们因此，您的用户名将进入您的ID列，您的密码将进入您的用户名列，依此类推。并且SQL会抱怨您无法将文本插入数字字段，并且您无法设置值无论如何都是一个身份字段！

总是列出你要插入的列：

  INSERT   INTO  tblUsers（[UserName]，[Password]，[Eail] ]） VALUES （ @ Username ， @ Password ， @ Email ）

但请不要存储密码像那样！切勿以明文形式存储密码 - 这是一个主要的安全风险。有关如何在此处执行此操作的信息：密码存储：如何做到这一点。 [ ^ ]

添加到解决方案1不确定但是在

 使用（SqlConnection con ... 

不应该是

 使用（SqlConnection con =  new  SqlConnection（cs））
 {
 使用（SqlCommand cmd =  new  SqlCommand（ < span class =code-string> spRegisterUser，con））
 {
 cmd.CommandType = CommandType.StoredProcedure; 
 ... 

the code i have tried is below you can check this as well

string cs = ConfigurationManager.ConnectionStrings["SampleConnectionString"].ConnectionString;

using (SqlConnection con = new SqlConnection(cs))
using(SqlCommand cmd = new SqlCommand("spRegisterUser",con))
 {
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("@Username", txtusername.Text);
 cmd.Parameters.AddWithValue("@Password", txtpassword.Text);
 cmd.Parameters.AddWithValue("@Email", txtemail.Text);
 con.Open();
int count = (int)cmd.ExecuteScalar();
if ( count == -1)
{
 Label1.Text = "User id is not avalilable please choose another one";
 }
 else
 {
 Response.Redirect("~/Login.aspx");
 }

and the stored procedure is

CREATE Procedure spRegisterUser   
@Username nvarchar(50),  
@Password nvarchar(50),  
@Email nvarchar(50)  
As  
Begin  
  Declare @Count int  
  Declare @Returncount int  
    
   Select @Count = COUNT(Username)  
   from tblUsers Where Username=@Username  
    
   if @Count > 0   
   Begin  
   Set @Returncount = -1  
   End  
   Else  
   Begin  
   set @Returncount = 1  
   Insert into tblUsers values(@Username,@Password,@Email)  
   End  
   Select @Returncount as ReturnValue  
   End

Connectionstring in web.config is

<add name="SampleConnectionString" connectionstring="Data Source=HEMANTH-PC;<br mode=" hold=" /> Initial Catalog=Sample;User ID=sa;Password=abc">


Problem is data is not getting stored in database and not even get
ting any exception and warning help me to find the problem.
Thanks in advance

解决方案

Without knowing your table design, I can only guess, but it's likely that you are using an identity field for your actual row id - and since you don't specify columns on your INSERT statement, SQL will attempt to insert them in numerical order. So your Username will go in your ID column, your Password will go in your Username column, and so on. And SQL will complain that you can't insert text into a numeric field, and that you can't set the value of an Identity field anyway!
Always list the columns you want to INSERT into:

INSERT INTO tblUsers ([UserName], [Password], [EMail]) VALUES (@Username,@Password,@Email)

But please, don't store passwords like that! Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]

To add to solution 1 Not sure but are you missing an opening bracket after

using (SqlConnection con...

Shouldn't it be

using (SqlConnection con = new SqlConnection(cs))
{
   using(SqlCommand cmd = new SqlCommand("spRegisterUser",con))
   {
      cmd.CommandType = CommandType.StoredProcedure;
...

这篇关于使用storedprocedure将数据插入数据库的问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！