-
Apr 27th, 2015, 12:51 AM
#1
Thread Starter
Lively Member
Runtime Error
Friends,
I had used the following code;
SQL = "SHAPE {select distinct(Company_Name) from exporter_returns where company_name='" & Combo3.Text & "' and returns_month between '" & stretmon & "' and '" & stretmon1 & "' and quantity!=0} AS Command1 APPEND ({select company_name,STUFF(SUBSTRING(CONVERT(varchar(25), returns_month, 113),4 ,8),4, 1, '-') monyear,type_name,sum(Quantity) Quantity,sum(Fob) Fob,convert(numeric(18,2),sum(Fob)/sum(Quantity)) Unitprice from Exporter_Returns where company_name='" & Combo3.Text & "' and returns_month between '" & stretmon & "' and '" & stretmon1 & "' and quantity!=0 group by company_name,returns_month,type_name order by returns_month} AS Command2 RELATE Company_Name TO Company_Name) AS Command2"
The Company name contains like Nathmull's, Premier's
So the ' symbol is placed in some companies. So i had faced error while executing the above query.
Please help me to fix this issue.
Thanks & Regards,
Guna
-
Apr 27th, 2015, 01:38 AM
#2
Re: Runtime Error
The best solution is to use parametrized queries but if you must use a string like that then you have to double up the 's when they appear
so Premier's
Would need to be Premier''s
You could write a function that you could call to double up the quotes where needed.
-
Apr 27th, 2015, 02:38 AM
#3
Thread Starter
Lively Member
Re: Runtime Error
Friend, Thanks for your reply. But i wish to inform you that, I don't know which company have that quotes (since 6000 companies there).
So How to fix in that query....
Thanks & Regards,
Originally Posted by DataMiser
The best solution is to use parametrized queries but if you must use a string like that then you have to double up the 's when they appear
so Premier's
Would need to be Premier''s
You could write a function that you could call to double up the quotes where needed.
-
Apr 27th, 2015, 02:52 AM
#4
Re: Runtime Error
Originally Posted by gunapriyan
Friend, Thanks for your reply. But i wish to inform you that, I don't know which company have that quotes (since 6000 companies there).
Do not need to know it, you'll just use the Replace() function.
See the help file about it.
But, the best way is: use a Command with Parameters.
-
Apr 27th, 2015, 05:32 AM
#5
Re: Runtime Error
Here is a link with the replace option being used.
http://www.vbforums.com/showthread.p...nto-SQL-Server
Parameter driven is the way to go. Here is a link from a member here explaining why with code examples of both ways:
http://jmcilhinney.blogspot.com/2009...in-adonet.html
Please remember next time...elections matter!
-
Apr 27th, 2015, 05:33 AM
#6
Thread Starter
Lively Member
Re: Runtime Error
Thanks for your reply.
I had used Replace function, but i had faced the error.
Please find the error in attachment.
Originally Posted by gibra
Do not need to know it, you'll just use the Replace() function.
See the help file about it.
But, the best way is: use a Command with Parameters.
-
Apr 27th, 2015, 05:39 AM
#7
Re: Runtime Error
Replace(Combo3.text,"'", "''") works using the VB 6.0 method. Still works . .Net.
Please remember next time...elections matter!
-
Apr 27th, 2015, 07:55 AM
#8
Thread Starter
Lively Member
Re: Runtime Error
Thank you very much.
Originally Posted by TysonLPrice
Replace(Combo3.text,"'", "''") works using the VB 6.0 method. Still works . .Net.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|