OleDbParameter inserting the same value over and over and over...

[SteveK]

I'm making a stored procedure for an Access DB and I'm adding
OleDbParameter objects to the OleDbCommand object. This all makes sense and
seems fine. However, for some reason when inserting thousands of records
using a loop, the values being inserted to the tables are the same for every
record inserted.

I have verified that I am setting the OleDbParamter.Value property correctly
for each respective insert, but still it is using a constant value. Here is
the code:
public void InsertRecords(ArrayList list)

{

string sql = string.Empty;

OleDbCommand cmd = new OleDbCommand();







OpenConn();

cmd.Connection = m_conn;





OleDbParameter param = new OleDbParameter("paramID",
OleDbType.Integer, 4, "shotID");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramName", OleDbType.VarChar, 128,
"Name");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramShotNr", OleDbType.TinyInt, 1,
"TakeNr");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramDescription", OleDbType.VarChar,
1024, "Description");

cmd.Parameters.Add(param);







// loop through and insert each one creating dependent records as
needed...

foreach(object o in list)

{

ShotRecord shot = (ShotRecord)o;




cmd.Parameters[0].Value = shot.ID; // ID

cmd.Parameters[1].Value = shot.takeName; // Name

cmd.Parameters[2].Value = shot.takeNum; // Take Nr

cmd.Parameters[3].Value = shot.description; //
Description





sql = "EXECUTE sp_InsertTest";

cmd.CommandText = sql;



try

{

cmd.ExecuteNonQuery();



}

catch(OleDbException e)

{

m_ui.ShowMessage(string.Format("DAL ERROR
OCCURED!\n\nException Text:\n{0}", e.Message), "DAL ERROR");

}

}


CloseConn();

}





What have I done wrong? This is very odd behavior, has anyone else seen
this??

Thanks,
Steve

 

[Miha Markic [MVP C#]]

Hi,

Did you try setting CommandText = "sp_InsertTest" and CommandType =
CommandType.StoredProcedure?

 

[SteveK]

And here is the SQL from the sproc:
string sql = "CREATE PROC sp_InsertTest( ";
sql += "in_ID INTEGER, ";
sql += "in_Name VARCHAR(128), ";
sql += "in_TakeNr BYTE, ";
sql += "in_Description VARCHAR(1024)";
sql += " )";

sql += " AS INSERT INTO test ";

sql += "(shotId, ";
sql += "Name, ";
sql += "TakeNr, ";
sql += "Description";
sql += " )";

sql += " VALUES( ";
sql += "in_ID, ";
sql += "in_Name, ";
sql += "in_TakeNr, ";
sql += "in_Description";
sql += " )";

 

[SteveK]

Hi-

If I add CommandType.StoredProcedure I get an exception:
"Expected query name after EXECUTE"


odd. Confused.
Thanks for the suggestion.

Hi,

Did you try setting CommandText = "sp_InsertTest" and CommandType =
CommandType.StoredProcedure?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I'm making a stored procedure for an Access DB and I'm adding
OleDbParameter objects to the OleDbCommand object. This all makes sense
and
seems fine. However, for some reason when inserting thousands of records
using a loop, the values being inserted to the tables are the same for
every
record inserted.

I have verified that I am setting the OleDbParamter.Value property
correctly
for each respective insert, but still it is using a constant value. Here
is
the code:
public void InsertRecords(ArrayList list)

{

string sql = string.Empty;

OleDbCommand cmd = new OleDbCommand();







OpenConn();

cmd.Connection = m_conn;





OleDbParameter param = new OleDbParameter("paramID",
OleDbType.Integer, 4, "shotID");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramName", OleDbType.VarChar, 128,
"Name");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramShotNr", OleDbType.TinyInt, 1,
"TakeNr");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramDescription", OleDbType.VarChar,
1024, "Description");

cmd.Parameters.Add(param);







// loop through and insert each one creating dependent records as
needed...

foreach(object o in list)

{

ShotRecord shot = (ShotRecord)o;




cmd.Parameters[0].Value = shot.ID; // ID

cmd.Parameters[1].Value = shot.takeName; // Name

cmd.Parameters[2].Value = shot.takeNum; // Take
Nr

cmd.Parameters[3].Value = shot.description; //
Description





sql = "EXECUTE sp_InsertTest";

cmd.CommandText = sql;



try

{

cmd.ExecuteNonQuery();



}

catch(OleDbException e)

{

m_ui.ShowMessage(string.Format("DAL ERROR
OCCURED!\n\nException Text:\n{0}", e.Message), "DAL ERROR");

}

}


CloseConn();

}





What have I done wrong? This is very odd behavior, has anyone else seen
this??

Thanks,
Steve

 

[Miha Markic [MVP C#]]

Did you changed CommandText also?
Btw, try using server explorer to create the command object (perhaps in a
test project) - once you define the database in server explorer, drag & drop
stored procedure on the form.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi-

If I add CommandType.StoredProcedure I get an exception:
"Expected query name after EXECUTE"


odd. Confused.
Thanks for the suggestion.

Hi,

Did you try setting CommandText = "sp_InsertTest" and CommandType =
CommandType.StoredProcedure?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I'm making a stored procedure for an Access DB and I'm adding
OleDbParameter objects to the OleDbCommand object. This all makes
sense
and
seems fine. However, for some reason when inserting thousands of records
using a loop, the values being inserted to the tables are the same for
every
record inserted.

I have verified that I am setting the OleDbParamter.Value property
correctly
for each respective insert, but still it is using a constant value. Here
is
the code:
public void InsertRecords(ArrayList list)

{

string sql = string.Empty;

OleDbCommand cmd = new OleDbCommand();







OpenConn();

cmd.Connection = m_conn;





OleDbParameter param = new OleDbParameter("paramID",
OleDbType.Integer, 4, "shotID");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramName", OleDbType.VarChar, 128,
"Name");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramShotNr", OleDbType.TinyInt, 1,
"TakeNr");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramDescription", OleDbType.VarChar,
1024, "Description");

cmd.Parameters.Add(param);







// loop through and insert each one creating dependent records as
needed...

foreach(object o in list)

{

ShotRecord shot = (ShotRecord)o;




cmd.Parameters[0].Value = shot.ID; // ID

cmd.Parameters[1].Value = shot.takeName; // Name

cmd.Parameters[2].Value = shot.takeNum; //
Take
Nr

cmd.Parameters[3].Value = shot.description; //
Description





sql = "EXECUTE sp_InsertTest";

cmd.CommandText = sql;



try

{

cmd.ExecuteNonQuery();



}

catch(OleDbException e)

{

m_ui.ShowMessage(string.Format("DAL ERROR
OCCURED!\n\nException Text:\n{0}", e.Message), "DAL ERROR");

}

}


CloseConn();

}





What have I done wrong? This is very odd behavior, has anyone else
seen
this??

Thanks,
Steve

 

[SteveK]

My mistake, no. But I just tried it with the edited CommandText and the
error stops, but the results are the same with the duplicate reocrds.

Did you changed CommandText also?
Btw, try using server explorer to create the command object (perhaps in a
test project) - once you define the database in server explorer, drag & drop
stored procedure on the form.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi-

If I add CommandType.StoredProcedure I get an exception:
"Expected query name after EXECUTE"


odd. Confused.
Thanks for the suggestion.

Hi,

Did you try setting CommandText = "sp_InsertTest" and CommandType =
CommandType.StoredProcedure?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

I'm making a stored procedure for an Access DB and I'm adding
OleDbParameter objects to the OleDbCommand object. This all makes
sense
and
seems fine. However, for some reason when inserting thousands of records
using a loop, the values being inserted to the tables are the same for
every
record inserted.

I have verified that I am setting the OleDbParamter.Value property
correctly
for each respective insert, but still it is using a constant value. Here
is
the code:
public void InsertRecords(ArrayList list)

{

string sql = string.Empty;

OleDbCommand cmd = new OleDbCommand();







OpenConn();

cmd.Connection = m_conn;





OleDbParameter param = new OleDbParameter("paramID",
OleDbType.Integer, 4, "shotID");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramName", OleDbType.VarChar, 128,
"Name");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramShotNr", OleDbType.TinyInt, 1,
"TakeNr");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramDescription", OleDbType.VarChar,
1024, "Description");

cmd.Parameters.Add(param);







// loop through and insert each one creating dependent records as
needed...

foreach(object o in list)

{

ShotRecord shot = (ShotRecord)o;




cmd.Parameters[0].Value = shot.ID; // ID

cmd.Parameters[1].Value = shot.takeName; // Name

cmd.Parameters[2].Value = shot.takeNum; //
Take
Nr

cmd.Parameters[3].Value = shot.description; //
Description





sql = "EXECUTE sp_InsertTest";

cmd.CommandText = sql;



try

{

cmd.ExecuteNonQuery();



}

catch(OleDbException e)

{

m_ui.ShowMessage(string.Format("DAL ERROR
OCCURED!\n\nException Text:\n{0}", e.Message), "DAL ERROR");

}

}


CloseConn();

}





What have I done wrong? This is very odd behavior, has anyone else
seen
this??

Thanks,
Steve

 

[Joyjit Mukherjee]

Hi,

try clearing all the parameters after executing the ExecuteNonQuery method
using cmd.Parameters.Clear() & then reassign a new set.

HTH
Regards
Joyjit

 

[SteveK]

Joyjit,

Thank you, that worked! It's unfortunate that I need to incur the
additional overhead for this workaround, it seems like a bug to me.
But... it works now, thank you!


-SK

Hi,

try clearing all the parameters after executing the ExecuteNonQuery method
using cmd.Parameters.Clear() & then reassign a new set.

HTH
Regards
Joyjit

I'm making a stored procedure for an Access DB and I'm adding
OleDbParameter objects to the OleDbCommand object. This all makes sense and
seems fine. However, for some reason when inserting thousands of records
using a loop, the values being inserted to the tables are the same for every
record inserted.

I have verified that I am setting the OleDbParamter.Value property correctly
for each respective insert, but still it is using a constant value.

Here

is
the code:
public void InsertRecords(ArrayList list)

{

string sql = string.Empty;

OleDbCommand cmd = new OleDbCommand();







OpenConn();

cmd.Connection = m_conn;





OleDbParameter param = new OleDbParameter("paramID",
OleDbType.Integer, 4, "shotID");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramName", OleDbType.VarChar, 128,
"Name");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramShotNr", OleDbType.TinyInt, 1,
"TakeNr");

cmd.Parameters.Add(param);



param = new OleDbParameter("paramDescription", OleDbType.VarChar,
1024, "Description");

cmd.Parameters.Add(param);







// loop through and insert each one creating dependent records as
needed...

foreach(object o in list)

{

ShotRecord shot = (ShotRecord)o;




cmd.Parameters[0].Value = shot.ID; // ID

cmd.Parameters[1].Value = shot.takeName; // Name

cmd.Parameters[2].Value = shot.takeNum; //

Take

Nr

cmd.Parameters[3].Value = shot.description; //
Description





sql = "EXECUTE sp_InsertTest";

cmd.CommandText = sql;



try

{

cmd.ExecuteNonQuery();



}

catch(OleDbException e)

{

m_ui.ShowMessage(string.Format("DAL ERROR
OCCURED!\n\nException Text:\n{0}", e.Message), "DAL ERROR");

}

}


CloseConn();

}





What have I done wrong? This is very odd behavior, has anyone else seen
this??

Thanks,
Steve