Why do I keep getting "Must declare the Scalar variable "@Param1"" when performing a parameterized query in C++ using ADO?

Question

Here is the code.
A Few notes about it. DStr is an string class that is internal to our company. It functions much like CString. I can connect to the database and run non-parameterized queries. Also this insert works fine if I do not use parameters. The cmd->Execute statment throws an exception. This is where I am getting the error msg from.

    void CEmailArchiveDatabase::TestAddUser( DStr username, DStr displayname )
{

    DStr sql = "INSERT INTO [User] (UserName, DisplayName) VALUES (@Param1, @Param2)";

    _variant_t vUser;
    _variant_t vDisp; 

    vUser.vt = VT_BSTR;
    vUser.bstrVal = username.AllocSysString();
    vDisp.vt = VT_BSTR;
    vDisp.bstrVal = displayname.AllocSysString();

    _CommandPtr cmd = NULL;
    _ConnectionPtr conn = NULL;
    _ParameterPtr prm = NULL;

    DStr connStr = "driver={SQL Server};server=DEMETER\\SQLEXPRESS;database=ExtractoMundo";

    try
    {
     conn.CreateInstance(__uuidof(Connection));
     cmd.CreateInstance(__uuidof(Command));

     if( !(conn->GetState() & adStateOpen) )
     {
      conn->ConnectionString = connStr.AllocSysString();
      conn->Open("", "", "", NULL);
      conn->CursorLocation = adUseClient;
     } 

     cmd->ActiveConnection = conn;
     cmd->CommandText = sql.AllocSysString();
     cmd->CommandType = adCmdText;

     prm = cmd->CreateParameter("@Param1", adLongVarChar, adParamInput, -1, vUser);
     cmd->Parameters->Append(prm);
     prm = cmd->CreateParameter("@Param2", adLongVarChar, adParamInput, -1, vDisp);
     cmd->Parameters->Append(prm);

     _variant_t recAff;

     cmd->Execute(&recAff, NULL, adExecuteNoRecords);

    }
    catch(_com_error &ex)
    {
     //ClearParameters();
     DStr err;
     err += PrintProviderError(conn);
     err += PrintComError(ex);
    }

    SysFreeString(vUser.bstrVal);
    SysFreeString(vDisp.bstrVal);
    SysFreeString(cmd->CommandText);
    SysFreeString(conn->ConnectionString);

}

Answer 1

Perhaps cmd->CreateParameter(_bstr_t("Param1"), ...) ?

The "@" is a SQL Server specific operator, it may be messing with you.

Answer 2

Did you try using adCmdStoredProc instead of adCmdText?

Answer 3

ADO refuses to work with named parameters in dynamic queries. You have to either convert named parameters into parameter placeholders:

DStr sql = "INSERT INTO [User] (UserName, DisplayName) VALUES (?, ?)";

or use a stored procedure instead. Create the procedure:

CREATE PROCEDURE spUserInsert
  @Param1 nvarchar(50), 
  @Param2 nvarchar(50) 
AS 
  SET NOCOUNT ON;
  INSERT INTO [User] (UserName, DisplayName) VALUES (@Param1, @Param2)
GO

and modify your code to call it:

DStr sql = "spUserInsert";
...
cmd->CommandType = adCmdStoredProcedure;