CODEWITHSUNDEEP
vb.net
user3378302
user3378302

Reputation: 1

Retrieve data from database in asp.net using vb

Below is the code for fetching the data into textbox but its not working it shows error no data exits row/column whereas data and datafield are perfectly alright. Please help.

    Dim Connection As OleDbConnection
    Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/db/QardanHasana.mdb"))
    Connection.Open()

    Dim ejamaatregcmd As OleDbCommand
    Dim ejamaatregdtrdr As OleDbDataReader
    ejamaatregcmd = New OleDbCommand("SELECT ITSData.[EJamaatID], ITSData.[ITSFirstName] FROM ITSData WHERE  EjamaatID= @EjamaatID", Connection)
    ejamaatregcmd.Parameters.Add(New OleDbParameter("@EjamaatID", txtEjamaatID.Text))
    ejamaatregdtrdr = ejamaatregcmd.ExecuteReader()


    If ejamaatregdtrdr.HasRows Then
        txtFirstName.Text = ejamaatregdtrdr.item("ITSFirstName").ToString()
    end if

Upvotes: 0

Views: 609

Answers (3)

Brett Caswell
Brett Caswell

Reputation: 1504

I'll generally do something like the following

Function GetResult(ID As string) As String
    GetResult = "No Data" 'Default Result
Dim conn As System.Data.OleDb.OleDbConnection = *NewConnectionObject*
Dim comm As System.Data.OleDb.OleDbCommand = *NewCommmandObject*
comm.Parameter.AddWithValue("@Parametername",ID)

    Using reader As System.Data.OleDb.OleDbDataReader = comm.ExecuteReader()
        If reader.HasRows Then
            'Reader has data, so iterate through it
            GetResult = ""
            while reader.read
               GetResult += reader("FirstName")
            End While
        Else
            'Either Do Nothing - Default Result will show
            'Throw New System.Exception("Empty Data") 'Try Catch Statement have overhead.. so it's not a popular methodology
            'Or Log Something.. 
        End If
    End Using
    If conn.state = connection.open Then
        conn.close
    End
End Function

Upvotes: 0

Steve
Steve

Reputation: 216243

A DataReader needs a call to Read to position itself on the first record retrieved

ejamaatregdtrdr = ejamaatregcmd.ExecuteReader()
If ejamaatregdtrdr.HasRows Then
     ejamaatregdtrdr.Read()
     txtFirstName.Text = ejamaatregdtrdr.item("ITSFirstName").ToString()
End if

By the way, Read returns false if there are no rows to read, so you could remove the test for HasRows and write simply

ejamaatregdtrdr = ejamaatregcmd.ExecuteReader()
If ejamaatregdtrdr.Read() Then
     txtFirstName.Text = ejamaatregdtrdr.item("ITSFirstName").ToString()
End if

Another suggestion to improve your code is to start using the Using Statement

Using Connection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;....")
Using ejamaatregcmd = new OleDbCommand("SELECT ITSData.[EJamaatID], ITSData.[ITSFirstName] FROM ITSData WHERE  EjamaatID= @EjamaatID", Connection)
    Connection.Open()
    ejamaatregcmd.Parameters.Add(New OleDbParameter("@EjamaatID", txtEjamaatID.Text))
    Using ejamaatregdtrdr = ejamaatregcmd.ExecuteReader()
        If ejamaatregdtrdr.Read() Then
           txtFirstName.Text = ejamaatregdtrdr.item("ITSFirstName").ToString()
       End if
    End Using
End Using
End Using

The using statement is invaluable to help you to close and dispose the disposable objects like the connection, the command and the reader. Lacking a proper dispose your code uses more memory and locks resources resulting in a more unstable application.

Upvotes: 1

Markus
Markus

Reputation: 22421

Before reading data from a DataReader, you need to move the row to the first row by calling the Read method. It returns true if data exist, so you don't need to check the HasRows property:

ejamaatregdtrdr = ejamaatregcmd.ExecuteReader()
If ejamaatregdtrdr.Read() Then
    txtFirstName.Text = ejamaatregdtrdr.Item("ITSFirstName").ToString()
End if

Upvotes: 0

Related Questions