?
Solved

Populating textbox from returned stored procedure values

Posted on 2008-11-19
6
Medium Priority
?
921 Views
Last Modified: 2013-11-27
I have a small app that I need to finish, this is being done in web developer express 2008.  I have so far created a gridview that loads the customer's data.  That gridview is done through code (databind)  first part of code.  Then when the "Edit" command is clicked what I would like is the textboxes corresponding to each field of that gridview to be loaded with the returned values of the corresponding stored procedure.  Any help would be greatly appreciated.
Function loadGrid(ByRef spname As String, ByRef gvName As GridView)
        Dim ConnString As String
        'The SQL Connection
        ConnString = "Data Source=HOME\SQLEXPRESS;Initial Catalog=Mobi;Integrated Security=SSPI;"
        'Set the Connection String
        Dim SQLConn As New SqlConnection(ConnString)
 
        Dim SQLCmd As New SqlCommand(spname, SQLConn) 'The SQL Command
        SQLCmd.CommandType = CommandType.StoredProcedure
        Try
 
            SQLConn.Open() 'Open the connection
            SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
 
            SQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only
 
            gvName.DataSource = SQLCmd.ExecuteReader()
 
            gvName.DataBind()
 
        Catch ex As Exception
            lblMessage.Text = "Could not load data" + ex.Message
        Finally
            SQLConn.Close()
        End Try
 
    End Function
 
'load text
 Function loadtext(ByRef spName As String)
        Dim daMyName As SqlDataAdapter
        Dim dsMyName As DataSet
        Dim ConnString As String
        'The SQL Connection
        ConnString = "Data Source=HOME\SQLEXPRESS;Initial Catalog=Mobi;Integrated Security=SSPI;"
        'Set the Connection String
        Dim SQLConn As New SqlConnection(ConnString)
 
        Dim SQLCmd As New SqlCommand(spName, SQLConn) 'The SQL Command
        SQLCmd.CommandType = CommandType.StoredProcedure
 
        Try
 
            SQLConn.Open() 'Open the connection
            SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
 
            daMyName.SelectCommand = SQLCmd
            daMyName.Fill(dsMyName)
 
            Me.txtFirstName.Text = dsMyName.Tables(0).Rows(0).Item("First Name").ToString
            Me.txtLastName.Text = dsMyName.Tables(0).Rows(1).Item("Last Name").ToString
            Me.txtPhoneNumber.Text = dsMyName.Tables(0).Rows(2).Item("Phone").ToString
            Me.txtEmail.Text = dsMyName.Tables(0).Rows(3).Item("Email").ToString
            Me.txtDOB.Text = dsMyName.Tables(0).Rows(4).Item("DOB").ToString
            Me.txtAddress.Text = dsMyName.Tables(0).Rows(5).Item("Address").ToString
            Me.txtCity.Text = dsMyName.Tables(0).Rows(6).Item("City").ToString
            Me.ddlState.SelectedValue = dsMyName.Tables(7).Rows(0).Item("State").ToString
            Me.txtZip.Text = dsMyName.Tables(0).Rows(8).Item("Zip").ToString
            Me.txtHeight.Text = dsMyName.Tables(0).Rows(9).Item("Height").ToString
            Me.txtWeight.Text = dsMyName.Tables(0).Rows(10).Item("Weight").ToString
            Me.ddlGender.SelectedValue = dsMyName.Tables(0).Rows(11).Item("Gender").ToString
            ' Me.txtMedicare.Text = dsMyName.Tables(0).Rows(0).Item("Medicare").ToString
            'Me.txtMedicareType.Text = dsMyName.Tables(0).Rows(0).Item("MedicareType").ToString
            'Me.ddlInsuranceType.SelectedValue = dsMyName.Tables(0).Rows(0).Item("InsuranceType").ToString
            ' Me.txtMedicaid.Text = dsMyName.Tables(0).Rows(0).Item("Medicaid").ToString
            Me.txtNotes.Text = dsMyName.Tables(0).Rows(16).Item("Notes").ToString
 
        Catch ex As Exception
            lblMessage.Text = "Could not load data" + ex.Message
        Finally
            SQLConn.Close()
        End Try
 
 
    End Function

Open in new window

0
Comment
Question by:andywhat
  • 3
  • 3
6 Comments
 
LVL 6

Expert Comment

by:HDSportster08
ID: 22997008
right above your text that you want populated you need to reference your text box, like object.textbox1 or whatever you have it named.
0
 

Author Comment

by:andywhat
ID: 22997051
HDSportster08:

If you are referring to the actual textbox it is created in the aspx page, the control is there.  I am referencing the control in the vb file.

This is a sample of what I have in the aspx page:
<div class="title">
            Personal Information:
            </div>
            <table>
                <tr>
                    <td>
                    First Name:
                    </td>
                    <td>
                    <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    Last Name:
                    </td>
                    <td>
                    <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    Phone Number:
                    </td>
                    <td>
                    <asp:TextBox ID="txtPhoneNumber" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    Email:
                    </td>
                    <td>
                    <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    Date of Birth:
                    </td>
                    <td>
                    <asp:TextBox ID="txtDOB" runat="server" Text="MM/DD/YYYY"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    Address:
                    </td>
                    <td>
                      <asp:TextBox ID="txtAddress" runat="server" Width="204px"></asp:TextBox>
                    </td>
                    <td>
                    City:
                    </td>
                    <td>
                      <asp:TextBox ID="txtCity" runat="server" Width="131px"></asp:TextBox>
                    </td>
                    <td>
                    State:
                    </td>
                    <td>
                    <asp:DropDownList ID="ddlState" runat="server" Width="44px">
                    </asp:DropDownList>
                    </td>
                    <td>
                    Zip: 
                    </td>
                    <td>
                    <asp:TextBox ID="txtZip" runat="server" Width="80px"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    Height:
                    </td>
                    <td>
                    <asp:TextBox ID="txtHeight" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>
                    Weight:
                    </td>
                    <td>
                    
                    <asp:TextBox ID="txtWeight" runat="server"></asp:TextBox>
                    </td>
                </tr>
                <tr>
            

Open in new window

0
 
LVL 6

Expert Comment

by:HDSportster08
ID: 22997088
so you would need something like this right?


Here is the coding;
<asp:TemplateColumn HeaderText="Data Entry">
<ItemTemplate>
<img src="images/Barrow.gif" onclick="CopyTotal(this);" title="Click here to
copy Previous Field to this text box">
<asp:TextBox CssClass="printableTextbox" runat="server"
onchange="AddRequesterName(this);" Readonly='<% #
iif(Container.DataItem("NotAllowed")=1,True,False) %>' id="txtDataEntry"
Text='<% # Format(Container.DataItem("Data"),"$#,##0.00") %>'
onblur="this.value=FormatCurrency(this.value,1);">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateColumn>

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:andywhat
ID: 23002899
I believe that code is for a gridview item, this is regular textboxes that I am trying to fill with data
0
 

Accepted Solution

by:
andywhat earned 0 total points
ID: 23004671
Ok I figured it out, this is what I was looking for:
 Function loadtext(ByRef spName As String, ByRef intPatientID As Integer)
        Dim ConnString As String
        'The SQL Connection
        ConnString = "Data Source=HOME\SQLEXPRESS;Initial Catalog=Mobi;Integrated Security=SSPI;"
        'Set the Connection String
        Dim SQLConn As New SqlConnection(ConnString)
        Dim SQLCmd As New SqlCommand(spName, SQLConn) 'The SQL Command
 
        Try
            With SQLCmd
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@PatientID", SqlDbType.VarChar, 100)
                .Parameters("@PatientID").Value = intPatientID
            End With
            SQLConn.Open()
            Dim da As New SqlDataAdapter(SQLCmd)
            Dim ds As New DataSet
            Dim dt As New DataTable
            da.Fill(ds)
            Dim dr As DataRow
 
            If ds.Tables(0).Rows.Count > 0 Then
                With ds.Tables(0)
                    dr = .Rows(0)
                    txtFirstName.Text = dr("FirstName").ToString
                    txtLastName.Text = dr("LastName").ToString
                    Me.txtPhoneNumber.Text = dr("PhoneNumber").ToString
                    Me.txtEmail.Text = dr("Email").ToString
                    Dim dob As Date = Convert.ToDateTime(dr("DOB"))
                    Me.txtDOB.Text = dob
                    Me.txtAddress.Text = dr("Address").ToString
                    Me.txtCity.Text = dr("City").ToString
                    Me.ddlState.SelectedValue = dr("State").ToString
                    Me.txtZip.Text = dr("Zip").ToString
                    Me.txtHeight.Text = dr("Height").ToString
                    Me.txtWeight.Text = dr("Weight").ToString
                    Me.ddlGender.SelectedValue = Trim(dr("Gender").ToString)
                    ' Me.txtMedicare.Text = dsMyName.Tables(0).Rows(0).Item("Medicare").ToString
                    'Me.txtMedicareType.Text = dsMyName.Tables(0).Rows(0).Item("MedicareType").ToString
                    'Me.ddlInsuranceType.SelectedValue = dsMyName.Tables(0).Rows(0).Item("InsuranceType").ToString
                    ' Me.txtMedicaid.Text = dsMyName.Tables(0).Rows(0).Item("Medicaid").ToString
                End With
            End If
 
 
        Catch ex As Exception
            lblMessage.Text = "Could not load data" + ex.Message
        Finally
            SQLConn.Close()
        End Try
 
 
    End Function

Open in new window

0
 
LVL 6

Expert Comment

by:HDSportster08
ID: 23004691
Great to hear you figured it out.  This stuff is hard to me as well.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

850 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