asked on

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

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
)

Open in new window

Miguel Oz

YOU have not supplied a parameter value for all your parameters:
For example lines 18-19 should be:
adapter.InsertCommand.Parameters.Add("@tripID",
SqlDbType.Int, 4, "tripID"). Value = 10; //or what ever integer is needed

Check:
http://msdn.microsoft.com/en-us/library/e5xwx8sc.aspx

tia_kamakshi

ASKER

No, If we have to do batch update then these value is picked from datatable itself
Please see
http://msdn.microsoft.com/en-us/library/kbbwt18a(VS.80).aspx

Please guide

Miguel Oz

Your example is for sql commands with sql statements not store procedures.
Any particular reason to use the store procedure because it looks that you are only doing an insert operation that can be done at C# level very easily.

ROMA CHAUHAN

If you just want to insert the bulk data then u can use the sqlBulkCopy to insert the whole table in one dump. For that u can use below fucntion.........

public void BatchUpdate(DataTable tripDetailDtble)
    {
        SqlTransaction tn = null;
        SqlConnection connection = new SqlConnection("Your connection string");
        connection.Open();
        try
        {

            tn = connection.BeginTransaction();
            SqlBulkCopy bcp = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tn);
            bcp.DestinationTableName = "trip_data";
            bcp.WriteToServer(tripDetailDtble);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            tn.Commit();
            connection.Dispose();
        }
    }

Open in new window

ROMA CHAUHAN

And if you want to go with the same code which u have done then make some changes in your code like this.........

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("insertTripData", connection);
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure; 
            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(); }
        }
    }

Open in new window

ASKER CERTIFIED SOLUTION

ROMA CHAUHAN

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tia_kamakshi

ASKER

Many Thanks