Insert problem on the Update call using a DataAdapter

  • Thread starter Thread starter Cheesehead
  • Start date Start date
C

Cheesehead

Has anyone ever seen the following problem?

I have DataTable with 2000 rows of data that I want to insert into a
MS Access database using a DataAdapter.

When I call the Update statement it inserts 2000 rows of data, but all
2000 rows are identical to the first row (except for the auto num
primary key field).

I've checked to make sure the data table has the correct info before
calling the update and it does. If I run the update with only one row
in the DataTable 2000 times it works fine (just too slow).
 
Sure can, here you go.

//---------------------------------------------
// CODE:
//---------------------------------------------
Save()
{
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();

dataAdapter.InsertCommand = this.CreateInsertCommand();
dataAdapter.InsertCommand.Connection = new
OleDbConnection(this.ConnectionString);

dataAdapter.Update(dataTable);
}

protected OleDbCommand CreateInsertCommand()
{
OleDbCommand cmd = CreateParameters();
cmd.CommandText = "[proc_HistoryEventInsert]";

return cmd;
}

protected OleDbCommand CreateParameters()
{
OleDbParameter p;

OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.StoredProcedure;

// Add params
p = cmd.Parameters.Add(Parameters.Id);
p.SourceColumn = "Id";
p.SourceVersion = DataRowVersion.Current;

p = cmd.Parameters.Add(Parameters.EquipId);
p.SourceColumn = "EquipId";
p.SourceVersion = DataRowVersion.Current;

p = cmd.Parameters.Add(Parameters.EventId);
p.SourceColumn = "EventId";
p.SourceVersion = DataRowVersion.Current;

p = cmd.Parameters.Add(Parameters.EventDateTime);
p.SourceColumn = "EventDateTime";
p.SourceVersion = DataRowVersion.Current;

p = cmd.Parameters.Add(Parameters.EventData);
p.SourceColumn = "EventData";
p.SourceVersion = DataRowVersion.Current;

return cmd;
}

protected class Parameters
{
public static OleDbParameter Id
{
get
{return new OleDbParameter("@Id", OleDbType.Integer, 0);}
}

public static OleDbParameter EquipId
{
get
{return new OleDbParameter("@EquipId", OleDbType.Integer, 0);}
}

public static OleDbParameter EventId
{
get
{return new OleDbParameter("@EventId", OleDbType.Integer, 0);}
}

public static OleDbParameter EventDateTime
{
get
{return new OleDbParameter("@EventDateTime", OleDbType.Date, 0);}
}

public static OleDbParameter EventData
{
get
{return new OleDbParameter("@EventData", OleDbType.Integer, 0);}
}
}


//---------------------------------------------
// ACCESS 2003 Stored Procedure:
//---------------------------------------------

PARAMETERS [_Id] Long, [_EquipId] Long, [_EventId] Long,
[_EventDateTime] DateTime, [_EventData] Long;
INSERT INTO HistoryEvent ( EquipId, EventId, EventDateTime, EventData
)
SELECT [_EquipId] AS Expr1, [_EventId] AS Expr2, [_EventDateTime] AS
Expr3, [_EventData] AS Expr4;





Miha Markic said:
Can you show us some code?

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

Cheesehead said:
Has anyone ever seen the following problem?

I have DataTable with 2000 rows of data that I want to insert into a
MS Access database using a DataAdapter.

When I call the Update statement it inserts 2000 rows of data, but all
2000 rows are identical to the first row (except for the auto num
primary key field).

I've checked to make sure the data table has the correct info before
calling the update and it does. If I run the update with only one row
in the DataTable 2000 times it works fine (just too slow).
Click to expand...
Click to expand...
 
Back
Top