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

tia_kamakshiAsked:
Who is Participating?
 
ROMA CHAUHANProject LeadCommented:
The changes i have made in your code are......
1) in sqlCommand just pass the procedure name, no need to specify exec and the parameters name in it. see line number 13

2) specify the command type as StoredProcedure, see line number 14

3) Important thing to note down here is make sure you are using the same name in parameters as the column name in the passed datatable.
In datatable if the column name is "tripdata_trip_link" then in parameters you have to write like
adapter.InsertCommand.Parameters.Add("@tripID",SqlDbType.Int, 4, "tripdata_trip_link");
0
 
Miguel OzSoftware EngineerCommented:
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
0
 
tia_kamakshiAuthor Commented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Miguel OzSoftware EngineerCommented:
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.
0
 
ROMA CHAUHANProject LeadCommented:
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

0
 
ROMA CHAUHANProject LeadCommented:
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

0
 
tia_kamakshiAuthor Commented:
Many Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.