How to pass dynamically generated textbox values as a datatable param to a stored proc RSS

• • Reply
  

  snailpace

  Member

  31 Points

  285 Posts

  How to pass dynamically generated textbox values as a datatable param to a stored proc

  May 14, 2012 06:10 PM|LINK

  I am using vb.net 2010 and sql 2008. My application is such.

  i have a dropdownlist, when user selects an item from this list, a bunch of textbox controls are generated. user can enter something in any of these texboxes and hit a button that will search inventory based on the text entered and return the results to a grid.

  I am able to generate textboxes and capture their values. But to save these values to a datatable, i have difficulty. I can capture only one column value which is an integer. but the text value is string, and i cant capture that. so whats happening is only the value frm the first textbox is being written to datatable. i tried looping through all the controls, but then i get the error that "input string is more than columns". I need help with figuring this out. My code for the button_click event is as follows. thanks

   Try
  Dim dattab As New DataTable
  Dim datRow As DataRow
  Dim id As New Integer
  Dim str As String
  Dim tbox As New TextBox
  For Each c As Control In txtPnl.Controls
  If c.[GetType]() = GetType(TextBox) Then
  tbox =DirectCast(c, TextBox)
  id = tbox.ID
  str = tbox.Text
  dattab.Columns.Add("ID", GetType(Integer))
  dattab.Columns.Add("Value", GetType(String))
  For Each col As DataColumn In dattab.Columns
  datRow = dattab.NewRow
  datRow("ID") = id
  datRow("Value") = tbox.Text
  dattab.Rows.Add(datRow)
  Next
  Dim commsearch As SqlCommand= sqlConn.CreateCommand
  commsearch.CommandType =CommandType.StoredProcedure
  commsearch.CommandText ="SearchInventory "
  Dim param1 As SqlParameter = commsearch.Parameters.AddWithValue("@a_type", Session("Controls"))
  Dim param2 As SqlParameter = commsearch.Parameters.AddWithValue("@att_table", dattab)
  param2.SqlDbType =SqlDbType.Structured
  sqlConn.Open()
  commsearch.ExecuteNonQuery()
  dim da As SqlDataAdapter
  da =New SqlDataAdapter(commsearch)
  Dim ds As New DataSet
  da.Fill(ds)
  da.Dispose()
  sqlConn.Close()
  Me.gridDisplay.Visible = True
  Me.bttAdd.Visible = True
  Me.gridDisplay.DataSource = ds
  Me.gridDisplay.DataBind()
  End  If
  Next
  Catch ex As Exception
  end Try

• • Reply
  

  pierrefrc

  Participant

  947 Points

  201 Posts

  Re: How to pass dynamically generated textbox values as a datatable param to a stored proc

  May 16, 2012 12:23 AM|LINK

  Hello

  Do you still help for this problem?

  Kindly mark this post as "Answer", if it helped you.
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: How to pass dynamically generated textbox values as a datatable param to a stored proc

  May 16, 2012 02:08 AM|LINK

  snailpace

  one column value which is an integer. but the text value is string,

  Just use Integer.Parse or Convert.ToInt32(……) to deal with the problem。

  I still don't know what you really want，so please answer my questions below to make it clear：

  1）What do you want to do？

  2）What have you done？

  3）What's the expected result？

  4）What's the actual result？

  Reguards！

• • Reply
  

  snailpace

  Member

  31 Points

  285 Posts

  Re: How to pass dynamically generated textbox values as a datatable param to a stored proc

  May 16, 2012 07:31 PM|LINK

  Thank you for replying.

  My problem is that when i enter some text in the textboxes and add these in datatable. The table is populated with just the first textbox entry. I am not able to get the text entered in all the textboxes. I've tried looping, but apparently i am not doing it right. If you could suggest a suitable way to save all the textbox imput values into a datatable that would be great.

  thanks

• • Reply
  

  pierrefrc

  Participant

  947 Points

  201 Posts

  Re: How to pass dynamically generated textbox values as a datatable param to a stored proc

  May 17, 2012 12:14 AM|LINK

  Hi

  I did same changes in your code.

  • When I was testing the "If c.[GetType]() = GetType(TextBox) Then" entered only once then I change to " If (TypeOf ctl Is TextBox) Then".
  • Other change was in the call of sp for after the foreach. 

  code behind

  Dim sqlConn As SqlConnection = New SqlConnection("")
          sqlConn.Open()
  
          Dim ds As New DataSet
          Dim dattab As New DataTable
          Dim datRow As DataRow
  
          dattab.Columns.Add("ID", GetType(String))
          dattab.Columns.Add("Value", GetType(String))
  
          For Each ctl As Control In txtPnl.Controls
  
              If (TypeOf ctl Is TextBox) Then
  
                  Dim tbox As New TextBox
                  tbox = DirectCast(ctl, TextBox)
  
                  datRow = dattab.NewRow
                  datRow("ID") = tbox.ID
                  datRow("Value") = tbox.Text
                  dattab.Rows.Add(datRow)
  
              End If
  
          Next
  
          Dim commsearch As SqlCommand = sqlConn.CreateCommand
          commsearch.CommandType = CommandType.StoredProcedure
          commsearch.CommandText = "SearchInventory"
          Dim param1 As SqlParameter = commsearch.Parameters.AddWithValue("@a_type", Session("Controls"))
          Dim param2 As SqlParameter = commsearch.Parameters.AddWithValue("@att_table", dattab)
          param2.SqlDbType = SqlDbType.Structured
          commsearch.ExecuteNonQuery()
  
          Dim da As SqlDataAdapter = New SqlDataAdapter(commsearch)
          da.Fill(ds)
          da.Dispose()
  
          commsearch.Dispose()
          sqlConn.Close()
  
          Me.gridDisplay.Visible = True
          Me.bttAdd.Visible = True
          Me.gridDisplay.DataSource = ds
          Me.gridDisplay.DataBind()

  asp.net

   <asp:Panel ID="txtPnl" runat="server">
              <asp:TextBox ID="TextBox1" runat="server" Text="1"></asp:TextBox>
              <asp:TextBox ID="TextBox2" runat="server" Text="2"></asp:TextBox>
          </asp:Panel>
          <asp:Button ID="bttAdd" runat="server" />
          <asp:GridView ID="gridDisplay" runat="server">
          </asp:GridView>
          <asp:DataList ID="t" runat="server" RepeatColumns="3" RepeatDirection="Horizontal"
              RepeatLayout="Table">
              <ItemTemplate>
                  <asp:Label ID="valor" runat="server"></asp:Label>
              </ItemTemplate>
          </asp:DataList>

  I hope this helps.

  Kindly mark this post as "Answer", if it helped you.
  • Marked as answer by snailpace on May 17, 2012 01:13 PM
• • Reply
  

  snailpace

  Member

  31 Points

  285 Posts

  Re: How to pass dynamically generated textbox values as a datatable param to a stored proc

  May 17, 2012 01:14 PM|LINK

  Thank you Pierrefrc! It works like a charm!

  I was missing the correct if clause. Now its doing what its supposed to be doing

  Thank you