Populate TextBox With DB Records

  • Thread starter Thread starter rn5a
  • Start date Start date
R

rn5a

This function in a VB class file takes UserID as a parameter & returns
a SqlDataReader to the calling function which exists in a ASPX page:

Namespace NConnect
Public Class Cart
Private sqlConn As New SqlConnection(".....")
Public Function GetAddress(ByVal UserID As Integer) As
SqlDataReader
Dim sqlCmd As SqlCommand
Dim sqlReader As SqlDataReader

sqlCmd = New SqlCommand("NETGetAddress", sqlConn)
sqlCmd.CommandType = CommandType.StoredProcedure

Try
With sqlCmd
.Parameters.Add("@UserID", SqlDbType.Int).Value =
UserID
End With

sqlConn.Open()
sqlReader = sqlCmd.ExecuteReader
Catch ex As Exception
Throw ex
End Try

Return sqlReader
End Function
End Class
End Namespace

Using vbc, I successfully compiled the above class file into a DLL
named NConnect.dll.

This is the simple stored procedure:

CREATE PROCEDURE NETGetAddress
@UserID integer
AS
SELECT UserID, Address, City, State, Country, Zip FROM tblUsers WHERE
UserID = @UserID

The ASPX page uses a user control named 'Address.ascx' which has 5
TextBoxes named txtAddress, txtCity, txtState, txtCountry & txtZip. I
am using the Get & Set statement for creating a property for each of
the TextBoxes like this:

Public Property Address() As String
Get
Address = txtAddress.Text
End Get
Set(ByVal value As String)
txtAddress.Text = value
End Set
End Property

'here comes the other properties

Finally, this is the ASPX page:

<%@ Register TagPrefix="NETConnect" TagName="Address"
Src="NETAddress.ascx" %>
<%@ Import Namespace="NConnect" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
Sub Page_Load(.....)
Dim boCart As Cart
Dim iUserID As Integer
Dim sqlReader As SqlDataReader

'retrieving the UserID by calling another function
sqlReader = boCart.GetAddress(iUserID)
End Sub
</script>

<form runat="server">
<NETConnect:NETAddress ID="ncBillingAddress" runat="server"/>
</form>

The ASPX page will render the 5 TextBoxes. Now how do I populate the 5
TextBoxes with the records that the stored procedure retrieves for a
particular UserID?
 
OK.....this is what I tried in the ASPX page (this ASPX page is named
NETAddress.aspx):

<%@ Register TagPrefix="NETConnect" TagName="Address"
Src="NETAddress.ascx" %>
<%@ Import Namespace="NConnect" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
Sub Page_Load(.....)
Dim boCart As Cart
Dim iUserID As Integer
Dim sqlReader As SqlDataReader
Dim nAddress As netaddress_ascx

'retrieving the UserID by calling another function

nAddress = Page.LoadControl("NETAddress.ascx")
nAddress.ID = "ncBilling"
pnlAddress.Controls.Add(nAddress)

sqlReader = boCart.GetAddress(iUserID)

Response.Write("Field Count: " & sqlReader.FieldCount)
nAddress.Address = sqlReader.GetString(1)
nAddress.City = sqlReader.GetString(2)
nAddress.StateName = sqlReader.GetString(3)
nAddress.Country = sqlReader.GetString(4)
nAddress.Zip = sqlReader.GetString(5)
End Sub
</script>
<form runat="server">
<asp:Panel ID="pnlAddress" runat="server"/>
</form>

But the above generates this error:

Invalid attempt to read when no data is present.

pointing to

nAddress.Address = sqlReader.GetString(1)

What's causing the error? Note the Response.Write(sqlReader.FieldCount)
line. If I comment out all the GetString lines after this line, then
sqlReader.FieldCount correctly displays the field count as 6. So how
come sqlReader.GetString(1) has no data?

Moreover if I bind the data to a DataList, then also the DataList
renders all the records corresponding to the UserID; so where am I
going wrong?
 
Well....the mistake I was making was I wasn't looping through the
SqlDataReader using the Read method of the SqlDataReader....

While(sqlReader.Read)
nAddress.Address = sqlReader.GetString(1)
nAddress.City = sqlReader.GetString(2)
nAddress.StateName = sqlReader.GetString(3)
nAddress.Country = sqlReader.GetString(4)
nAddress.Zip = sqlReader.GetString(5)
End While
 
Back
Top