MSSQL data to many textbox using a loop

Posted on 2009-02-17
Last Modified: 2013-12-25
Very new to but very well versed with classic asp. Need assistance to pull data from mssql and fill many textboxes, looking for a loop that goes thought the columns and fills the textbox that have the same name. also in this group are pull down menus that need to change based on the data . In my web.config file I have a connection String named CLLDdataSQLConnectionString

Please remember very new to and need the connection code to accompany the solution
Question by:crundle
    LVL 27

    Expert Comment

    LVL 27

    Expert Comment

    See to set up connection string.

    Author Comment

    I actually have the below code but wanted to get ride of all the textbox = filed's to clean up and make more managable
     Protected Sub Page_Load(ByVal sender As Object, _
      ByVal e As System.EventArgs) Handles Me.Load
            ' If Not Page.IsPostBack Then
            '  FillAuthorList()
            'End If
            ' Create a Select statement that searches for a record
            ' matching the specific author ID from the Value property.
            Dim selectSQL As String
            selectSQL = "SELECT * FROM tblshipment "
            selectSQL &= "WHERE ShipmentID= 1499"
            ' Define the ADO.NET objects.
            Dim con As New SqlConnection(connectionString)
            Dim cmd As New SqlCommand(selectSQL, con)
            Dim reader As SqlDataReader
            ' Try to open database and read information.
                reader = cmd.ExecuteReader()
                ' Fill the controls.
                'For x = 0 as integer
                '  For x to 100
                MBL.Text = reader("MBL").ToString()
                'CLID.Text = reader("CLID").ToString()
                MBL.Text = reader("MBL").ToString()
                HBL.Text = reader("HBL").ToString()
                SUB_BL.Text = reader("Sub_BL").ToString()
                Client_Name.Text = reader("CLIENT_NAME").ToString()
                'CLIENT_ID.Text = reader("CLIENT_ID").ToString()
                Shipper_Name.Text = reader("Shipper_Name").ToString()
                Shipper_Address.Text = reader("Shipper_Address").ToString()
                Shipper_address2.Text = reader("Shipper_address2").ToString()
                Shipper_address3.Text = reader("Shipper_address3").ToString()
                FA_Name.Text = reader("FA_Name").ToString()
                FA_Address.Text = reader("FA_Address").ToString()
                FA_address2.Text = reader("FA_address2").ToString()
                FA_address3.Text = reader("FA_address3").ToString()
                Consignee_Name.Text = reader("Consignee_Name").ToString()
                Consignee_Address.Text = reader("Consignee_Address").ToString()
                Consignee_Address2.Text = reader("Consignee_address2").ToString()
                Consignee_Address3.Text = reader("Consignee_address3").ToString()
                Notify_Name.Text = reader("Notify_Name").ToString()
                Notify_Address.Text = reader("Notify_Address").ToString()
                Notify_address2.Text = reader("Notify_address2").ToString()
                Notify_address3.Text = reader("Notify_address3").ToString()
                Broker.Text = reader("Broker").ToString()
                'SSLType_of_Move.Text = reader("SSLType_of_Move").ToString()
                'Railmove.Text = reader("Railmove").ToString()
                'CUSTSSLType_of_Move.Text = reader("CUSTSSLType_of_Move").ToString()
                POINT_OF_ORIGIN.Text = reader("POINT_OF_ORIGIN").ToString()
                Voyage_Flight.Text = reader("Voyage_Flight").ToString()
                Vessel_Airline.Text = reader("Vessel_Airline").ToString()
                Carrier.Text = reader("Carrier").ToString()
                Port_of_Loading.Text = reader("Port_of_Loading").ToString()
                Port_of_Discharge.Text = reader("Port_of_Discharge").ToString()
                Rail_destination.Text = reader("Rail_destination").ToString()
                Place_of_Delivery.Text = reader("Place_of_Delivery").ToString()
                IT_Number.Text = reader("IT_Number").ToString()
                FirmCode.Text = reader("FirmCode").ToString()
                'active.Text = reader("active").ToString()
                'EnteredBy.Text = reader("EnteredBy").ToString()
                'Lastupdateby.Text = reader("Lastupdateby").ToString()
                'seawaybl.Text = reader("seawaybl").ToString()
                ProDescription.Text = reader("ProDescription").ToString()
                IT_Number_Date.Text = reader("IT_Number_Date").ToString()
                'EnteredDate.Text = reader("EnteredDate").ToString()
                ' LastUpdate.Text = reader("LastUpdate").ToString()
                ' chkContract.Checked = CType(reader("contract"), Boolean)
                ' lblResults.Text = ""
            Catch Err As Exception
                '  lblResults.Text = "Error getting author. "
                ' lblResults.Text &= Err.Message
            End Try
        End Sub

    Open in new window

    LVL 27

    Expert Comment

    Here's one option:

    Instead of doing a select *.. explicitly mention the column names in the select statement. I'll give an example for just 3 fields.

    Say your SELECT statement looks like this:
    "SELECT MBL, CLID, HBL FROM tblshipment WHERE ShipmentID= 1499"

    Create an array of textboxes:

    List<TextBox> tbs = new List<TextBox>();
    'Add the textboxes in the same order as the columns in your SELECT statement.

    For i = 0 to tbs.Count
       tbs(i).Text = reader(i).ToString()
    Next i

    Author Comment

    I copy the above but tbs, list  is not declaired. is that VB code or c#. sorry very new to
    LVL 27

    Accepted Solution

    My apologies... I had mixed up C# and Here's the right version:

    Dim tbs As New List(Of TextBox)()  
    'Add the textboxes in the same order as the columns in  your SELECT statement.  
    For i As Integer = 0  To tbs.Count - 1  
        tbs(i).Text = reader(i).ToString()  


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now