当我想从sql中删除多行时，我有问题 [英] i have problem when i want delete multiple rows from sql

问题描述

你好



i使用存储过程。



当我想从数据库中删除多行时存储过程我处理错误无法将字符串转换为int32



我的代码是

hello

i work with stored procedure.

when i want delete multiple rows from database with stored procedure I deal with the error can not convert string to int32

my code is

protected void Button1_Click(object sender, EventArgs e)
    {
        string id = string.Empty;
        int i=0;
        foreach (GridDataItem mm in grdAllUsers.Items)
        {

            if (mm.Selected)
            {
                id += mm.GetDataKeyValue("usr_Id") + ",";
            }
            

        }

        string strid = id.Substring(0, id.LastIndexOf(","));

        Admin delet = new Admin();
        string req = delet.DeleteUserFromAdmin(strid);
        LoadGridAllUsers();
        Literal1.Text = strid + "/n REquest:" + req;

    }



 internal string DeletUserFromAdmin(string id)
    {
        
            
                SqlCommand cmd = new SqlCommand("DELETE FROM [dbo].[Users] WHERE usr_Id in ("+ id +") ", con);
                //cmd.CommandType = CommandType.StoredProcedure;

                //cmd.Parameters.Add("@userid", SqlDbType.Int).Value = id;

                try
                {

                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }

                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        return "Succ";
                    }
                    else
                    {
                        return "UnSucc";
                    }
                }

                catch (Exception ex)
                {

                    return ex.Message;
                }
                finally
                {
                    con.Close();
                }
             
            }

和存储过程代码是：

and stored procedure code is:

USE [MyWebsite]
GO
/****** Object:  StoredProcedure [dbo].[DeletUserFromAdmin]    Script Date: 3/23/2013 9:33:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[DeletUserFromAdmin]

@userid int


as

DELETE FROM [dbo].[Users]
      WHERE usr_Id in (@userid) 

usr）Id feild是int and dont更改它，因为它是我的表的id并使用sql server创建auto。

usr)Id feild is int and dont change it because it is a id of my table and create auto with sql server.

推荐答案

当您在IN中传递字符串时出现错误的原因子句并直接运行sql。



而是使用动态查询，如

当你使用in子句时，必须有可能删除多个ID的行。

所以从C＃传递像1.4.7这样的用户ID

The reason you are getting error as you are passing a string in "IN " clause and run the sql directly.

Instead use dynamic query like
When you use in clause, then there must be a possibility of deleting rows for multiple ids.
So pass the userid like "1.4.7" from C#

ALTER proc [dbo].[DeletUserFromAdmin]
@userid vrachar(max)
as
  declare @sql varchar(max)='''';
  set @sql = ''DELETE FROM [dbo].[Users] WHERE usr_Id in ('' + @userid + '') '';
  execute (@sql);

即使你可以使用参数化动态查询来摆脱sql注入。



希望这会有所帮助。

干杯

Even you can use parameterized dynamic query for getting rid of sql injection.

Hope this helps.
Cheers

这篇关于当我想从sql中删除多行时，我有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！