Solved

Dynamic query and the embedded aprostophe ( ' )

Posted on 2008-06-19
12
255 Views
Last Modified: 2010-04-21
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
Comment
Question by:garyinmiami2003
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 21821577
did you've tryed like this:

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

jppinto
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 21821579
double them:
strsql = strsql + " MID_ID = ' " & txtMidID.Text.replace("'", "''") & " ' "
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 350 total points
ID: 21821592
and you have some space characters that should not be there:

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

Open in new window

0
 

Author Comment

by:garyinmiami2003
ID: 21821723
emoreau:

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

Author Comment

by:garyinmiami2003
ID: 21821763
EMOREAU:

sorry, I was wrong let me try again
0
 

Author Comment

by:garyinmiami2003
ID: 21821963
Emoreau:

This does not solve my problem.  It does the replace  but I can't change the value  
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 21822163
>>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
 
LVL 18

Expert Comment

by:philipjonathan
ID: 21822778
If you use VB.NET, can you use the SqlParameter equivalent for Sybase?
0
 

Author Comment

by:garyinmiami2003
ID: 21822819
philipjonathan
Possibly, Can you tell me a little more.  I will network this around on my end.
0
 
LVL 4

Assisted Solution

by:Thunder724
Thunder724 earned 50 total points
ID: 21825069
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
 
LVL 18

Assisted Solution

by:philipjonathan
philipjonathan earned 100 total points
ID: 21827789
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
 

Author Closing Comment

by:garyinmiami2003
ID: 31468740
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
WPF - Tooltips for ComboBox items 5 27
Recommendation vb6 to vb.net or others 14 105
Help with Query not working in client's PC 1 35
Get Client IP on RDS - VB.NET 15 15
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now