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

  • Thread starter Thread starter SteveK
  • Start date Start date
S

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
 
Hi,

Did you try setting CommandText = "sp_InsertTest" and CommandType =
CommandType.StoredProcedure?
 
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 += " )";
 
Hi-

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


odd. Confused.
Thanks for the suggestion.



Miha Markic said:
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

SteveK said:
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
Click to expand...
Click to expand...
 
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

SteveK said:
Hi-

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


odd. Confused.
Thanks for the suggestion.



Miha Markic said:
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

SteveK said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.



Miha Markic said:
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

SteveK said:
Hi-

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


odd. Confused.
Thanks for the suggestion.



Miha Markic said:
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
Click to expand...
Click to expand...
Click to expand...
 
Hi,

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

HTH
Regards
Joyjit
 
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


Joyjit Mukherjee said:
Hi,

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

HTH
Regards
Joyjit

SteveK said:
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.
Click to expand...
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; //
Click to expand...
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
Click to expand...
Click to expand...
 
Back
Top