Member Avatar for salwa.kh.9

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
  • 2 Contributors
  • 1 Reply
  • 236 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by george4986

Dani AI

Generated

The code in the thread has three related issues that explain the errors and why the TVP parameter is empty:

  • : CheckBoxListTrainers.Items.ToString() does not return selected item values; it returns the collection type name. Likewise dt.Rows.ToString() returns the collection's type name, so int.Parse(...) fails.
  • : dt.Rows[0].ToString() is still a DataRow string, not the column value. Read the column from the row (for example dt.Rows[0]["TrainerID"]) and convert with Convert.ToInt32(...).

Recommended, reliable approach (steps):

  1. Bind the CheckBoxList so each ListItem has DataTextField = trainer name and DataValueField = trainer ID. That gives direct access to IDs.
  2. Build a DataTable that exactly matches the TVP type (same column name and type: TrainerID int). Add one row per selected checkbox.
  3. Call the stored procedure with a SqlParameter of SqlDbType.Structured, set TypeName = "dbo.tvpTID", and pass the DataTable as the parameter value. If the designer-generated TableAdapter does not accept a TVP, use a plain SqlCommand.

Example (new code snippet — not in earlier posts):

var tvp = new DataTable();
tvp.Columns.Add("TrainerID", typeof(int));
foreach (System.Web.UI.WebControls.ListItem li in CheckBoxListTrainers.Items)
    if (li.Selected) tvp.Rows.Add(Convert.ToInt32(li.Value));

using(var cn = new SqlConnection(connString))
using(var cmd = new SqlCommand("Sp_insertworkshoptrainers", cn))
{
  cmd.CommandType = CommandType.StoredProcedure;
  // add scalar params...
  cmd.Parameters.Add(new SqlParameter("@tvpTID", SqlDbType.Structured) { TypeName = "dbo.tvpTID", Value = tvp });
  cn.Open(); cmd.ExecuteNonQuery();
}

Quick troubleshooting tips: ensure the CheckBoxList values are IDs (not names), the DataTable column name exactly matches the TVP schema, and prefer Convert.ToInt32 or (int) on DataRow column values instead of parsing the DataRow object. Also consider changing the SP to specify INSERT column lists and to THROW or RAISERROR in the CATCH so calling code can see failures.

Member Avatar for george4986

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.