Trouble with OleDbParameters

**seraieis** · Dec 17 '10, 05:24 PM

I'm running into an error that doesn't make sense to me when converting an OleDbCommand to use OleDbParameter' s. I'm using a VB.NET front end, connected to an Access database (will soon be migrated to SQL Server).

When I run this bit of code, the row is inserted correctly (notice the My.User.Name in the select statement):

Code:

cmd = New OleDbCommand("INSERT INTO ACCOUNT_H SELECT ACCOUNT.*, NOW() AS COPIED_DT, '" & My.User.Name & "' AS COPIED_USER FROM ACCOUNT WHERE ACCOUNT.ACCT_NUM=@AcctNum", cn)
cmd.Parameters.Add(New OleDbParameter("@AcctNum", OleDbType.VarChar)).Value = acctnum

However, when I modify the COPIED_USER bit to use a paramter:

Code:

cmd = New OleDbCommand("INSERT INTO ACCOUNT_H SELECT ACCOUNT.*, NOW() AS COPIED_DT, @UserNm AS COPIED_USER FROM ACCOUNT WHERE ACCOUNT.ACCT_NUM=@AcctNum", cn)
cmd.Parameters.Add(New OleDbParameter("@AcctNum", OleDbType.VarChar)).Value = acctnum
cmd.Parameters.Add(New OleDbParameter("@UserNm", OleDbType.VarChar)).Value = My.User.Name

I get the following error:

Code:

System.Data.OleDb.OleDbException: Duplicate output destination 'ACCT_NUM'.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at ASLDB.ASLDB.SaveNewRecord() in C:\Documents and Settings\C15919\My Documents\Visual Studio 2005\ASLDB\ASLDB\ASLDB.vb:line 1642

I've taken the query and put it directly into Access, and it works correctly. I'm not sure why changing the query to use a parameter would give that error, especially when in another query, I can use 'SELECT @UserNm as LAST_USER' without issue.

Any thoughts?

The table structure is:
ACCOUNT:
ACCT_NUM
ACCT_NM
CLIENT_ID
STATUS_CD
EFF_DT
REGION
REPORTING_TY
LAST_TIMESTAMP
LAST_USER

ACCOUNT_H:
ACCT_NUM
ACCT_NM
CLIENT_ID
STATUS_CD
EFF_DT
REGION
REPORTING_TY
LAST_TIMESTAMP
LAST_USER
COPIED_DT
COPIED_USER