Results 1 to 8 of 8

Thread: Runtime Error

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2010
    Posts
    87

    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

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Aug 2010
    Posts
    87

    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,

    Quote Originally Posted by DataMiser View Post
    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.

  4. #4
    gibra
    Guest

    Re: Runtime Error

    Quote Originally Posted by gunapriyan View Post
    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.

  5. #5
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    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!

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Aug 2010
    Posts
    87

    Re: Runtime Error

    Thanks for your reply.

    I had used Replace function, but i had faced the error.

    Please find the error in attachment.

    Name:  Error.jpg
Views: 103
Size:  32.7 KB

    Quote Originally Posted by gibra View Post
    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.

  7. #7
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Runtime Error

    Replace(Combo3.text,"'", "''") works using the VB 6.0 method. Still works . .Net.
    Please remember next time...elections matter!

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Aug 2010
    Posts
    87

    Re: Runtime Error

    Thank you very much.

    Quote Originally Posted by TysonLPrice View Post
    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
  •  



Click Here to Expand Forum to Full Width