Print Page | Close Window

DB query

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: ASP.NET Discussion
Forum Description: Discussion and chat on ASP.NET related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=7317
Printed Date: 29 March 2026 at 12:51am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: DB query
Posted By: IrishNewbie
Subject: DB query
Date Posted: 16 November 2003 at 11:51am

When i use the line

aQuery = "Select StudentID From Students Where (StudentID = '"& strUsername &"')"

what exactly happens if it finds the ID in the table?? How would i access the password assoc with this ID when im doin the following login authentication.

 

Sub Submit_OnClick(sender as Object, e as EventArgs)
  If StudentLogin (txtUserName.Text, txtPassword.Text) Then   
      FormsAuthentication.RedirectFromLoginPage (txtUserName.Text, False)
  Else
      ' Invalid credentials supplied, display message
      lblMessage.Text = "Invalid login credentials"
  End If
End Sub

 

Function StudentLogin (ByVal strUsername As String, _
                          ByVal strPassword As String) As Boolean
    ' Open DB
 Dim aConnection As OleDbConnection = New OleDbConnection
         Dim aConnectionString As String
         Dim aQuery As String
         aConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DB\records.mdb"
         aConnection.ConnectionString = aConnectionString
         Dim aCommand As OleDbCommand = New OleDbCommand
         aConnection.Open()
   
 aQuery = "Select StudentID From Students Where (StudentID = '"& strUsername &"')"
 aCommand.Connection = aConnection
         aCommand = New OleDbCommand(aQuery, aConnection)
 aCommand.ExecuteNonQuery()

    ' Return True if UserID found else False
 
End Function                          

I'm confused as to where the ID is stored from the select statement if found in the table.




Replies:
Posted By: MorningZ
Date Posted: 17 November 2003 at 6:26am

First off...  your query looks like you are trying to find a numeric ID, and you are feeding it "strUsername", which looks like a string that holds the person's username, not their numeric identification number

so you'll have to start by double checking and fixing that.....

then if that is the case and you are looking for a number in the SQL query, then you do not use single tic's around the right hand side of the equation (which says "against this string")



-------------
Contribute to the working anarchy we fondly call the Internet


Posted By: IrishNewbie
Date Posted: 17 November 2003 at 8:01am

morningZ,

Yea thats true, Im looking for a numeric ID in the table, I've changed this to suit but Im unsure of how to write the validation against the DB to check whether its valid or not.  I'm attempting to do this in the following segment, could you give me your opinion on it?

 

Public Function Login(intID As Integer, strPassword As String)

    Dim aConnection As New OleDbConnection, aCommand As New      OleDbCommand, DbReader As OleDbDataReader, LoginSucces As Boolean
  Dim aConnectionString As String
  

    'The connection string
  
  aConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DB\records.mdb"
    aConnection.ConnectionString = aConnectionString
    'Open the connection
    aConnection.Open()

 

 'Initialize the database command
 

     aCommand.CommandText = "SELECT Pword FROM Students WHERE StudentID = " & intID & ""

 

 Try

    DbReader = aCommand.ExecuteReader()
    While DbReader.Read

       LoginSucces = True

    End While
    DbReader.Close

 Catch

   LoginSucces = False

 End Try

 aConnection.Close()

 End Function



Posted By: MorningZ
Date Posted: 17 November 2003 at 8:15am

well yeah..

you pass in "strPassword" into the function, but no where in the code do you check against that....

man, i hate to say it, you have a far uphill climb in programming whatever it is you are programming if you are getting tripped up on this stuff



-------------
Contribute to the working anarchy we fondly call the Internet


Posted By: IrishNewbie
Date Posted: 17 November 2003 at 8:34am

Can i say something like,

If (strPassword = aCommand.CommandText)

LoginSucess=True

?????

Ive tried it but I dont think i should be using aCommand.CommandText.  I just dont know how to use the Pword once i select if from the DB, i dont know where it gets stored



Posted By: MorningZ
Date Posted: 17 November 2003 at 8:39am
Originally posted by IrishNewbie IrishNewbie wrote:


I just dont know how to use the Pword once i select if from the DB, i dont know where it gets stored

you arent even selecting it from the database!!!!  so "where it gets stored" is the least of your problems



-------------
Contribute to the working anarchy we fondly call the Internet


Posted By: IrishNewbie
Date Posted: 17 November 2003 at 8:41am
Man theres no need to be a prick about everything.  This is meant as a discussion board where people can talk about ASP.net and problems they are having, just because every member isnt a natural born genius like you are doesnt give you the right to act like you do.


Posted By: MorningZ
Date Posted: 17 November 2003 at 9:16am

i dont think i am a "genious" nor everyone else should be...

but this stuff is basic basic basic stuff... stuff that has absolutely zero to do with .NET code, it all has to do with common sense

you are looking to compare a user entered value to a value in the database and your code doesn't even try to do it.... no where in any of all the code you posted do you ask the database: "is the value entered the same as the value in the database"

you need to start off way more simple than you are, because you are waaaaaay over your head.... people on message boards and etc can help you to a point, but you HAVE to know the basics yourself first



-------------
Contribute to the working anarchy we fondly call the Internet


Posted By: IrishNewbie
Date Posted: 17 November 2003 at 9:26am

I haven't been doing ASP.NET as lons as others on this board, I got landed with a project which has to be done and I've worked out how to let people register on the site but its when i try using SELECT that I run into trouble, I've been pissing around with a login page for over a week now and getting nowhere.

I obviously forgot these parts of the code:

'Initialize the database command
 

     aQuery = "SELECT Pword FROM Students WHERE StudentID = " & intID & ""
  aCommand.Connection = aConnection
  aCommand = New OleDbCommand(aQuery, aConnection)
  aCommand.ExecuteNonQuery()
 

 Try

    DbReader = aCommand.ExecuteReader()
    While DbReader.Read
  If (strPassword = ???????????????)


       LoginSucces = True
  End If
    End While
    DbReader.Close

 Catch

   LoginSucces = False

 End Try

 aConnection.Close()

Am i heading in the right direction at all with that much?



Posted By: Diep-Vriezer
Date Posted: 17 November 2003 at 11:06am

That looks like my code! Except you modified it a bit to heavy. Anyway, you could try this:

Public Function Login(strId As Integer, strPassword As String)

Dim DbConnection As New OleDbConnection, DbCommand As New OleDbCommand, DbReader As OleDbDataReader, LogginSucces As Boolean

     With DbConnection

           .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\DB\records.mdb"
          'Open the connection
           .Open()

     End With

     'Initialize the database command

     With DbCommand

         .CommandText = "SELECT * FROM Students WHERE StudentID = " & strId & "' And Pword = '" & strPassword & "'"
         .Conne ction = DbConnection

     End With

 Try

    DbReader = aCommand.ExecuteReader()
    While DbReader.Read

       LoginSuccesfull = True

    End While
    DbReader.Close()

 Catch

   LogginSucces = False

 End Try

 DbConnection.Close()

End Function



-------------
Gone..


Posted By: MorningZ
Date Posted: 17 November 2003 at 11:19am

Originally posted by IrishNewbie IrishNewbie wrote:


Am i heading in the right direction at all with that much?

theres a better way, and there's all sorts of holes in your code

1) First off.. with your SQL statement.. how are you supposed to know what Password to get if the person isn't logged in to provide your code with what "StudentID" we are using
2) What about checking the username??
3) How are you planning on having the code "remember" that the person entered the right password?

I mean see, those three questions have absolutely zip to do with exact coding, this whole project of your hasn't been thought out on single bit judging by what you have posted.... you are so unprepared.....  maybe you take that constructive criticism as "me being a prick", but as long as you realize that you really need to do some research and learn some basics before taking on something so incredibly far over your head...... it appears you just copied and pasted some code w/o having even the slighest clue on what it does or is used for.... that's never ever going to result in success

anyways.. alternative code following along with this http://www.4guysfromrolla.com/webtech/chapters/ASPNET/ch06.2.shtml - excellent data access article from 4guysfromrolla

Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim myDataReader As OleDbDataReader

Dim aQuery As String
aQuery = "SELECT StudentID, Uname FROM Students WHERE Pword = '" & Replace(strPassword,"'","''") & "' AND Uname = '" & Replace(strUsername,"'","''") & "'"
myConnection = New OleDbConnection(aConnection)
myConnection.Open()
myCommand = New OleDbCommand(aQuery, myConnection )
myDataReader = myCommand.ExecuteReader()
If myDataReader.HasRows() then
    'Login Good, we have rows returned that matched
    While myDataReader.Read()
        'maybe store the Uname and StudentID in session variables or something
    End While
else
    'Login Bad since we have no rows
End if
myDataReader.Close()
myConnection.Close

you can take my criticism/help or leave it, i am not here to make friends or do anyone else's work for them, i'm here to help with code :)  but what you are after right now is not code, its getting a clue on what the heck you are doing from a much higher level



-------------
Contribute to the working anarchy we fondly call the Internet


Posted By: Diep-Vriezer
Date Posted: 17 November 2003 at 2:45pm

You should add the

Try
Catch
End Try

in the proces, since the app will fail when the table doesn't returns something.



-------------
Gone..


Posted By: MorningZ
Date Posted: 17 November 2003 at 3:15pm
the ".HasRows" property is basically a "do i have returned rows" check, the try...catch block is not necessary.....  and in the case of wrapping database calls is probably undesired since that'll mask/hide what a database error would be if it happens...

-------------
Contribute to the working anarchy we fondly call the Internet



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net