Searching for a value in an SQL table

HCL Domino Domino Forum
1

I am trying to search for a value in an SQL table, AFC_INV_ITEM.I am getting the following on viewing the code in notes

“23 does not exist in AFC_INV_ITEM” even though 23 exists in the table.

Below is the code that I have written.

Sub Click(Source As Button)

Dim con As New ODBCConnection

Dim qry As New ODBCQuery

Dim result As New ODBCResultSet

Dim barCode As Variant

Set qry.Connection = con

Set result.Query = qry

con.ConnectTo(Inputbox(“ODBC data source name”, “DSN”))

While Not con.IsConnected

dsn = Inputbox(“ODBC data source name”, _

"Connection not made ...")

If dsn = “” Then Exit Sub

con.ConnectTo(dsn)

Wend

Messagebox "Connected to " & con.DataSourceName, _

“Connection made …”

barCode = Cvar(Inputbox$(“Bar Code?”, “Query”))

qry.SQL = “SELECT * FROM AFC_INV_ITEM WHERE (bar_code = ‘barCode’)”

result.Execute

If result.IsResultSetAvailable Then

Msgbox barCode & " exists in AFC_INV_ITEM"

Else

Msgbox barCode & " does not exist in AFC_INV_ITEM"

End If

result.Close(DB_CLOSE)

con.Disconnect

2

Subject: Searching for a value in an SQL table

Change the qry.SQL line to the following and it should work:

qry.SQL = {SELECT * FROM AFC_INV_ITEM WHERE (bar_code = ‘} + barCode + {’)}

3

Subject: RE: Searching for a value in an SQL table

Thanks it worked for me.