Inserting a new row into the DB using IBM.Data.DB2.iSeries provider with parameters

[EDN Admin]

So I found some examples already in these forums, but nothing has worked so far. I am able to run a query that returns rows, but I am not able to insert anything with parameters. This is what I got so far, but its causing this error: "SQL0206 Column $USERNAME
not in specified tables."

I have the connection opening in a different function like this:
<pre class="prettyprint System.Data.IDbConnection connection = null;
connection = new iDB2Connection(ConfigurationManager.ConnectionStrings["db2connection"].ToString());

try
{
connection.Open();
}
//catch block excluded

//connection is passed to another function that runs the sql statment

insertIntoDatabase(segName[y], length[y], connection);
[/code]
<br/>

The following is the other function that runs the sql insert. variable declarations are excluded.
<pre class="prettyprint String sql = "INSERT INTO TSTDTACUR.CG1P (C1SEG, C1LEN, C1STYP, C1P1, C1P2, C1MTMM, C1MTYY, C1TMNT, C1PRTC, C1MTCT, C1MUSR) " +
"VALUES ($segmentName, $length, 0, $pictureL, $pictureR, 0, 0, 0, 0, 0, $userName)";
try
{
IDbCommand command = connection.CreateCommand();

IDbDataParameter name = command.CreateParameter();
IDbDataParameter segmentLength = command.CreateParameter();
IDbDataParameter picL = command.CreateParameter();
IDbDataParameter picR = command.CreateParameter();
IDbDataParameter userName = command.CreateParameter();

name.DbType = DbType.String;
name.ParameterName = "segmentName";
name.Value = segName;

segmentLength.DbType = DbType.String;
segmentLength.ParameterName = "length";
segmentLength.Value = length;

picL.DbType = DbType.String;
picL.ParameterName = "pictureL";
picL.Value = pictureL;

picR.DbType = DbType.String;
picR.ParameterName = "pictureR";
picR.Value = pictureR;

userName.DbType = DbType.String;
userName.ParameterName = "userName";
userName.Value = tb_userName.Text;

command.Parameters.Add(name);
command.Parameters.Add(segmentLength);
command.Parameters.Add(picL);
command.Parameters.Add(picR);
command.Parameters.Add(userName);
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.ExecuteNonQuery();[/code]
<br/>
<br/>
When it tries to ExecuteNonQuery, thats when it fails giving this error: "SQL0206 Column $USERNAME not in specified tables."
I dont know why its saying that the column is not in the specified table. For some reason, its thinking that the parameter name is a column name. If I change the parameter name to whatever, I get the same error with the new parameter name.

What am i doing wrong?

View the full article