troubleshooting Question

Unable to pass parameters in SqlDataAdapter, InsertCommand when doing batch update

Avatar of tia_kamakshi
tia_kamakshiFlag for United Arab Emirates asked on
C#
7 Comments1 Solution1110 ViewsLast Modified:
Hi,

I am working on ASP.net 3.5 using C#

When I am doing batch update,

I have passed a required parameter to my SqlDataAdapter, InsertCommand

Still I am getting the error

The parameterized query '(@tripID int,@questionID int,@answer varchar(8000))EXEC insertTr' expects the parameter '@tripID', which was not supplied.

Please see my code and SQL stored proc below and please guide me where I have done wrong

Many Thanks
public void BatchUpdate(DataTable tripDetailDtble, int tripID)
{
    int batchSize = 20;
    SqlTransaction tn = null;
    DbConnection dbConn = new DbConnection();
    SqlConnection connection = null;

    try
    {
	connection = dbConn.Connection();
	tn = connection.BeginTransaction();

	SqlDataAdapter adapter = new SqlDataAdapter();


	adapter.InsertCommand = new SqlCommand(
		"EXEC insertTripData @tripID, @questionID, @answer", connection);
	adapter.InsertCommand.Parameters.Add("@tripID",
	       SqlDbType.Int, 4, "tripID");
	adapter.InsertCommand.Parameters.Add("@questionID",
	       SqlDbType.Int, 4, "questionID");
	adapter.InsertCommand.Parameters.Add("@answer",
	       SqlDbType.VarChar, 8000, "tripdata_question_answer");
	adapter.InsertCommand.Transaction = tn;
	adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

	adapter.UpdateBatchSize = batchSize;

	adapter.Update(tripDetailDtble);
	tn.Commit();
    }
    catch (Exception ex)
    {
	throw ex;
    }
    finally
    {
	if (tn != null) { tn.Rollback(); tn.Dispose(); }
	if (connection != null) { connection.Dispose(); }
    }
}



------------------Stored Proc---------------

Create procedure insertTripData
	@tripID int,
	@questionID int,
	@answer varchar(8000)

AS

INSERT INTO trip_data
(tripdata_trip_link, tripdata_question_link, tripdata_question_answer, tripdata_is_donot_use_global_field)
VALUES
(
	@tripID, @questionID,@answer,0
)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros