[RESOLVED] Replace Quotation

[Simply Me]

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?

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

[whythetorment]

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

[Simply Me]

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.

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

[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??

[Static]

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

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

[Simply Me]

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

[Simply Me]

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?

[Shuja Ali]

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?

[Simply Me]

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.

[Simply Me]

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?

[Shuja Ali]

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.

[Simply Me]

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