Vis Studio param query to FoxPro trouble

Question

User1804063303 posted

Hello,

I'm querying a fox table with Visual Studio 2010 using VB.  I have a couple of query errors I need help with.

1. This doesn't return any records.

strSQLSelect.CommandText = ("SELECT service, userid, password, account, security_code AS code, notes, rec_id FROM passwords " & _
                                                "WHERE UPPER(userid) LIKE '%" & @StrFind & "%' ")
           
strSQLSelect.Parameters.AddWithValue("@StrFind","ABC123")

Note: It works when I change the WHERE clause to a literal:
WHERE UPPER(userid) LIKE '%ABC123%' "

Also, I don't receive the error below.

2. I get error 'Operation is invalid for a Memo, Blob, General or Picture field.' on the  i = oAdapt.Fill(ds1, "ThisPass") VB code when using
this:

Note: the notes field is a MEMO field.

strSQLSelect.CommandText = ("SELECT DISTINCT service, userid, password, account, security_code AS code, notes, rec_id FROM passwords " & _
    "WHERE UPPER(service) LIKE '%@StrFind%' OR " & _
    "UPPER(USERID) LIKE '%@StrFind%' OR " & _
    "UPPER(PASSWORD) LIKE '%@StrFind%' OR " & _
    "UPPER(account) LIKE '%@StrFind%' OR " & _
    "UPPER(security_code) LIKE '%@StrFind%' Or " & _
    "UPPER(notes) LIKE '%@StrFind%' ")

strSQLSelect.Parameters.AddWithValue("@StrFind", "ABC123")

Same error when I use '%ABC123%' in place of '%@StrFind%'

Oddly, I don't get this error when I when I use

"SELECT service, userid, password, account, security_code AS code, notes,rec_id FROM passwords "

          
Hope someone can help. I've been retired for 4 years now and WOW things do change!

Thanks,
Marc Miller

Answer 1

User269602965 posted

Intuitively

strSQLSelect.Parameters.AddWithValue("@StrFind", "ABC123")

should be

strSQLSelect.Parameters.AddWithValue("StrFind", "ABC123")

where "StrFind" is the NAME of the BIND VARIABLE @StrFind in the SQL statement.

+++++++++

I think for the multiparameter statement, you will need to add a unique pararmeter name for each bind variable in the SQL eventhough you are feeding each parameter the same filter variable value.

"StrFind1"

"StrFind2"

for

WHERE X = @StrFind1 OR Y = @StrFind2

+++++++++

Time to get out of FoxPro and try MSSQL Express or Oracle 11g Express for small office .NET applications or intranet websites.

Welcome back to work.

Answer 2

User1804063303 posted

Thank you, belatedly, for your answer!  I have been indisposed for a while.

I would use SQL Server or Oracle as I have extensive experience in both,SQL Server being my preference.  But this is an old Foxpro app that I wrote for friends and family who each have their own populated Fox dbs.  I wanted to add some new functionality that .NET provides and, after 4 years of retirement, it's a good project to re-hone my skills.

Thanks so much again,

Marc Miller