在参数中调用带有撇号的存储过程不起作用 [英] calling stored procedure with apostrophe in argument doesn't work

问题描述

我有一个 C# 应用程序，它的部分工作是将 Excel 文件中的所有数据插入到数据库中.

I have an app in C# when part of its job is to insert all the data from an Excel file to a database.

从 Excel 获取数据的工作没问题，我正在使用存储过程将数据插入数据库中的表中.

The job of getting the data from Excel is alright, and I am using stored procedures to insert the data into the tables in the DB.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[sp_Insert]

@id int,
@brand nvarchar(50),
@model nvarchar(50),
@prodNumber nvarchar(50),
@category nvarchar(50),
@local nvarchar(50)

AS
INSERT INTO Tbl_Registos (ID, Brand, Model, [Product Number], Category, Local)
VALUES (@id, @brand, @model, @prodNumber, @category, @local)

这是一个 SP 完美运行的例子:

This is one example when the SP works perfectly:

sp_Insert N'1', N'Brand example', N'ABC123', N'123456-7890', 
          N'Laptop', N'18'

当我遇到这样的情况时就会出现问题:

The problem comes when I have cases like this:

sp_Insert N'1', N'Brand example', N'ABC123', N'123456-7890',
          N'Laptop', N'Director's floor'

当我在参数中间有这个 ' 时.我想使用引号 []，但在我有整数的情况下，我不能将它们放在引号之间.

When I have this ' in the middle of the argument. I thought to use quotes [], but in cases where I have integers, I can't put them between quotes.

从 Excel 中获取一行后，我使用循环将参数放入作为查询的字符串中.

After I got a row from Excel, I am using a loop to put the arguments in the string that is the query.

推荐答案

您可以通过在输入中放置另一个 ' 来对输入中的 ' 进行转义，并且插入将起作用.您可以执行此操作，也可以解析此类字符的数据并忽略它们.因此，带有此转义符的插入语句将是:

You can escape the ' in your input by putting another ' in front of it and the insert will work. You can either do this or parse the data for characters like this and disregard them. So your insert statement with this escaped would be:

sp_Insert N'1', N'Brand example', N'ABC123', N'123456-7890', N'Laptop', N'Director''s floor'

这篇关于在参数中调用带有撇号的存储过程不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！