Why isn't my stored procedure recognizing my parameter from my code?

[LELE7]

My .Net code:

 connDO.Open();
                    SqlCommand comm = new SqlCommand("DOrders.dbo.p_Update", connDO);
                    comm.CommandType = CommandType.StoredProcedure;
                    daDirectOrders.UpdateCommand = comm;

	comm.Parameters.Add("@UOrderNumber", SqlDbType.Int, 32, uOrderNumber.ColumnName);
                    comm.Parameters.Add("@ConfirmationCode", SqlDbType.VarChar, 50, confirmationCode.ColumnName);
                    comm.Parameters["@ConfirmationCode"].SourceVersion = DataRowVersion.Original;
                    comm.Parameters.Add("@Carrier", SqlDbType.VarChar, 50, doCarrier.ColumnName);
                    comm.Parameters.Add("@Tracking", SqlDbType.VarChar, 50, doTracking.ColumnName);
                    comm.Parameters.Add("@OrderID", SqlDbType.Int, 32, orderID2.ColumnName);
                    comm.Parameters["@OrderID"].SourceVersion = DataRowVersion.Original;
                    comm.Parameters.Add("@LineItem", SqlDbType.Int, 32, lineItem.ColumnName);
                    comm.Parameters["@LineItem"].SourceVersion = DataRowVersion.Original;

                    daDirectOrders.Update(dataSet);
                    connDO.Close();

My Stored Procedure:

	@UOrderNumber int,
	@ConfirmationCode varchar(50),
	@Carrier varchar(50),
	@Tracking varchar(50),
	@OrderID int,
	@LineItem int
	
AS
BEGIN

	SET NOCOUNT ON;

	UPDATE Orders 
		SET UOrderNumber = @UOrderNumber
		WHERE ConfirmationCode = @ConfirmationCode

	UPDATE OrdersDetail
		SET Carrier = @Carrier, Tracking = @Tracking, LastUpdated = GETDATE()
        WHERE OrderID = @OrderID and LineItem = @LineItem


END

When I run, I get the following error:

Procedure or function 'p_update' expects parameter '@Carrier' which was not supplied

Why isn't is recognizing my parameter? Also, it does not give a problem with @UOrderNumber and @ConfirmationCo de. And when I do Parameters.AddW ithValue and pass a string value, it does work. The problem is, that addWithValue does not take a dataColumn from what I saw. (correct me if I'm wrong)

Thanks!

[Christian Binder]

The problem is, your parameter doesn't have a value supplied.
If you don't want to use AddWithValue, you can do the follwing:

comm.Parameters.Add("@Carrier", SqlDbType.VarChar, 50, doCarrier.ColumnName).Value = ...;

The Add-method returns the SqlParameter which was added.

[LELE7]

But my value is the data from the datacolumn in my dataset. When I do ".Value=dataCol umn" I still get the same error.
By the way, this same code worked when it was not a stored procedure, but SQL in my code. How does that make sense?

[Christian Binder]

Oh, I see. I have never used SqlParameters with source-columns.
What if you set the SourceVersion-property for @Carrier like you did on the other parameter? (comm.Parameter s["@Carrier"].SourceVersion = DataRowVersion. Original;)

When you debug, does dataSet have a column named like the value of doCarrier.Colum nName?

[LELE7]

The sourceVersion property is used for the column that is acting as my primary key. Original, I believe, means that it was not changed. I tried DataRowVersion. current which did not help. I tried specifying parameter direction and source column again, but nothing seemed to help.

The column exists in the dataSet with that name, so I am going out of my mind.

Isn't this a common thing- to update a database from a dataset? There were so few examples online, but my code does seem to follow everything exactly. Also, like I mentioned, the first part of my stored procedure works, so that makes me even more confused.

[Christian Binder]

Maybe it's a problem with datatypes/length? What's the datatype of doCarrier.Colum nName-column in dataSet?

I've never used that way for updating a database...

If you add the param with AddWithValue (and a test-string) for @Carrier, does it work? I think of this because @Tracking-param is done the same way. So we could figure out, if the problem is definitly the @Carrier-param.

[LELE7]

I checked the datatypes a lot of times and everything matches the database.

A test string does work. The problem comes up just for @Carrier because it's first. If I put a test string, then @Tracking pops up... etc.

Is there another way to update a database that would work in this situation?

[Christian Binder]

Which DataTables are inside your DataSet? Maybe it doesn't know from which DataTable to take the columns.
I've had a look there.

Are you able to send me some more code (including sql-tables), so I could try a little on my own?

[Plater]

is doCarrier.Colum nName an empty string or null?

[LELE7]

Thanks Christian! That was it. I actually figured it out becaue it worked when I split into 2 stored procedures- 1 for each table.

Just curious, is there a way to choose more than 1 table in a dataset to update with 1 update command? I know there's a way to specify 1 table, or the whole datset (which is 4 tables in this case).

[talhakarkun]

U Can use following code :

comm.Parameters .Add("@Carrier" , SqlDbType.VarCh ar).Value = doCarrier.Colum nName;