Single Quotes Embedded in Data

24 May 2005 1:32 PM

Tom Glasser

I have a VB program that builds an SQL SELECT statement and stores it in a
variable
as follows:

strSQL = "SELECT * FROM MyTable WHERE [CUST_ID] = '" & strCustID & "' AND
[SHIP_TO_ID] = '" & strShipToID & "'"

The problem I am having is that certain values for strShipToID contain
embedded single quotes. (Ex: REC'D) This confuses the parsing of the
SELECT statement! Is there any way to handle this kind of scenario??

Thanks,
Tom

24 May 2005 2:08 PM

Bob Butler

"Tom Glasser" <TomGlas***@discussions.microsoft.com> wrote in message
news:B51E62D7-9443-4C21-887C-F3711EE15EF0@microsoft.com
> I have a VB program that builds an SQL SELECT statement and stores it
> in a variable
> as follows:
>
> strSQL = "SELECT * FROM MyTable WHERE [CUST_ID] = '" & strCustID & "'
> AND [SHIP_TO_ID] = '" & strShipToID & "'"
>
> The problem I am having is that certain values for strShipToID contain
> embedded single quotes. (Ex: REC'D) This confuses the parsing of
> the SELECT statement! Is there any way to handle this kind of
> scenario??

replace each single quote with 2 single quotes:

AND [SHIP_TO_ID] = '" & Replace(strShipToID,"'","''") & "'"

Or use a command object with parameters

--
Reply to the group so all can participate
VB.Net: "Fool me once..."

24 May 2005 2:20 PM

Tom Glasser

Thanks, Bob. Don't know what you mean, however, by "command object
with parameters".

Tom

Show quoteHide quote

"Bob Butler" wrote:

> "Tom Glasser" <TomGlas***@discussions.microsoft.com> wrote in message
> news:B51E62D7-9443-4C21-887C-F3711EE15EF0@microsoft.com
> > I have a VB program that builds an SQL SELECT statement and stores it
> > in a variable
> > as follows:
> >
> > strSQL = "SELECT * FROM MyTable WHERE [CUST_ID] = '" & strCustID & "'
> > AND [SHIP_TO_ID] = '" & strShipToID & "'"
> >
> > The problem I am having is that certain values for strShipToID contain
> > embedded single quotes. (Ex: REC'D) This confuses the parsing of
> > the SELECT statement! Is there any way to handle this kind of
> > scenario??
>
> replace each single quote with 2 single quotes:
>
> AND [SHIP_TO_ID] = '" & Replace(strShipToID,"'","''") & "'"
>
> Or use a command object with parameters
>
> --
> Reply to the group so all can participate
> VB.Net: "Fool me once..."
>
>

24 May 2005 2:39 PM

Veign

Sample of using a command object with parameters;

'-----------------------Start Code------------------------------
Dim Cmd1 As Command
Dim Param1 As Parameter
Dim RS As Recordset

Dim strSQL as String
strSQL = "Select EmployeeID, [Date], Status From TimeTable Where EmployeeID
= ? And [Date] = ? And Status = Yes"

Set Cmd1 = New ADODB.Command
Set Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = strSQL

Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput)
Param1.Value = UD.EmployeeID
Cmd1.Parameters.Append Param1

Set Param1 = Cmd1.CreateParameter(, adDate, adParamInput)
Param1.Value = SysDate
Cmd1.Parameters.Append Param1

Set RS = Cmd1.Execute()
'---------------------------------------------------------------------

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--
Read. Decide. Sign the petition to Microsoft.
http://classicvb.org/petition/

Show quoteHide quote

"Tom Glasser" <TomGlas***@discussions.microsoft.com> wrote in message
news:8C3ADC4D-E930-4C95-898B-027E405D5378@microsoft.com...
> Thanks, Bob. Don't know what you mean, however, by "command object
> with parameters".
>
> Tom
>
> "Bob Butler" wrote:
>
> > "Tom Glasser" <TomGlas***@discussions.microsoft.com> wrote in message
> > news:B51E62D7-9443-4C21-887C-F3711EE15EF0@microsoft.com
> > > I have a VB program that builds an SQL SELECT statement and stores it
> > > in a variable
> > > as follows:
> > >
> > > strSQL = "SELECT * FROM MyTable WHERE [CUST_ID] = '" & strCustID & "'
> > > AND [SHIP_TO_ID] = '" & strShipToID & "'"
> > >
> > > The problem I am having is that certain values for strShipToID contain
> > > embedded single quotes. (Ex: REC'D) This confuses the parsing of
> > > the SELECT statement! Is there any way to handle this kind of
> > > scenario??
> >
> > replace each single quote with 2 single quotes:
> >
> > AND [SHIP_TO_ID] = '" & Replace(strShipToID,"'","''") & "'"
> >
> > Or use a command object with parameters
> >
> > --
> > Reply to the group so all can participate
> > VB.Net: "Fool me once..."
> >
> >

24 May 2005 2:46 PM

Bob Butler

"Tom Glasser" <TomGlas***@discussions.microsoft.com> wrote in message
news:8C3ADC4D-E930-4C95-898B-027E405D5378@microsoft.com
> Thanks, Bob. Don't know what you mean, however, by "command object
> with parameters".

Assuming that you are using ADO, look up the Command object. You can define
named parameters and set their values without having to fiddle with things
like the quotes.

--
Reply to the group so all can participate
VB.Net: "Fool me once..."