Testing for presence of table row

I am currently using the following code to check for the presence of an entry in a table. I choose this code because I thought it was a good idea to use a public function to reduce the amount of code required. It works fine until the field I am checking for contains a ' character. I realise I need to use a parameter like @name but cannot work out how to pass the correct information to the public function. Can someone show me how to amend my existing code so I can use @staffNo.


Imports System.Data.SqlClient


Dim PaddedStaffNo As String
PaddedStaffNo = (TextBox2.Text.PadLeft(4, "0"c))


Private Sub ContinueButton_Click(sender As System.Object, e As System.EventArgs) Handles ContinueButton.Click
' Check for valid open database connection before query database
If _SqlConnection IsNot Nothing AndAlso _SqlConnection.State = ConnectionState.Open Then
If RecordExists(_SqlConnection, "SELECT StaffNo FROM tb_Users WHERE StaffNo = '" & PaddedStaffNo & "'") Then
' record found in DB, lets do record found task
Label5.Text = "Staff No. already being used"
End If
' close database connection
_SqlConnection.Close()
End If




Module ModuleUserDetails

Public PubUsername As String
Public PubPriv As String
Public PubStaffNo As String
Public PubEmptyPassword As Boolean
Public Function RecordExists(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As Boolean
Dim _SqlDataReader As System.Data.SqlClient.SqlDataReader = Nothing
Try
' Pass the connection to a command object
Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection)
' get query results.
_SqlDataReader = _SqlCommand.ExecuteReader()
Catch _Exception As Exception
' Error occurred while trying to execute reader
' send error message to console (change below line to customize error handling)
MsgBox(_Exception.Message)
Return False
End Try
If _SqlDataReader IsNot Nothing AndAlso _SqlDataReader.Read() Then
' close sql reader before exit
If _SqlDataReader IsNot Nothing Then
_SqlDataReader.Close()
_SqlDataReader.Dispose()
End If
' record found
Return True
Else
' close sql reader before exit
If _SqlDataReader IsNot Nothing Then
_SqlDataReader.Close()
_SqlDataReader.Dispose()
End If
' record not found
Return False
End If
End Function
End Module

You need to escape the apostrophe as shown below.

Code:

---

Dim FindValue As String = "O'Brien"
Dim SelectStatment As String = _
<SQL>
  SELECT
      FirstName,
      LastName
  FROM
      Customers
  WHERE
      LastName = '<&#37;= FindValue.Replace("'", "''") %>'
</SQL>.Value
Console.WriteLine(SelectStatment)

---

Code:

---

SELECT FirstName, LastName FROM Customers WHERE LastName = 'O''Brien'

---