Results 1 to 2 of 2

Thread: Testing for presence of table row

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2011
    Location
    Buckingham, England
    Posts
    197

    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

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,533

    Re: Testing for presence of table row

    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'

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width