SQL fügt mit Apostroph in die database ein

Ich führe ein Programm auf klassischem ASP aus und füge in eine database mit dem folgenden ein:

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 ) " 

Wenn jedoch einer der Werte einen Apostroph hat, stürzt das Programm ab und ich bin nicht sicher, wie ich es umgehen kann.

Vielen Dank

Ersetzen ist nicht der path, hier zu gehen, Sie verwenden bereits ein ADODB.Command object, also warum nicht eine parametrisierte Abfrage verwenden .

Versuche dies;

Da Sie keine Informationen zu Ihren Feldtypen angegeben haben, kann ich nur spekulieren, stattdessen habe ich Platzhalter [datatype] und [size] hinzugefügt, die Sie durch ADO-datatypkonstanten replace können . Eine gute Ressource für die Zuordnung von datatypen in T-SQL ist dieser Artikel – datatypzuordnung

 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