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

Reply

I0ri

None

0 Points

38 Posts

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

Feb 05, 2014 05:37 AM|I0ri|LINK

Hi all,

My MS Access table 'Credential' consist of 4 fields

Username
Password
LoginFlag (Either 'Online' or 'Offline')
SessionID (Either a unique IP Addr or 'nil')

The purpose of this aspx:

Search through the ms access database table called 'Credential' to check whether there is a row with the paramentered SessionID
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'
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

Reply

DarrellNorto...

All-Star

43204 Points

10243 Posts

MVP

Re: How to retrieve data from a specific row of the database?

Feb 05, 2014 05:47 AM|DarrellNorton|LINK

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

Darrell Norton, MVP
Darrell Norton's Blog
Please click "Mark as Answer" if this helped you.