• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Populating a ASP.net form a SQL query

I have a basic web form that has 40 text field controls.  This form is populated via a SQL select statement procedure.  Currently I use the following VB code to populate the form, however there must be a better way that doesn't use so much code.  Any suggestions?

How can I link SqlDataSource and SQL procedure directly with the controls without having to write code for each text control?

ASP File -
<asp:SqlDataSource ID="sp_GetData" runat="server"
          ConnectionString="<%$ ConnectionStrings:GetDataConnectionString %>"
          SelectCommand="sp_GetData" SelectCommandType="StoredProcedure">
              <asp:Parameter Name="UserLgn" Type="String" />


Dim userLgn As String = Request.ServerVariables("LOGON_USER")
            sp_GetMYConsData.SelectParameters("UserLgn").DefaultValue = userLgn
Dim matrixDv As DataView = sp_GetMYConsData.Select(DataSourceSelectArguments.Empty)
            If matrixDv.Count > 0 Then
                For Each matrixDr As DataRowView In matrixDv
                    If Not IsDBNull(matrixDr("Activity1")) Then
                        Activity1.Text = matrixDr("Activity1")
                    End If
                    If Not IsDBNull(matrixDr("Activity2")) Then
                        Activity2.Text = matrixDr("Activity2")
                    End If
                    If Not IsDBNull(matrixDr("Activity3")) Then
                        Activity3.Text = matrixDr("Activity3")
                    End If
                    If Not IsDBNull(matrixDr("Activity4")) Then
                        Activity4.Text = matrixDr("Activity4")
                    End If
                    If Not IsDBNull(matrixDr("Activity5")) Then
                        Activity5.Text = matrixDr("Activity5")
                    End If

.... etc...etc.. for 40 variables... How can I link SqlDataSource and SQL procedure directly with the controls without having to write code for each control?

Open in new window

1 Solution
Carl TawnSystems and Integration DeveloperCommented:
This would be slightly slower because of the FindControl() call but it would reduce the amount of code:
Dim tb As TextBox
For Each matrixDr As DataRowView In matrixDv
    For i As Integer = 1 To 20
        If Not IsDBNull(matrixDr("Activity" & i.ToString())) Then
            tb =CType(FindControl("Activity" & i.ToString()), TextBox)
            tb.Text = matrixDr("Activity" & i.ToString())
        End If

Open in new window

you could use any of the data display objects, e.g.

If you're updating the database with changes in the text boxes, using this approach will significantly reduce the coding required there too
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now