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