I have a visual basic program in visual studio 2010 where the user can type in a portion of customers name and get a list of customers who have similiar names and then the user selects the specific customer. The search is performed within iSeries version 7. The customer name is stored in the iSeries as following: SMITH, JOHN Q.
If the user enters SMITH, it works, if the user enters just SMITH without the comma i receive the SQL0802 error. I want the user to be able to widen the search by being able to just enter the first few letters of last name but the comma stored in iSeries is not being handled properly. In the following code, i assume that the "thisCommand.Parameters("@uname").Value = txtName.Text & "%"" is where the problem is, i just do not know how to escape the comma properly. Any suggestions? The entire code is below:
Private Sub txtName_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtName.Leave
If txtName.Text "" Then
If txtName.Text name1 Then
Dim thisConnection As New iDB2Connection("DataSource=sub; Naming=System; LibraryList=QS36F, *USRLIBL")
Dim thisCommand As New iDB2Command("SELECT ""UBA.MF""., ""UBA.VR""., FUELADJ.* FROM ""UBA.MF"" INNER JOIN ""UBA.VR"" ON ""UBA.MF"".UACCT = ""UBA.VR"".UACCT LEFT OUTER JOIN FUELADJ ON ""UBA.MF"".BILLDT = FUELADJ.BILLDT WHERE (UNAME LIKE @uname)", thisConnection)
Dim thisReader As iDB2DataReader
thisCommand.Parameters("@uname").Value = txtName.Text & "%"
thisReader = thisCommand.ExecuteReader()
Dim dt As New DataTable("UBA.MF")
NameSearch.dgv2.DataSource = dt
Catch ex As Exception
NameSearch.TopMost = True
HistoryToolStripMenuItem.Visible = True