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) )


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)
  BEGIN try

      INSERT INTO dbo.tbl_Workshop
      VALUES     (@Title,

      DECLARE @WorkshopID AS INT

      SET @WorkshopID=Scope_identity()

      INSERT INTO dbo.tbl_WorkshopTrainer
      SELECT TrainerID,
      FROM   @tvpTID

  END try

  BEGIN catch
  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());

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.