替换不是这里的方法,您已经在使用ADODB.Command
对象所以为什么不使用参数化查询 http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html.
尝试这个;
由于您没有提供有关字段类型的信息,我只能推测,所以我添加了[datatype]
and [size]
供您替换的占位符ADO 数据类型常量 http://msdn.microsoft.com/en-us/library/windows/desktop/ms675318(v=vs.85).aspx。有关 T-SQL 中的数据类型如何映射到的良好资源ado /questions/tagged/ado这篇文章是——数据类型映射 http://carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
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