問題描述

我有一個 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'

這篇關于在參數中調用帶有撇號的存儲過程不起作用的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！