Problem with single quotes

Hi,
I've a column (type: varchar) in a table. I need to add single quotes in the
value while inserting the rows. But I get an error. Please see below:
create table #t1 (address varchar(20))
go
insert into #t1 values ('St John's street')
Error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ')
Since the database is on hosting providers server, I cannot change the
settings of the server. Please help me by providing any suitable solution.
Also, note that I've an ASP script which insert rows into the table.
Thanks in advance.
-VenkatHi Venkat,
To solve the problem you need to generate insert statement like following in
your ASP script:
insert into #t1 values ('St John''s street')
i.e. replace single quote ( ' ) by two single quotes ( '' )
Krish
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq#aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
the
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||Hi
A single quote within a string can be escapped with a second quote.
insert into #t1 (address) values ('St John''s street')
John
"G.V.Reddy" wrote:

> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in t
he
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>
>|||Thank you very much Krish and John.
Since the address is entered by the visitors on the web site, do we need to
check each and every value entered/inserted into the varchar field for the
single quotes? In case the answer is Yes, I think we can do it by writing a
function which replaces a single quote with adding another quote. Is there
any other simple method to integrate this functionality (escaping with
another single quote) into the ASP code?
Thanks in advance.
-Venkat
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
> the value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||G.V.Reddy wrote:
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we
> need to check each and every value entered/inserted into the varchar
> field for the single quotes? In case the answer is Yes, I think we
> can do it by writing a function which replaces a single quote with
> adding another quote. Is there any other simple method to integrate
> this functionality (escaping with another single quote) into the ASP
> code?
> Thanks in advance.
> -Venkat
>
Look into the use of "parameters". I don't know the exact details for *asp*,
but they are something like:
* provide placeholders in you sql string :
insert into #t1 values (?)
* create a parameter, fill it with the "plain" value ("St John's street"), n
o need
to escape quotes, then add that parameter to the command object
* execute the query
Hans Kesting|||Hi
For ASP/ADO check out the SQL Server samples
http://msdn.microsoft.com/library/d...
5ym.asp
John|||replace(strParam, "'","''")|||Yes, a function to "double up" the single quote items will help. It would be
even better to use parameterized command objects instead of concatenated SQL
strings for communicating with the database (I am guessing you are using ADO
in your ASP application). Building concatenated SQL strings leaves you
application open to SQL Injection attacks, which is a severe security issue.
For information on ADO Command objects and parameters, see:
http://msdn.microsoft.com/library/d...rsreference.asp
For information about SQL injection attacks, see:
http://search.microsoft.com/search/.../>
0&s=1&swc=0
http://www.google.com/search?hl=en&q=sql+injection
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:OxZF4q$aFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we need
to
> check each and every value entered/inserted into the varchar field for the
> single quotes? In case the answer is Yes, I think we can do it by writing
a
> function which replaces a single quote with adding another quote. Is there
> any other simple method to integrate this functionality (escaping with
> another single quote) into the ASP code?
> Thanks in advance.
> -Venkat
>
> "G.V.Reddy" <vreddyg@.go.com> wrote in message
> news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
below:
solution.
>
>sql

Problem with simple SQL

I want to view all companyname that starts with A, the sql runs but does not show the output,
What's wrong with the query?, am I missing any quotes or comma. Pls 'elp
Dim myCharacter = "A"
SQL = "SELECTlogo, EmployerID, CompanyName FROM EmployerDetails wherecompanyName like '"&myCharacter & "%'"



Select seems correct! How you are using this in ASP.NET page? Are you getting data into Reader or not?|||Thanks Sreed
I think the problem is the SQL because when I hard code to this
SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like 'A%' "
It works, PLS help Thanks
|||

If you declared myCharacter,SQL as a local string variable, then it should work!

Just to debug after SQL = "..blabla" statement do,

Response.Write(SQL)
Response.End()
and run the page and see what is the output you are getting from SQL, and see if that is giving correct Select or not!

That should help us to find whether it building currect SQL or not!

|||Hi Sreed
This is the full code below , I did the PRINT STUFF and the output is also below
Sub BindDataToGrid(myCharacter)
Response.write("Testing "&myCharacter)
Dim Conn, Conn1 as SqlConnection
Dim mySqlCommand, mySqlCommand2 as SqlCommand
Dim SQL,SQL1,SQL2 ,GetQuestions As String
Dim myDate as date
Dim pic as ArrayList = new ArrayList()
Dim Job_no as Integer

IF myCharacter <> "" THEN
SQL ="SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL & " ' " & myCharacter & " % ' "
Else
SQL ="SELECT logo, EmployerID FROM EmployerDetails order by newID() "
End If
Conn = New SqlConnection(ConfigurationSettings.AppSettings("connString"))
Dim resultsDataSet as New DataSet()
Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(SQL, Conn)

Conn.Open()
myDataAdapter.Fill(resultsDataSet,"EmployerDetails")
mySqlCommand = New SqlCommand(SQL, Conn)
mySqlCommand.ExecuteNonQuery()

Dim myReader As SqlDataReader = mysqlCommand.ExecuteReader()
Dim content
While myReader.Read()
If not (myReader("Logo") is DBNull.Value) Then
If Len(myReader("Logo")) > 0 Then
Dim LOGO = myReader("Logo")
Dim EmployerID =myReader("EmployerID")
Content = "<br><ahref='/JBoard/EmployerJobs.aspx?EMPID="&EmployerID
Content = Content & " '><img src='/JBoard/CompLogo/"&Logo
Content = Content & " ' width='124' height='50'BORDER='1'></a>"
pic.Add(Content)
End If
End If
End While

LogoGrid.DataSource = pic
LogoGrid.DataBind()
Conn.Close()
End Sub

OUTPUT IS
SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like 'M % '
What is wrong with this tiny code, forum help me pls.


|||

SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL &" ' " & myCharacter &" % ' "

This looks like you have extra spaces in there. If you copied and pasted this directly from your code, you're actually querying for anything that starts with a space and then an 'A' and then has a space and then any character and then a space. Drop the spaces in theRED code above and it should work.

|||Hi PD
How do you drop the space, that's why I thought ?

|||Use your keyboard's Delete key to remove the extra spaces around thesingle quote and percentage characters. (A total of 4 spacesshould be deleted.)
That should leave you with the following statement:
SQL = "SELECT logo, EmployerID, CompanyName FROM EmployerDetails where companyName like "
SQL = SQL &"'" & myCharacter &"%' "
|||

tmorton wrote:

Use your keyboard's Delete key to remove the extra spaces

In a recent technical journal, it suggested that the Backspace key canalso be used to remove characters. I haven't tried it myself, as I fearthat I'm not advanced enough. But obinna123 seems pretty advanced, sohe may want to try that technique to removed the extra spaces.

Problem With Quotes in @[System::ErrorDescription] Variable

I am using an Execute T-SQL Task as a part of an OnError event Handler in my SSIS Package. When occurs an error, using the Expressions-feature, my Execute T-SQL task builds an Insert Statement to insert the @.System::ErrorDescription into a table.

"
INSERT INTO [ErrorDB].[dbo].[ISErrors]
([EventType]
,[PackageName]
,[TaskName]
,[DateDone]
,[Status]
,[Host]
,[ErrorCode]
,[ErrorDescription]
,[Comments])
VALUES
( 'OnError'
, '"+ @.[System::PackageName] + "'
, '"+ @.[System::SourceName] + "'
,getdate()
,'Failed'
,'" + @.[System::MachineName] + "'
, null
, '" + @.[System::ErrorDescription] + "'
,null
)

"

When I run the task ( not the package, only the task) everything is ok ( since the ErrorDescription variable is empty)

But when an error occurs in my package, then the T-SQL task fails giving the following error

[Execute SQL Task] Error: Executing the query " INSERT INTO [LogDB].[dbo].[ISFullMaintenanceErrors] ([EventType] ,[PackageName] ,[TaskName] ,[DateDone] ,[Status] ,[Host] ,[ErrorCode] ,[ErrorDescription] ,[Comments]) VALUES ( 'OnError' , 'Package' , 'TrialTempEx' ,getdate() ,'Failed' ,'SCYLLA' , null , @.[System::ErrorDescription] ,null ) " failed with the following error: "Must declare the scalar variable "@.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I realized that the problem is that the @.[System::ErrorDescription] contains quotes ( " ' ) and this is the reason that the insert statement fails. I tried the replace function but there was no solution

Any help would be appreciated

For this very reason I advocate using the parameter support in the Exec SQL Task, over expressions for this type of statement. This is basically a SQL injection attack, albeit benign, but by using a parameterised statement, you can protect yourself from this. The other issue you may hit is with long descriptions, you could exceed the 4000 character limit for an expression result.|||

Darren and I don't exactly see eye-to-eye on this one but I'll concede he makes a good, if slightly dramatic, point about SQL Injection

If you do want to carry on using expressions then you can just wrap the variable in a REPLACE() function.

-Jamie