[RESOLVED] sql query in vb HELP

[kamran]

If an sql query has ' then how to avoid is..I know v can use the replace function n i have tried but it still gives me an error...does anybody have any function that returns a string..that formats a query..thanx

[bushmobile]

so you've already tried the below?

sql = Replace$(sql, "'", "''")

[kamran]

yes i have tried it but still error..
my query is

psql = "select PriceCode from InsuranceTypes where Name='" & value & "'"

and value =Railway & Transport Employees' Friendly Society He

if i put that an call the function it still find a problem

[bushmobile]

what error does it give?

[kamran]

the error is the i replaces " with '..if i format the value only then its fine

[bushmobile]

so you just need:

psql = "select PriceCode from InsuranceTypes where Name='" & Replace$(value, "'", "''") & "'"

[kamran]

This is not working either now..how can i solve this..its really a pain

[bushmobile]

Place

Debug.Print psql

after you contruct the query and check the output for errors before sending it to the database.

[kamran]

thanx mate..i solved it but its a bit strange..why can't v replace all the string but instead v have to do it with the value parameter

[bushmobile]

why can't v replace all the string but instead v have to do it with the value parameter

All the text has to be enclosed within single quotes ('text goes here'), e.g.:

psql = Replace("select PriceCode from InsuranceTypes where Name='" & value & "'", "'", "''")

outputs:

select PriceCode from InsuranceTypes where Name= '' Railway & Transport Employees '' Friendly Society He ''

and none of the text is actually between the single quotes - so it errors:


However, with:

psql = "select PriceCode from InsuranceTypes where Name='" & Replace$(value, "'", "''") & "'"

the output is:

select PriceCode from InsuranceTypes where Name= 'Railway & Transport Employees' 'Friendly Society He'

and all the text is enclosed in single quotes - and it is processed correctly.

(I've edited the output strings so the quote pairs are made more obvious, it's not the true output.)

Is that what you were asking?

[kamran]

I have to try one of ur first options the way ur doing it..wat i was doing is that constructing a string and passing it to a function that formats it..i'll c it n if i have any problems..i'l ask u..thanx for ur help