Home Forums Programming General No Value Given For One or More Required Parameter oledb to Excel RE: No Value Given For One or More Required Parameter oledb to Excel

  • Why are you using String.Format()? I don't think it's needed, but it's not what's causing your problem - you get the same INSERT or UPDATE statement whether you surround it with String.Format() or you don't.

    I would need to spend some time looking at this problem. I'm not sure if it's even possible to do what you're trying to do. It seems you want to use ADO.NET to update an Excel worksheet as if it were a SQL database. You might need a completely new approach.

    Perhaps you or someone else on this forum has had successful experience doing something along these lines. If so, I would like to know how it can be done.

    It's been a while since I've programmed using ADO.NET but from what I remember, you would normally provide your SqlDataAdapter (or OleDbDataAdapter) with a SelectCommand with a WHERE clause that filtered the rows by the primary key such as

    SELECT OrderID, ProductID, Quantity, UnitPrice

    FROM [Order Details]

    WHERE OrderID = @OrderID AND ProductID = @ProductID

    Then you could either use the Table Adapter / Data Adapter Configuration Wizard in Visual Studio or SqlCommandBuilder to generate the InsertCommand, UpdateCommand, and DeleteCommand, or you could provide your own. The InsertCommand would look something like the following:

    INSERT INTO [Order Details]

    ( OrderID, ProductID, Quantity, UnitPrice )

    VALUES ( @p1, @p2, @p3, @p4 )

    The UpdateCommand might look like the following:

    UPDATE [Order Details]

    SET OrderID = @OrderID_New, ProductID = @ProductID_New, Quantity = @Quantity_New, UnitPrice = @UnitPrice_New

    WHERE OrderID = @OrderID_Old AND ProductID = @ProductID_Old

    The DeleteCommand might be

    DELETE [Order Details]

    WHERE OrderID = @OrderID AND ProductID = @ProductID

    Again, this worked well for tables in SQL Server databases but I don't know if you are trying to apply this to Excel, where it probably would not be appropriate.

    Regards,

    Brian