Need help in .NET coding

• denotilater

  24 Jan 09, 02:19

  Hello guys, I've question regarding retrieving of data from SQL server 2005 and then displaying it on a textbox / label. So hopefully I can find aid here. I've done some research and was thinking of using dataReader to read and then display data. I'm doing it 3-tier.

  So these are my codes...

  ViewInformationDAL.vb

  Public Class ViewInformationDAL
      Dim cmd As New System.Data.SqlClient.SqlCommand
  
      Sub New()
          'Empty constructor
      End Sub
  
      Public Function viewInformation(ByVal username As String, ByVal password As String) As String
          Dim dReader As System.Data.IDataReader = Nothing
          Dim StrSelect As String = "SELECT * FROM Members"
          Dim msg As String = Nothing
          Try
              retrieveDReader(StrSelect, dReader)
              While dReader.Read
                  msg = "Successful"
              End While
          Catch ex As System.Data.DataException
              msg = ex.Message
          End Try
          Return msg
      End Function
  
      Public Sub retrieveDReader(ByVal StrSelect As String, ByRef dReader As System.Data.IDataReader)
  
          Dim connectionString As String = My.Settings.dbConnection
          Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
          dbConnection.Open()
          Try
  
              cmd.Connection = dbConnection
              cmd.CommandText = StrSelect
              dReader = cmd.ExecuteReader
  
          Catch ex As Exception
  
          End Try
  
      End Sub
  End Class

  ViewInfoBLL.vb

  Imports Microsoft.VisualBasic
  
  Public Class ViewInfoBLL
      Dim loginAdapter As DAL.LoginDAL = New DAL.LoginDAL
  
      Public Sub New()
      End Sub
  
  
      Public Function GetLogin(ByVal username As String, ByVal password As String) As String
          Dim str As String = loginAdapter.GetLogin(username, password)
          Return str
      End Function
  
  End Class

  Default.aspx.vb

  
  Partial Class Default
      Inherits System.Web.UI.Page
      Dim retrieveInfo As ViewInfoBLL = New ViewInfoBLL
  
      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
          Dim username As String = Nothing
          Dim password As String = Nothing
  
          Dim Result As String = retrieveInfo.GetLogin(username, password)
  
          If Result = "Successful" Then
              'I want to display value thats from database into the question marks part
              TextBox1.Text = "???"
              TextBox2.Text = "???"
          Else
              TextBox1.Text = "Error"
              TextBox2.Text = "Error"
          End If
  
      End Sub
  End Class

  And lastly there's tableAdapter

  Hopefully someone can enlighten me.Thanks alot and greatly appreciated for any advices and helps.

   

• GIB

  24 Jan 09, 11:45

  Imports System.Data.SqlClient
  
  Public Class Form1
      Inherits System.Web.UI.Page
  
  Dim myConnection As SqlConnection
  Dim myCommand As SqlCommand
  Dim dr As New SqlDataReader()
  'declaring the objects
  
  Public Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  myConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")
  'establishing connection. you need to provide password for sql server
  Try
  myConnection.Open()
  'opening the connection
  myCommand = New SqlCommand("Select col1, col2 from Members", myConnection)
  'executing the command and assigning it to connection
  dr = myCommand.ExecuteReader()
  if dr.Read() Then
  'reading from the datareader
  
  TextBox1.Text = dr(0).ToString()
  TextBox2.Text = dr(1).ToString()
  else
  TextBox1.Text = "Not found"
  TextBox2.Text = "Not found"
  End If
  Catch e As Exception
  TextBox1.Text = "Error : " & e.Message
  TextBox2.Text = "Error : " & e.Message
  Finally
  'need to do checking for state first before closing
  dr.Close()
  myConnection.Close()
  End Try
  End Sub
  
  End Class

• denotilater

  24 Jan 09, 23:46

  Hi GIB, thanks for your reply, but however...I'm quite confused with your codes.

• GIB

  25 Jan 09, 11:40

  Originally posted by denotilater:

  Hi GIB, thanks for your reply, but however...I'm quite confused with your codes.

  It's just a simple example of a code behind page which opens a connection to an SQL Server and read the values from the database table and then assigns the values to the 2 textboxes. Which part is confusing?

• denotilater

  25 Jan 09, 15:06

  Thanks GIB for reply, but I managed to solve it . Did some changes and examples from your codes and i got it. Thanks again ^^