Hi,
I've a webform that has five fields,, the first four fields has textboxes and the fifth one is a checkbox list. All the data of textboxes has to be saved in a table tbl_Workshop.
the checkbox list has list of trainers, a user can select for that particular wokrshop.. the checkbox selected items should go in tbl_WorkshopTrainers that has columns workshopId and trainerID.
I wrote a below storedprocedure with TVP to insert the webform data into my db.

CREATE type dbo.tvpTID AS TABLE ( TrainerID int NOT NULL, PRIMARY KEY (TrainerID) )

go

ALTER PROCEDURE Sp_insertworkshoptrainers (@Title       AS VARCHAR(50),
                                           @Topic       AS VARCHAR(50),
                                           @Date        AS DATE,
                                           @Duration    AS VARCHAR(50),
                                           @CreatedDate AS DATE,
                                           @UpdatedDate AS DATE,
                                           @tvpTID      TVPTID Readonly)
AS
  BEGIN try
      BEGIN TRANSACTION tr_Insert

      INSERT INTO dbo.tbl_Workshop
      VALUES     (@Title,
                  @Topic,
                  @Date,
                  @Duration,
                  @CreatedDate,
                  @UpdatedDate)

      DECLARE @WorkshopID AS INT

      SET @WorkshopID=Scope_identity()

      INSERT INTO dbo.tbl_WorkshopTrainer
      SELECT TrainerID,
             @WorkshopID
      FROM   @tvpTID

      COMMIT TRANSACTION
  END try

  BEGIN catch
      ROLLBACK TRANSACTION tr_insert
  END catch 

Well now to call this SP for UI, I use this below code.. but what do I have to pass as the tvp object parameter, to enter the selected chkboxlist items.
my UI shows the trainers name so first I'm fetching their ID's and then have to pass the same as tvp object.

string r = CheckBoxListTrainers.Items.ToString();

DataSet1TableAdapters.tbl_Trainer1TableAdapter tt = new DataSet1TableAdapters.tbl_Trainer1TableAdapter();
DataSet1.tbl_Trainer1DataTable dt = tt.GetDataBy(r); // this getDataBy() calls for a SP that takes the id and returns its name from tbl_Trainer
int id = int.Parse(dt.Rows.ToString()); // here I'm getting an error saying input string in not in the correct format


DataSet1TableAdapters.tbl_WorkshopTrainerTableAdapter ta = new DataSet1TableAdapters.tbl_WorkshopTrainerTableAdapter();
ta.sp_InsertWorkshopTrainers(txtTitle.Text, txtTopic.Text, DateTime.Now, txtDuration.Text, DateTime.Now, DateTime.Now,id); // here id is an object tvp that has to passed in the SP

int id = int.Parse(dt.Rows.ToString());

specify the row,If it retrns one row use like below
int id = int.Parse(dt.Rows[0].ToString());

This article has been dead for over six months. Start a new discussion instead.