[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Dynamic query and the embedded aprostophe ( ' )

I am having problems with using variables that may have an embedded aprostophe.  Because single quotes (aprostophe) are wrappers for text strings in a query, my dynamic query finds one of those and assumes the end of a text string when there is more text to follow.  I am looking for a parameter driven way of replacing the single quotes.  I do not want to use a string function replacing " ' " with " ' ' "

Changing any sybase db parms is not viable.  

Example:
strsql = strsql + " MID_ID = ' " & txtMidID.Text & " ' "

If txtMidID.Text  = "AMERICA'S" then the dynamic query finds a false end to the text string and results in a query syntax error.   Please provide a complete example with the solution
0
garyinmiami2003
Asked:
garyinmiami2003
  • 5
  • 3
  • 2
  • +2
3 Solutions
 
jppintoCommented:
did you've tryed like this:

strsql = strsql + " MID_ID = " &" ' " & txtMidID.Text & " ' "

jppinto
0
 
Éric MoreauSenior .Net ConsultantCommented:
double them:
strsql = strsql + " MID_ID = ' " & txtMidID.Text.replace("'", "''") & " ' "
0
 
Éric MoreauSenior .Net ConsultantCommented:
and you have some space characters that should not be there:

strsql = strsql + " MID_ID = '" & txtMidID.Text.replace("'", "''") & "' "

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
garyinmiami2003Author Commented:
emoreau:

IT DOES NOT SEEM TO LIKE REPLACING 1 CHARACTER WITH 2?   vs 2005 VB.NET
0
 
garyinmiami2003Author Commented:
EMOREAU:

sorry, I was wrong let me try again
0
 
garyinmiami2003Author Commented:
Emoreau:

This does not solve my problem.  It does the replace  but I can't change the value  
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>It does the replace  but I can't change the value  

The quote is not replaced into the database, it is only an escape character for the time the query is sent to SQL.

The quote is the value delimiter. If your value contains a quote, SQL thinks that the value ends there. That's why you need to double it.
0
 
philipjonathanCommented:
If you use VB.NET, can you use the SqlParameter equivalent for Sybase?
0
 
garyinmiami2003Author Commented:
philipjonathan
Possibly, Can you tell me a little more.  I will network this around on my end.
0
 
Thunder724Commented:
I had this probelm a few years ago and I solved it by using the CHR functions.  Example below:
You can use this in a stored proc or if writing dynamic SQL from the client just put quotes around everything.

SELECT
      PID,
      C.CODE + ' + CHAR(39) + ' - ' + CHAR(39) + ' C.RDESC AS [RejectCode]
FROM TABLE1
0
 
philipjonathanCommented:
I've only done this with MS SQL and MySQL, but there should be similar concept for sybase.

I'll give you an example in MySQL:
string cmdText = "SELECT * FROM table WHERE MID_ID = '" + txtMIDId.Text + "'";

Change this to:
string cmdText = "SELECT * FROM table WHERE MID_ID = ?mid_id";
MySqlCommand cmd = new MySqlCommand(cmdText, connection);
cmd.Parameters.Add("?mid_id", txtMIDId.Text);
cmd.ExecuteReader(); // or execute whatever ...

Check this out for articles on SqlParameter (for MS SQL):
http://www.codeproject.com/KB/database/NET_Data_Access.aspx
0
 
garyinmiami2003Author Commented:
The parameterized query works,  I assume CHR function would work but did not try.  I used the replace.  I learned something from each of the experts who received the points and my thanks to all of you.  Tried to award on merits of your solution combined with my needs.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now