locked
How to retrieve data from a specific row of the database? RRS feed

  • Question

  • User-1974669539 posted

    Hi all,

    My MS Access table 'Credential' consist of 4 fields

    1. Username
    2. Password
    3. LoginFlag (Either 'Online' or 'Offline')
    4. SessionID (Either a unique IP Addr or 'nil')

    The purpose of this aspx:

    1. Search through the ms access database table called 'Credential' to check whether there is a row with the paramentered SessionID
    2. If there IS (Only will have ONE row with that SessionID if there is), the LoginFlag data will be extracted and converted to string and check whether If is 'Online'
    3. If the LoginFlag is "Online", the Username is extracted and converted to string and replace the initial return string value of the method.

     

    The problem right now is How am i suppose to extract the data from my sql? I failed to do so! The LoginCheck method return "TestLineError1". This means the row is found and existed in the database but how do I extract the loginflag and username data. Please assist thanks!

     

    Imports System.Data.OleDb
    Public Class LoginCheck
        Inherits System.Web.UI.Page
    
        Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|MMORPG.mdb")
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            'Dim sidQS As String = HttpContext.Current.Request.QueryString("sid")
    
            Dim reply As String = LoginCheck("http://192.168.1.182")
            Response.Write(reply)
        End Sub
    
        Public Function LoginCheck(ByVal sidPara As String) As String
    
    
            Dim result As String = "#na"
    
    
            Dim sqlStr As String = "SELECT COUNT(*) AS NUMBEROFROWS FROM Credential WHERE [SessionID] = '" + sidPara + "'"
    
            Dim cmd As New OleDbCommand(sqlStr, cnn)
            Dim dataReader As OleDbDataReader
            Trace.Write(sqlStr)
    
    
    
            Try
    
                cnn.Open()
    
                dataReader = cmd.ExecuteReader()
    
                Dim counter As Integer
    
                Do While dataReader.Read
                    counter = Convert.ToInt16(dataReader("NUMBEROFROWS"))
                Loop
    
                If counter = 1 Then
                    result = "TestLineError1"
                    If (Convert.ToString(dataReader("LOGINFLAG")) = "Online") Then
                        result = "TestLineError2"
                        result = Convert.ToString(dataReader("USERNAME"))
    
                    End If
    
                End If
    
    
            Catch ex As Exception
    
            Finally
                If Not cnn Is Nothing Then
                    cnn.Close()
                    cnn.Dispose()
                End If
            End Try
    
            Return result
        End Function
    
    
    End Class

    Wednesday, February 5, 2014 5:37 AM

Answers

  • User-837620913 posted

    Your SQL statement only returns a count of rows: SELECT Count(*) FROM ....

    I don't know why you would think that you could then access something from the database that you had not retrieved.

    Change your SQL Statement to select the entire row and then you can access LoginFlag:

    Public Function LoginCheck(ByVal sidPara As String) As String
    	Dim result As String = "#na"
    	Dim sqlStr As String = "SELECT * FROM Credential WHERE [SessionID] = '" + sidPara + "'"
    
    	Dim cmd As New OleDbCommand(sqlStr, cnn)
    	Dim dataReader As OleDbDataReader
    	Trace.Write(sqlStr)
    
    	Try
    		cnn.Open()
    		dataReader = cmd.ExecuteReader()
    
    		Do While dataReader.Read 'It will skip this while loop if there are no rows
    			If (Convert.ToString(dataReader("LOGINFLAG")) = "Online") Then
    				result = "TestLineError2"
    				result = Convert.ToString(dataReader("USERNAME"))
    			End If
    		Loop
    
    	Catch ex As Exception
    
    	Finally
    		If Not cnn Is Nothing Then
    			cnn.Close()
    			cnn.Dispose()
    		End If
    	End Try
    
    	Return result
    End Function
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 5, 2014 5:47 AM

All replies

  • User-837620913 posted

    Your SQL statement only returns a count of rows: SELECT Count(*) FROM ....

    I don't know why you would think that you could then access something from the database that you had not retrieved.

    Change your SQL Statement to select the entire row and then you can access LoginFlag:

    Public Function LoginCheck(ByVal sidPara As String) As String
    	Dim result As String = "#na"
    	Dim sqlStr As String = "SELECT * FROM Credential WHERE [SessionID] = '" + sidPara + "'"
    
    	Dim cmd As New OleDbCommand(sqlStr, cnn)
    	Dim dataReader As OleDbDataReader
    	Trace.Write(sqlStr)
    
    	Try
    		cnn.Open()
    		dataReader = cmd.ExecuteReader()
    
    		Do While dataReader.Read 'It will skip this while loop if there are no rows
    			If (Convert.ToString(dataReader("LOGINFLAG")) = "Online") Then
    				result = "TestLineError2"
    				result = Convert.ToString(dataReader("USERNAME"))
    			End If
    		Loop
    
    	Catch ex As Exception
    
    	Finally
    		If Not cnn Is Nothing Then
    			cnn.Close()
    			cnn.Dispose()
    		End If
    	End Try
    
    	Return result
    End Function
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 5, 2014 5:47 AM
  • User-1974669539 posted

    thank you Sir Darrell! It works now!

    Wednesday, February 5, 2014 8:16 PM