Using VB.net code to take Text from a TextBox and use for an SQL Stored Procedure

Question

I have a funny situation where I am trying to pass a date (formatted as a String in yyyy-MM-dd format [i.e. 2013-08-01]) to an SQL stored procedure within an asp.net webpage. I have the stored procedure set up, but now I have to pass the date as a parameter. The date I need is located in a separate SQL table, so I created a TextBox (we'll call it endDATE) that provides the date I will need AND that I can hide to make it invisible on the webpage.

The problem is that the TextBox is contained within a DetailsView, so I can't seem to access it for the stored procedure.

I'm thinking I should try to pass the String from the TextBox using VB code to the stored procedure. How do I do this?

Code of TextBox:

<asp:DetailsView ID="LatestDate" DataSourceID="SqlDataSource6" runat="server" AutoGenerateRows="false" 
AutoGenerateColumns="False" BorderStyle="None" GridLines="None">
     <Fields>
     <asp:BoundField DataField="MostRecent" HeaderText="" ReadOnly="True" ShowHeader="False"
     DataFormatString="Daily: {0:M/d/yyyy}" ItemStyle-CssClass="boldbig" />
     <asp:TemplateField>
      <ItemTemplate>
       <asp:TextBox runat="server" ID="endDATE" Visible="false" Text='<%# Eval("MostRecent","{0:yyyy-MM-dd}") %>' />
      </ItemTemplate>
     </asp:TemplateField>
     </Fields> 
</asp:DetailsView>

Code of Stored Procedure [date1 is the parameter I am trying to provide]:

<asp:SqlDataSource ID="SqlDataSource7" runat="server"
    ConnectionString="<%$ConnectionStrings:ConnectionString3 %>" 
    ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" 
    SelectCommand="dbo.TheMgr_Total"
    SelectCommandType="StoredProcedure">
     <SelectParameters>
       <asp:QueryStringParameter name="MgrName" DbType="String" QueryStringField="id" />
       <asp:Parameter Name="date1" DbType="String" DefaultValue="2013-08-08" />
     </SelectParameters>
</asp:SqlDataSource>

Update: Steve's solution looks good to me, but I was never able to get it to work. My project changed directions, so I am no longer working on this particular problem.

Hi there, would you be able to post any code (e.g. the ASP.NET page markup and the VB.NET code-behind) which you have so far? I think this will let folks here provide more specific advice to your situation — nkvu
– nkvu, Commented Aug 13, 2013 at 21:26
If you need just some temp storage to read data from one place (table) and pass to another (SP) - you do not need a textbox, just use a variable of needed type (string, date etc.) — Comfortably Numb
– Comfortably Numb, Commented Aug 13, 2013 at 21:33
Are you writing SQL (structured query language) and really mean Microsoft SQL Server (the actual product) by this? If yes: please add sql-server tag to make this clear. If not: what database system is this for? — marc_s
– marc_s, Commented Aug 14, 2013 at 4:56

Steve · Accepted Answer · 2013-08-13 21:33:51Z

Passing parameters to a stored procedure is a very common task and it is well documented

Dim dateValue as DateTime
dateValue = GetDateFromDetailsView()

Using cn As SqlClient.SqlConnection(conString)
   cn.Open()
   Using cmd AS SqlClient.SqlCommand(cn)
       cmd.CommandText = "storedProcName"
       cmd.CommandType = CommandType.StoredProcedure
       cmd.Parameters.AddWithValue("@theDate", dateValue)
       cmd.ExecuteNonQuery()
   End Using
End Using

Of course, you need to replace the storedProcName and the @theDate parameter with their actual values The only problem is retrieving the variable dateValue of type DateTime. Here I need again to suppose a lot about your page structure but perhaps the GetDateFromDetailsView could be written as

 public Function GetDateFromDetailsView() As String

      Dim txtHiddenText = detailsViewName.FindControl("txtHiddenText")
      return txtHiddenText.Text

 End Function

Collectives™ on Stack Overflow

Using VB.net code to take Text from a TextBox and use for an SQL Stored Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related