[RESOLVED] Replace Quotation

HI! I have succesfully used the code below to replaced quotation mark ( ' ) however, if i use the same code in my search textbox it gives me error. How do i let my search box accept quotation mark?

VB Code:

Function doQuery(ByVal sCriteria As String, ByVal sfieldName As String) As String
  doQuery = "SELECT Code, ProductDescription, SellingPrice,PurchasePrice,Quantity, " & _
            "Unit,EntryDate, ReOrder FROM tblStocks " & _
              "WHERE " & sfieldName & " LIKE '" & sCriteria & "%'"
End Function
 
Private Sub txtSearch_Change()
  Dim rsStocks1 As New ADODB.Recordset
  sSQL = doQuery(txtSearch.Text, "ProductDescription")
  rsStocks1.Open sSQL, oConn, adOpenForwardOnly, adLockOptimistic
  
  If rsStocks1.EOF = False Then
    Call FillListView(lstStocks, rsStocks1)
  End If
End Sub

VB Code:

LIKE '" & replace(sCriteria, "'", "''")

Quote:

---

Originally Posted by whythetorment

VB Code:

LIKE '" & replace(sCriteria, "'", "''")

---

Thanks for the reply. But as i've said i have successfully used replace quotation. I need to implement the same when the user type for example (Hershey's) the app should accept it.

here's my replace quotation code.

VB Code:

Public Sub ReplaceQuotation(ByRef txt1 As TextBox)
  txt1.Text = Replace(txt1.Text, "'", "''", , , vbTextCompare)
End Sub

The best way to handle these special characters is to use Parametrized Queries. These queries are faster and safer compared to the T-SQL. Take a look at this thread.
http://www.vbforums.com/showthread.php?t=402848

And have you written any code in your textbox keypress event that will disable apostrophes??

Just hit the replace in the doQuery, but as Shuji mentioned.. I would change it to a Param query...

VB Code:

Function doQuery(ByVal sCriteria As String, ByVal sfieldName As String) As String
    If InStr(sCriteria, "'") <> 0 Then
        sCriteria = Replace(sCriteria, "'", "''")
    End If
    doQuery = "SELECT Code, ProductDescription, SellingPrice,PurchasePrice,Quantity, " & _
            "Unit,EntryDate, ReOrder FROM tblStocks " & _
              "WHERE " & sfieldName & " LIKE '" & sCriteria & "%'"
End Function

thanks to both of you guys. I'll have to take a look the link you gave shuja Ali

Quote:

---

Originally Posted by Shuja Ali

The best way to handle these special characters is to use Parametrized Queries. These queries are faster and safer compared to the T-SQL. Take a look at this thread.
http://www.vbforums.com/showthread.php?t=402848

And have you written any code in your textbox keypress event that will disable apostrophes??

---

I followed the link you gave. Im sorry but i really can't get how do i implement Parameterized Queries and where in the link is it?

Quote:

---

Originally Posted by Simply Me

I followed the link you gave. Im sorry but i really can't get how do i implement Parameterized Queries and where in the link is it?

---

It is in this post.

What exactly are you not able to understand?

The title says How do i Validate Textbox Preventing Single ' & Double " but i don't want to prevent it.

If im missing your point however, please kindly show me how do i implement it with the code i have currently.

im sorry, I have re-read the title. I thought it was about preventing it, my mistake.

Anyway could you show me how to implement your code please?

See instead of directly assigning the value in the SQL Query you create a parameter and then assign the value to that parameter.

It would be better if you give it a try yourself and then post whatever problemyou face.

Thanks Shuja Ali. I just followed the code of static.