0

I've a Stored Procedure that insert some record then update it

CREATE PROCEDURE [dbo].[InsertSponsors]
@Someparameters  nvarchar(100),
@Anotherparameter int,
AS
    
Begin

DECLARE @ID int,

 @RowCount int

    insert into Clients (Name,Photo,PolicyID,BirthDay,
    Gender,Title,Nationality,Relationship,ClassID,HRID,Active,
    StartingDate,EndingDate,AddingDate,CreationOwner,UniqueID,PassportNo)
    values (@Name,@Photo,@PolicyID,@Birthday,@Gendar,@Title,@Nationality,@RelationShip,
    @ClassID,@HRID,@Active,@StartingDate,@EndingDate,@AddingDate,@CreationOwner,@UniqueID,@PassportNo)

    SELECT @RowCount = @@ROWCOUNT;

    Select @ID=SCOPE_IDENTITY();
    update Clients set SponsorID=@ID where ID=@ID;

     
End

I'm Calling this Stored Procedure from DataAdapter like below

Notice: I've tried all the commented code but with no luck

internal static int UpdateInsertAdapter(string query,DataTable dt, SqlParameter[] sqlParameters, bool SP)
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = openConnection();
            cmd.CommandText = query;
            if (SP)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            cmd.UpdatedRowSource = UpdateRowSource.None;
            foreach (SqlParameter parameter in sqlParameters)
            {
                cmd.Parameters.Add(parameter);
            }
            
            SqlDataAdapter adpt = new SqlDataAdapter();
            //adpt.AcceptChangesDuringFill = true;
            //adpt.AcceptChangesDuringUpdate = true;  
            //SqlCommandBuilder cmdb = new SqlCommandBuilder(adpt);
            //adpt.InsertCommand = cmdb.GetInsertCommand();
            adpt.InsertCommand = cmd;

            **//////Should i've select command to select the @RowCount**

            adpt.UpdateBatchSize = 10;

            int recordsInserted = adpt.Update(dt);
            return recordsInserted;
        }

Notice:

This Datatable is modified as i read it using ExcelDataReader then i've added two DataColumns to that Datatable

Questions

what can i do to bulk insert Datatable and select the inserted rows.

In this article Codeproject Multiple Ways to do Multiple Inserts the author wrote a Stored Procedure just to insert can i insert then update

Errors:

1.Update requires a valid UpdateCommand when passed DataRow collection with modified rows

2.The DataAdapter.SelectCommand property needs to be initialized.

Update

As per jmcilhinney help i figured out the reason for error #1 and i've found a Solution at this question by user Dan by using SqlCommandBuilder.

            SqlCommandBuilder cmdb = new SqlCommandBuilder(adpt);
            adpt.InsertCommand = cmd;
            adpt.UpdateCommand = new SqlCommandBuilder(adpt).GetUpdateCommand();

but when i use these lines error #2 occurs.

Community
  • 1
  • 1
Ahmed TalǮat
  • 27
  • 9
  • Maybe if you were to explain what actually happens when you execute the code you have, we could narrow down the issue more easily. If there's an exception then there's an error message, which is provided for diagnostic purposes. If you want us to diagnose the issue, logic dictates that you provide that information. – jmcilhinney Jun 05 '15 at 02:47
  • @jmcilhinney sorry i've missed to put the error messages .. i've edited my post ... Thanks for your comment anyway :) – Ahmed TalǮat Jun 05 '15 at 02:53
  • The issue becomes plain now that we know the error messages. In the case of the first one, that indicates that your `DataTable` contains rows with a `RowState` of `Modified`. If you intend to insert records then your rows need to have a `RowState` of 'Added`. The `RowState` is set to `Added` by default when you create a row and then `Add` it to the `Rows` collection of the `DataTable`. The `RowState` is set to `Modified` if you edit an existing row. – jmcilhinney Jun 05 '15 at 05:42
  • With regards to the second error message, is that actually occurring when you call `Update` or elsewhere when you call `Fill`? The `SelectCommand` should not be required unless you want to retrieve data, which you do by calling `Fill`. It's irrelevant when you save changes, which you do by calling `Update`. – jmcilhinney Jun 05 '15 at 05:52
  • @jmcilhinney Thanks for your reply. i've understood problem 1 but can i change the rowstate after modifications. for problem 2 i'm not calling Fill() anywhere . i've changed the Stored Procedure and removed the Update and the Select part and Created a trigger which do this task my SP now just inserts and second error still there as it is – Ahmed TalǮat Jun 05 '15 at 06:10
  • I've tried to change the rowstate to DataRowState.Added but it's cannot be assigned to it is read only – Ahmed TalǮat Jun 05 '15 at 06:18
  • Where and when EXACTLY is that second error message displayed? I could see that maybe happening if you hadn't commented out the bit of code relating to a command builder but, without that or a call to `Fill`, I can't see why the `SelectCommand` would matter. – jmcilhinney Jun 05 '15 at 06:39
  • You generally don't change the `RowState` to `Added`. As I said, it's set to `Added` automatically when you add them. If the `RowState` is `Modified` then that means that you're modifying existing rows. Why are you doing that if your intention is to insert new rows? – jmcilhinney Jun 05 '15 at 06:40
  • i read an excel file from the user and then add two columns to the datatable then i populate these columns with some values – Ahmed TalǮat Jun 05 '15 at 06:53
  • i figured out why i'm shuffling between these two errors i found a solution to the modified rows error which is by using the command builder so when i uncomment the adpt.UpdateCommand = new SqlCommandBuilder(adpt).GetUpdateCommand(); line . it throws exception #2 – Ahmed TalǮat Jun 05 '15 at 07:12
  • "i read an excel file from the user and then add two columns to the datatable then i populate these columns with some values" How did you read the file? Did you use a data adapter? If so, set its `AcceptChangesDuringFill` property to `False` before calling `Fill`. That way, `AcceptChanges` won't be called and all the `RowState` values won't be changed from `Added` to `Unchanged`. – jmcilhinney Jun 05 '15 at 07:37
  • "using the command builder" Don't use a command builder. The whole point of a command builder is for it to create the action commands for you but you want to create your own `InsertCommand` and that's all. – jmcilhinney Jun 05 '15 at 07:40
  • Sorry @jmcilhinney for disturbing you .. but I read the file using ExcelDataReader after that I've modified the datatable – Ahmed TalǮat Jun 05 '15 at 07:57
  • Presumably you are calling `Load` on a `DataTable` and passing the data reader as an argument. That `Load` method is overloaded and should allow you to pass a `LoadOption` value as a second argument. Try passing `Upsert` to that parameter and I think that should do what you want, i.e. leave each row with a `RowState` of `Added`. – jmcilhinney Jun 05 '15 at 09:44

0 Answers0