select statement with special char

Question

select statement with special char

Frances L

SSCarpal Tunnel

Points: 4727
More actions
November 21, 2007 at 8:51 am

#377099

select EMPLNO,[Full Name],[CCNAME],[CC] from HREMP where [Full Name] like '%" & strS & "%' and [CC] like '01032%' and (EMPSTATUS <> 90 or EMPSTATUS is null) order by [Full NAME];"
If end user enter strS= O'CONNELL, it will give me error .
how to solve the problem ?
Thx.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

select EMPLNO,[Full Name],[CCNAME],[CC] from HREMP where [Full Name] like '%" & REPLACE(strS,'''','''''') & "%' and [CC] like '01032%' and (EMPSTATUS <> 90 or EMPSTATUS is null) order by [Full NAME];"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 2

Ananth-431739

Ten Centuries

Points: 1185

November 26, 2007 at 2:51 am

#753900

Replace single quotes with double singlequotes.

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 3

I tried that before. While I bulit the vb.net application. it gave me error.

strS=Replace(strS,'''',''""")

it has bulit error. expression expression expected.

if I put strS with replace in my string like Jeff M. did. it gave me green. like comment out that line.

Thx.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 4

The code which Jeff has posted is for T-SQL and not for VB. T-SQL uses single quotes to delimit strings (can be changed with QUOTED IDENTIFIER set option) whereas VB uses double quotes.

Replace with this

strS=Replace(strS,"'","''")

But what if somebody puts up % in the name:hehe:?

--Ramesh

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 5

but the outcome did not come out as I expected.

select EMPLNO,[Full Name],[CCNAME],[CC] from HREMP where [Full Name] like '%O''CONNELL%' and [CC] like '01032%' and (EMPSTATUS <> 90 or EMPSTATUS is null) order by [Full NAME];"

I have O'Connell in the database. it did not show up at all.;)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Ok... are we working in VB or T-SQL???

And, please provide a sample of the input that you currently have that is not being transformed to your liking as well as what you would like it to look like. Without such a before/after sample, we're really only guessing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 7

It is vb.net code , here.

in the vb.net

I have select statement

select EMPLNO,[Full Name],[CCNAME],[CC] from HREMP where [Full Name] like '%" & REPLACE(strS,'''','''''') & "%' and [CC] like '01032%' and (EMPSTATUS <> 90 or EMPSTATUS is null) order by [Full NAME];

if end user key in the text box O'Connell.

I am expecting all the records with O'Connell come out

'fill the data with the same last name

Dim NameComm As New SqlCommand()

NameComm.Connection = myConn

myConn.Open()

Dim strComm As String

'it may need to modify which only have cc with 03

Dim strS As String

strS = txtName.Text.Trim.ToUpper

strS = Replace(strS, "'", "''")

strComm = "select EMPLNO,[Full Name],[CCNAME],[CC] from HREMP where [Full Name] like '%" & strS & "%' and [CC] like '01032%' and (EMPSTATUS <> 90 or EMPSTATUS is null) order by [Full NAME];"

NameComm.CommandText = strComm

NameComm.CommandType = CommandType.Text

Dim NameReader As SqlDataReader

Try

NameReader = NameComm.ExecuteReader

Catch ex As Exception

Response.Write(ex.ToString)

End Try

lstName.Items.Clear()

Dim strName As String ' the name show in the list box

Try

While NameReader.Read

strName = NameReader("Full Name") & ControlChars.Tab & NameReader("CCNAME") & ControlChars.Tab & NameReader("CC")

lstName.Items.Add(New ListItem(strName, NameReader("EMPLNO")))

End While

If lstName.Items.Count = 0 Then

Me.lblLoginError.Visible = True

Me.lblLoginError.Text = "Check the last name you just entered"

End If

Catch ex As Exception

Response.Write(ex.ToString)

End Try

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

.NET syntax for replace is

<name of string object>.REPLACE("old value", "new value")

So you want

Dim strS As String

strS = txtName.Text.Trim.ToUpper

strS.Replace("'", "''")

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 9

Frances L

SSCarpal Tunnel

Points: 4727

November 26, 2007 at 12:10 pm

#754201

Thx. It worked fine right now.