Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2009-12-27
7
Medium Priority
?
1,069 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:tia_kamakshi
  • 3
  • 2
  • 2
7 Comments
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 26126860
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
 

Author Comment

by:tia_kamakshi
ID: 26127151
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
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 26128482
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:ROMA CHAUHAN
ID: 26130463
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
 
LVL 11

Expert Comment

by:ROMA CHAUHAN
ID: 26130507
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
 
LVL 11

Accepted Solution

by:
ROMA CHAUHAN earned 2000 total points
ID: 26130518
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
 

Author Closing Comment

by:tia_kamakshi
ID: 31670172
Many Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question