January 24, 2014 at 1:59 am
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 to insert the webform data into my db.
but this is taking just one trainerID, how abt inserting as many trainers as the user selects from the chkboxlist?
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,
@TrainerID as int
)
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
values(@TrainerID,@WorkshopID)
commit transaction
end try
begin catch
rollback transaction tr_insert
end catch
January 24, 2014 at 3:19 am
What do you pass to the @TrainerID argument when you have multiple selection?
Igor Micev,My blog: www.igormicev.com
January 24, 2014 at 4:29 am
Thts what my question is..
January 24, 2014 at 4:48 am
You can write your stored procedure with a table-valued parameter instead of scalar parameters. You'd need to use an OUTPUT clause on your INSERT statement instead of relying on SCOPE_IDENTITY.
John
January 24, 2014 at 5:10 am
If you pass multiple TrainerID-s delimited, than you can parse them in the stored procedure and execute this insert
insert into dbo.tbl_Workshop
values(@Title,@Topic,@Date,@Duration,@CreatedDate,@UpdatedDate)
as many times as the trainer ids there are in the passed string.
Another option is by using TVPs.
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
January 24, 2014 at 11:40 am
Can I know how to use TVP in my stored procedure?
January 24, 2014 at 12:19 pm
rani_sals (1/24/2014)
Can I know how to use TVP in my stored procedure?
You can read about them here. http://technet.microsoft.com/en-us/library/bb510489.aspx
The basis gist of it is that you first create the type, then in your client application you would then need to populate the table variable and finally pass it in to your proc. Once inside your proc you can join to that parameter just like you would any other table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 24, 2014 at 1:14 pm
Thanks. So I tried this code, but it gives me error saying I must declare @TrainerID,, when I've already done that.
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 @TrainerID as tvpTID, @WorkshopID as int
SET @WorkshopID=SCOPE_IDENTITY()
insert into dbo.tbl_WorkshopTrainer
values(@TrainerID,@WorkshopID)
commit transaction
end try
begin catch
rollback transaction tr_insert
end catch
January 24, 2014 at 1:50 pm
rani_sals (1/24/2014)
That is pretty close. Remember that your user defined type is not functioning as a table. What you did is declare a new instance of that datatype but didn't put anything in it.
This should be pretty close (I formatted this for legibility).
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2014 at 1:38 am
Oh okay!
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
January 25, 2014 at 7:52 am
rani_sals (1/25/2014)
Oh okay!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
You need to read up a bit on tvp and how they work. You also need to think through what you are doing here. Your parameter is a table, not an int.
Your first error is because you are trying to get the rows collection of your datatable and parse it as an int. That doesn't make sense. There are a number of .net objects that can populate a tvp. From looking at your code, the easiest is probably to use a datatable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2014 at 8:20 am
If I try something like this:
How to pass a tvp in C#?
protected void btnSave_Click(object sender, EventArgs e)
{
foreach (ListItem item in CheckBoxListTrainers.Items)
{
if (item.Selected)
{
DataSet1TableAdapters.tbl_Trainer1TableAdapter tt = new DataSet1TableAdapters.tbl_Trainer1TableAdapter();
DataSet1.tbl_Trainer1DataTable dt = tt.GetDataBy(item.Text);
int i = int.Parse(dt.Rows[0][0].ToString()); // i gives me ID's of the selected trainers
}
}
DataSet1TableAdapters.tbl_WorkshopTrainerTableAdapter ta = new DataSet1TableAdapters.tbl_WorkshopTrainerTableAdapter();
ta.sp_InsertWorkshopTrainers(txtTitle.Text, txtTopic.Text, DateTime.Now, txtDuration.Text, DateTime.Now, DateTime.Now,*???*); // What to pass as a TVP for the trainerID's???
}
January 26, 2014 at 2:54 am
protected void btnSave_Click(object sender, EventArgs e)
{
DataTable dataTable = new DataTable("tvpTID");
dataTable.Columns.Add("TrainerID", typeof(Int32));
foreach (ListItem item in CheckBoxListTrainers.Items)
{
if (item.Selected)
{
DataSet1TableAdapters.tbl_Trainer1TableAdapter tt = new DataSet1TableAdapters.tbl_Trainer1TableAdapter();
DataSet1.tbl_Trainer1DataTable dt = tt.GetDataBy(item.Text);
int i = int.Parse(dt.Rows[0][0].ToString());
dataTable.Rows.Add(i);
}
}
DataSet1TableAdapters.tbl_WorkshopTrainerTableAdapter ta = new DataSet1TableAdapters.tbl_WorkshopTrainerTableAdapter();
ta.sp_InsertWorkshopTrainers(txtTitle.Text, txtTopic.Text, DateTime.Now, txtDuration.Text, DateTime.Now, DateTime.Now, dataTable);
Response.Write("Workshop added successfully");
BindData();
}
THis worked well for me. THanks everyone who tried helping me:)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply