SQL INSERT与撇号数据库 [英] SQL insert into database with apostrophe
问题描述
我正在对传统的ASP程序并插入到数据库以下内容:
I am running a program on classic ASP and inserting into a database with the following:
CreateJob.CommandText = "INSERT INTO dbo.Jobs (JobID, CompanyName, DateReceived, DateOfDocument, ClientReference, Subject, TypeOfService,DueDate,AssignedAgent, ClientName, Plaintiff, Defendant1, Defendant2, Defendant3, CourtJurisdiction, Court, Subtype, CourtNumber, Amount, ServiceMethod, JobNotes, JobStatus, CreatedBy, CreatedDate) VALUES (" & Request.Form("jobid") & ", '""" & Request.Form("compname") & """', '" & Request.Form("datereceived") & "','" & Request.Form("dateofdoc") & "', '" & Request.Form("clientref") & "', '" & Request.Form("subjects") & "', '" & Request.Form("TypeOfService") & "', '" & Request.Form("duedate") & "', '" & Request.Form("AssignedAgent") & "', '" & Request.Form("ClientName") & "', '" & Request.Form("Plaintiff") & "', '" & Request.Form("Defendant1") & "', '" & Request.Form("Defendant2") & "', '" & Request.Form("Defendant3") & "', '" & Request.Form("CourtJurisdiction") & "', '""" & Request.Form("Court") & """', '" & Request.Form("SubType") & "', '" & Request.Form("CourtNumber") & "', '" & Request.Form("Amount") & "','" & Request.Form("ServiceMethod") & "','" & Request.Form("JobNotes") & "', 'OPEN', '" & Session("LoggedName") & "', CURRENT_TIMESTAMP ) "
然而,如果价值的有一个撇号,程序崩溃,我不知道如何逃避它。
However, if one of the value has an apostrophe, the program crashes and I am not sure how to escape it.
感谢
推荐答案
取而代之的是不要去这里,你已经在使用 ADODB.Command
对象,这样的方式为什么不使用参数化一个查询。
Replace isn't the way to go here, you are already using a ADODB.Command
object so why not use a parameterised query.
试试这个;
由于您没有提供您的字段类型,我只能猜测这样反而我添加信息 [数据类型]
和 [大小]
占位符为你使用的 ADO数据类型常量的。在T-SQL的地图如何数据类型来结结实实<一个很好的资源/ A>是这篇文章 - 数据类型映射
As you haven't provided information on your field types I can only speculate so instead I've added [datatype]
and [size]
placeholders for you to replace with ADO data type constants. A good resource for how data types in T-SQL map to ado is this article - Data Type Mapping
sql = ""
sql = sql & "INSERT INTO dbo.Jobs (" & vbCrLf
sql = sql & "JobID, CompanyName, DateReceived, DateOfDocument, ClientReference" & vbCrLf
sql = sql & ", Subject, TypeOfService,DueDate,AssignedAgent, ClientName, Plaintiff" & vbCrLf
sql = sql & ", Defendant1, Defendant2, Defendant3, CourtJurisdiction, Court" & vbCrLf
sql = sql & ", Subtype, CourtNumber, Amount, ServiceMethod, JobNotes, JobStatus" & vbCrLf
sql = sql & ", CreatedBy, CreatedDate" & vbCrLf
sql = sql & ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
With CreateJob
.ActiveConnection = "yourconnectionstring"
.CommandType = adCmdText
.CommandText = sql
'Add your parameters (all 24 of them in order)
'Assumed JobID is int which equates to adInteger ADO data type constant.
.Parameters.Append(.CreateParameter("@JobID", adInteger, adParamInput, 4))
.Parameters.Append(.CreateParameter("@CompanyName", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@DateReceived", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@DateOfDocument", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@ClientReference", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Subject", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@TypeOfService", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@DueDate", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@AssignedAgent", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@ClientName", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Plaintiff", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Defendant1", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Defendant2", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Defendant3", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@CourtJurisdiction", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Court", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Subtype", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@CourtNumber", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@Amount", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@ServiceMethod", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@JobNotes", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@JobStatus", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@CreatedBy", [datatype], adParamInput, [size]))
.Parameters.Append(.CreateParameter("@CreatedDate", [datatype], adParamInput, [size]))
'Specify your parameter values may need some conversion based on what you are passing.
.Parameters("@JobId").Value = Request.QueryString("jobid")
'Add the other 23 parameters as the above line.
'...
'Doing an INSERT no need to return recordset
Call .Execute(adExecuteNoRecords)
End With
Set CreateJob = Nothing
这篇关于SQL INSERT与撇号数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!