SQL insert into database with apostrophe

by

Replace isn’t the way to go here, you are already using a ADODB.Command object so why not use a parameterised query.

Try this;

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

More Related Contents:

  1. Convert UTF-8 String Classic ASP to SQL Database
  2. Using Stored Procedure in Classical ASP .. execute and get results
  3. How to use ASP variables in SQL statement
  4. How to make a parametrized SQL Query on Classic ASP?
  5. operation not allowed when the object is closed when running more advanced query
  6. Error trying to call stored procedure with prepared statement
  7. How do I create a parameterized SQL query? Why Should I?
  8. Auto Increment after delete in MySQL
  9. Update table values from another table with the same user name
  10. What is the string concatenation operator in Oracle?
  11. Using an Alias in a WHERE clause
  12. How do I delete from multiple tables using INNER JOIN in SQL server
  13. Does Foreign Key improve query performance?
  14. SQL query for finding records where count > 1
  15. SQL Server convert string to datetime
  16. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  17. Return number of rows affected by UPDATE statements
  18. SQL speed up performance of insert?
  19. SQL Server: Cannot insert an explicit value into a timestamp column
  20. Error: Column does not exist
  21. TransactSQL to run another TransactSQL script
  22. How to Concatenate Numbers and Strings to Format Numbers in T-SQL?
  23. SQL server identity column values start at 0 instead of 1
  24. SQL method to replace repeating blanks with single blanks
  25. String concatenation does not work in SQLite
  26. How do I view grants on Redshift
  27. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  28. Determine if one coordinate is in radius of another
  29. Entity Framework Skip/Take is very slow when number to skip is big
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment