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
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.
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
The purpose of this aspx:
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 ClassAll-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 FunctionDarrell Norton's Blog
Please click "Mark as Answer" if this helped you.
None
0 Points
38 Posts
Re: How to retrieve data from a specific row of the database?
Feb 05, 2014 08:16 PM|I0ri|LINK
thank you Sir Darrell! It works now!