How do I escape ampersands in stored procedure?

I am using the following stored procedure to gather information from one of my tables:
CREATE PROCEDURE usr_GetCustomerNumber
@.custName varchar(20)
AS
SELECT CUNO FROM CIPNAME0
WHERE CUNM LIKE @.custName
GO
The problem I am facing is that some of the customers have ampersands (&) in their names, i.e. A & M Auto Supply. When I feed in anything with an ampersand to @.custName, procedure doesn't return any information. I am using the following code to call the stored procedure:
Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
Dim custSelected As String
Dim custNum As String
Dim objDataSet As New DataSet
Dim objCmd As New SqlCommand("usr_GetCustomerNumber", objConn)
Dim objReader As SqlDataReader
Try
lblStatus.Text = ""
custSelected = lstResults.SelectedItem.Text
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@.custName", custSelected)
objConn.Open()
objReader = objCmd.ExecuteReader
While objReader.Read
custNum = objReader("CUNO")
End While
objReader.Close()
objConn.Close()
Catch ex As SqlException
lblStatus.ForeColor = Color.Red
lblStatus.Text = ex.Message
End Try
lblStatus.Text = custNum
End Sub
I am trying to determine exactly how I would escape the & and still get the desired results. Should I search the custSelected string for the & and try to escape it before it gets to the stored procedure? I have no clue.
Thanks!
Ariston Collander
ariston@.coxcomputer.comAmpersand character does not have any special meaning in LIKE pattern. Only characters %, _, [, ], ^ has special meaning. Your C# code looks find to me. The ampersand character might be getting encoded before reaching your click routine. You can verify it by either setting a break point in the C# code and looking at the value or running a SQL Profiler trace to see the executed SP call with the parameter values.