How to find a specific Character in a text box 1

[hpsingh]

I am using VB with SQL Server database in my project.

There is a text box on a Form in which the user either types or copies text which is around 5 to 10 lines. I am facing a problem that if the user enters or copies the character {'} (e.g. Government's ) and when tries to save it is not accepted by the SQL database table.

Anyone who can provide a solution is welcome. Kindly send the solution in detail.

 

[sunaj]

Hi,

The best solution is to replace the {'} with either {"} or {''}:

MyConn.Execute "INSERT INTO MYtable(TheText) VALUES (" & replace(Text1.text,"'","''&quot & &quot"

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[abdullauthan]

In the KeyPress event of the Text, include the following code.

If KeyAscii=39 Then KeyAscii=0

This will prevent the TextBox from accepting the ' character.

 

[hpsingh]

Dear Sunaj

I have tried your suggestion today but was not sucessful.
I have given the following code which gives compile error as "Expected :lisat separator or }"

"insert into pquestion values('" & UCase(Trim(Text2.Text)) & "', '" & Format(DTPicker1.Value, "mm/dd/yyyy&quot & "','" & Trim(Combo4.Text) & "','" & Trim(Combo3.Text) & "','" & Trim(Combo2.Text) & "','" & Trim(Combo1.Text) & "', '" & val & "','" & replace(Text3.Text,"'",""&quot & "','" & val2 & "','" & Trim(Text4.Text) & "','" & status & "')"

This code is not working. Please advise.

 

[sunaj]

Hi,

It a little difficult for me to spot problem, because your code is taken out of a context (e.g. what if there is a single quote (') in text2.text).
Anyway, I can see 2 problems:

You are using the reserved word 'val' as a variable name. Change the name of the variable.

The replace function shall replace single quotes with 2 single quotes (not a double quote): replace(Text3.Text,"'","''&quot
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[hpsingh]

Dear Sunaj,
Very much thanks for the valuable suggestions. I have replaced the single quote with two single quote and this has done my job but I was surprised when I opened the concerned table to see the column value as Government's (I thought it must have been saved as Government''s).
Kindly Clear my doubt.

 

[sunaj]

Hi,

The replacement of single quotes with 2 single quotes is simply a way to tell the provider that it shall insert a single quote in the string (which is defined as the text between the signle quotes.

The same is true in VB with double quotes, to put a double quote into a string, replace it with 2 double quotes:
---------------------------------------------------
msgbox "Here "" is a double quote in a VB string"
---------------------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'