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 @ConfirmationCode. And when I do Parameters.AddWithValue 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=dataColumn" 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.Parameters["@Carrier"].SourceVersion = DataRowVersion.Original;)

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

[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.ColumnName-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.ColumnName 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.VarChar).Value = doCarrier.ColumnName;