October 22, 2003 at 11:31 am
I am having a problem when I try to run an Insert INTO statement..
From an application I'm using the SQL syntax as follows:
INSERT INTO tblAdjustment (AdjDate, Credit, Contactno, fullname) values ('" & me.txtAdjDate & "', '" & me.txtCredit & "', '" & me.txtContactno & "', '" & me.txtname & "')
I get an error because the name being passed in is: O'Brian. We would like to try and keep the actual name with the single quote, so I tried to re-create with actual values in a T-SQL statement but it is still wrong. This is the error in T-SQL I get:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'O'Brian'.
I looked up in the T-SQL help and also replaced:
'" & me.txtname & "' with
""" & me.txtname & """ and then the error goes away that it isn't end of statement, but it says something is wrong with value 'O'Brian'.
Help. Does anyone know how to get a string value that has the single quote in it without getting errors?
Julie H.
October 22, 2003 at 12:38 pm
From your use of ampersands I infer that this is Visual Basic. Create a function in your project to replace the single quotes with double quotes when using values with SQL Server, e.g.:
Public Function SQLFmt(Val as String) As String
SQLFmt = Replace(Val, "'", "''")
End Function
--Jonathan
--Jonathan
October 23, 2003 at 7:55 am
To add my two cents if you use the quoted value on a parameter that is used for dynamic TSQL take a look at QUOTENAME on BOL
HTH
* Noel
October 23, 2003 at 2:50 pm
The following is from the BOL
When QUOTED_IDENTIFIER is ON, SQL Server follows the SQL-92 rules for the use of double quotation marks and the single quotation mark (') in SQL statements:
Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings.
To maintain compatibility with existing applications, SQL Server does not fully enforce this rule. Character strings can be enclosed in double quotation marks if the string does not exceed the length of an identifier; this practice is not recommended.
Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers.
If the character string contains an embedded single quotation mark, insert an additional single quotation mark in front of the embedded mark:
SELECT * FROM "My Table"
WHERE "Last Name" = 'O''Brien'
When QUOTED_IDENTIFIER is OFF, SQL Server follows these rules for the use of single and double quotation marks:
Quotation marks cannot be used to delimit identifiers. Instead, use brackets as delimiters.
Single or double quotation marks can be used to enclose character strings.
If double quotation marks are used, embedded single quotation marks do not have to be denoted by two single quotation marks:
SELECT * FROM [My Table]
WHERE [Last Name] = "O'Brien"
Joe Johnson
NETDIO,LLC.
Edited by - johnsonj on 10/23/2003 2:50:59 PM
Joe Johnson
NETDIO,LLC.
November 19, 2003 at 6:35 am
I like Joe Johnson's information. This approach means making a modification to you code implementation to check for and handle any names that may include the apostrophe, intercept them and append the additional apostrophe. This to me seems like a straight forward approach.
November 19, 2003 at 7:29 am
The best practice is to use stored procedures as command objects with parameter objects at the front end. This is fastest, most secure, and doesn't require any string conversions by the front-end. E.g.:
CREATE PROC dbo.p_Ins_Adj
@Date datetime,
@Credit money,
@ContactNo int,
@Name varchar(80)
AS
SET NOCOUNT ON
INSERT INTO dbo.Adjustments(AdjDate, Credit, ContactNo, FullName)
VALUES(@Date, @Credit, @ContactNo, @Name)
With ADOcmd
.ActiveConnection = ADOconn
.CommandType = adCmdStoredProc
.CommandText = "dbo.p_Ins_Adj"
.Parameters.Append .CreateParameter("@Date", adDBTimeStamp, adParamInput, , datAdjDate)
.Parameters.Append .CreateParameter("@Credit", adCurrency, adParamInput, , curCredit)
.Parameters.Append .CreateParameter("@ContactNo", adInteger, adParamInput, , lngContactNo)
.Parameters.Append .CreateParameter("@Name", adVarChar, adParamInput, 80, strName)
.Execute , , adExecuteNoRecords
End With
One would also use some error handling, of course.
--Jonathan
Edited by - Jonathan on 11/19/2003 07:32:39 AM
--Jonathan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply