Shulou(Shulou.com)06/02 Report--

This article mainly introduces how to deal with the problem of single quotation marks when C# inserts records into SQL Server, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

Asp.net uses cquote to insert record values (title, content) [article title and content] into the coredb.mybbs table. Because content and title may contain single quotation marks, the insert command that uses sql directly will report an error. There are two ways to deal with this, one is to replace single quotation marks with two single quotation marks, and the second is to use stored procedures.

The format of the table mybbs is defined as follows:

Create table [dbo]. [mybbs] ([id] [bigint] identity (1,1) not null, [title] [char] (160,160) collate chinese_prc_ci_as null, [author] [char] (20) collate chinese_prc_ci_as null, [date_of_created] [datetime] null, [abstract] [char] (480) collate chinese_prc_ci_as null [content] [ntext] collate chinese_prc_ci_as not null) on [primary] textimage_on [primary]

1. One of the ways to solve the problem of single quotation when C # inserts records into Sql Server: replace single quotation marks with two single quotation marks:

Sqlconnection coredb=new sqlconnection (); coredb.connectionstring= "workstation id=\" gqa-eric-lv\ "; packet size=4096;integrated security=sspi;" + "data source=\" gqa-eric-lv\ "; persist security info=false;initial catalog=coredb"; / / single quotation marks replaced with "" to insert into the sql server String title=textbox1.text.replace (","); string content=textbox2.text.replace (","); if (title.trim ()) = "| content.trim () =") return; string insertcmd = @ "insert into mybbs (title,content) values (" + title + "," + content+ ")"; sqlcommand mycommand = new sqlcommand (insertcmd,coredb); coredb.open (); sqldatareader myreader = mycommand.executereader (); myreader.close (); coredb.close ()

2. When C # inserts records into Sql Server, the second way to solve the problem is to insert records by using stored procedures.

1) create a stored procedure:

Create proc insertmybbsproc (@ title char, @ author char (20), @ content ntext) as insert into mybbs (title,author,content) values (@ title, @ author, @ content)

2) Test the stored procedure in the query analyzer:

Declare @ title char, declare @ author char, declare @ content char, set @ title=test title 3 set @ author=david euler 3 set @ content=it is the content 3 exec insertmybbsproc @ title, @ author, @ content

3) C # executes stored procedures through sqlcommand:

Sqlconnection coredb=new sqlconnection (); coredb.connectionstring= "workstation id=\" gqa-eric-lv\ "; packet size=4096;integrated security=sspi;" + "data source=\" gqa-eric-lv\ "; persist security info=false;initial catalog=coredb"; string title=textbox1.text; string content=textbox2.text; if (title.trim ()) = "| | content.trim () =") return; / / insertmybbsproc is the procedure that inserts data into mybbs: sqlcommand insertcmd = new sqlcommand (" insertmybbsproc ", coredb); insertcmd.commandtype=commandtype.storedprocedure / / Command type is stored procedure; the following parameter objects are defined: sqlparameter prm1=new sqlparameter ("@ title", sqldbtype.char,160); sqlparameter prm2=new sqlparameter ("@ author", sqldbtype.char,20); sqlparameter prm3=new sqlparameter ("@ content", sqldbtype.ntext,1073741823); prm1.direction=parameterdirection.input; prm2.direction=parameterdirection.input; prm3.direction=parameterdirection.input; / / add sql parameters to insertcmd: insertcmd.parameters.add (prm1); insertcmd.parameters.add (prm2) Insertcmd.parameters.add (prm3); / / assign values to the sql parameter: prm1.value=title; prm2.value= "david euler"; prm3.value=content; coredb.open (); int recordsaffected=insertcmd.executenonquery (); if (recordsaffected==1) response.write ("

Alert ("+" insert successfully "+")

Thank you for reading this article carefully. I hope the article "how to deal with the problem of single quotation marks when inserting records into SQL Server by C#" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.