locked
Vis Studio param query to FoxPro trouble RRS feed

  • 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

    Thursday, August 25, 2011 1:40 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 28, 2011 8:40 PM

All replies

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, August 28, 2011 8:40 PM
  • 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

     

     

    Tuesday, October 11, 2011 5:55 AM