[RESOLVED] sql query in vb HELP

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

so you've already tried the below?

VB Code:

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

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

what error does it give?

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

so you just need:

VB Code:

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

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

Place

VB Code:

Debug.Print psql

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

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

Quote:

---

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.:

VB Code:

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

outputs:

VB Code:

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:

VB Code:

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

the output is:

VB Code:

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?

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