[RESOLVED] [2008] user login

[bagstoper]

i am trying to log a user in using a login form. i have my connection to my database and there and it connects just fine but it will only read the first row. here is my code.

        Dim ConnString As String = "Driver={MySQL ODBC 3.51 Driver};Server=dansoft.selfip.com;Option=131072;Stmt=;Database=elemental_battle; User=****;Password=*****;"
        Dim objConn As New OdbcConnection(ConnString)

        objConn.Open()
        Dim dt As New DataTable
        Dim sqlComm As New OdbcCommand("SELECT username,password from userinfo", objConn)
        Dim dr As OdbcDataReader = sqlComm.ExecuteReader
        dr.Read()
   
        Dim user As String = CStr(dr("username"))
        Dim pass As String = CStr(dr("password"))

        If user = UsernameTextBox.Text And pass = PasswordTextBox.Text Then
            frmMain.Show()
            Me.Close()
        Else
            MessageBox.Show("Invalid Username or password, Please try again")
        End If
        objConn.Close()

there are two entries in the database. the first username and password are bagstoper and 1a2b3c4 the second is bagstoper2 and 1a2b3c4d. when i test it with the first username and password it works but when i try the second it doesnt work. so any help would be great. thank you.

[jmcilhinney]

You're only calling Read once so, logically, you're only reading one record. That said, there's no point bringing back every user name and password when all you really care about is if there is one that matches. The database can tell you that. Follow the Validate Credentials link in my signature for an example.

[jmcilhinney]

I'd also suggest that you download the free ADO.NET provider from MySQL and use that rather than using Odbc.

http://dev.mysql.com/downloads/connector/net/5.2.html

[bagstoper]

i tried the link in your sig but it failed every time when i knew the userID and password were correct.

Dim connection As New OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=dansoft.selfip.com;Option=131072;Stmt=;Database=elemental_battle; User=****;Password=****;")
        Dim command As New OdbcCommand("SELECT COUNT(*) FROM userinfo WHERE username = @UserID AND password = @Password", connection)

        With command.Parameters
            .AddWithValue("@UserID", UsernameTextBox.Text)
            .AddWithValue("@Password", PasswordTextBox.Text)
        End With
        connection.Open()
        If CInt(command.ExecuteScalar()) = 0 Then
            MsgBox("Invalid Username or Password.")
        Else
            frmMain.Show()
            Me.Hide()
        End If
        connection.Close()

maybe there was something wrong with my modified version of your code.

[jmcilhinney]

MySQL doesn't prefix parameters with "@". I believe that MySQL uses "?". You may be able to just replace all the "@" with "?", or you may have to remove the entire parameter names from the SQL code and use just "?" on its own.

[bagstoper]

got an error.

ERROR [42000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.51a]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UserID AND password = '1a2b3x4'Password' at line 1

that is when i changed the '@'s to'?'s

[bagstoper]

i checked the documentation and it is still an @ symbol not a ?

[aldrean]

Had you try "?" only instead of "?UserID"

[bagstoper]

i am having trouble with the ADO.NET provider from MySQL. i have to create views and i have never done that. this is my view command code.

@UserID=usernametextbox.text
@password=passwordtextbox.text
SELECT COUNT(*) FROM userinfo WHERE username = @UserID AND password = @Password

but it still says that i need to declare @userID

[aldrean]

On #3 reply above by Jm, there is a suggestion there for you to download.Have you try that yet?

Also, try to make your query in the TableAdapter and try to observe there what really is an issue.Honestly, I can't give an accurate solution as much as I want since I am using MS SQL Server.

[bagstoper]

Had you try "?" only instead of "?UserID"

yes i tried ?userId and ?password

[bagstoper]

On #3 reply above by Jm, there is a suggestion there for you to download.Have you try that yet?

yes i have downloaded it that is why i need the info on the create view stuff because that is how it gets the data at least that is what the documentation says.

Also, try to make your query in the TableAdapter and try to observe there what really is an issue.Honestly, I can't give an accurate solution as much as I want since I am using MS SQL Server.

how do i make a table adapter i used to know how to do it but i cant remember now.

[aldrean]

Firstly, you have to create a DataSet.On you project add an item and select DataSet,name the DataSet whatever you want.Now it will give you a blank dataset now(grey color),you have to right click on this and select Add TableAdapter.It will prompt you to make a connection to the database,and if your successfull, you can select which specific table/s you want to to load on your adapter.

[bagstoper]

ok i have a table adapter but how do i use it to check the username and password?

[aldrean]

Well, lets see, I am not keen in explaining details but I will try.

I don't have idea which table you had selected in making the TableAdapter but I would assume that its the one where you can find the UserName and Password fields.Now,on your dataset you will see your table adapter right, so select the adapter and right click to it and select configure.It will give you the TableAdapter Configuration Wizard, now select Query Builder on the lower right side of the wizard and it will show you the table you had selected as well the sql statement that you have.

The best way to check you query earlier is to put on the Select statement section what you have earlier on you post, and from there, try to click the Execute Query button.See what goes wrong from there.
For sure, that would prompt you for error, now you have to experiment which parameter sign you have to use, if its ? then go and check, otherwise try the @.

[bagstoper]

i tried it and it didn't work. i think it is because i need to tell it what @userid and @password are but i cant since it isnt being done at run time. i have to have this code for it to return any data. even though it is wrong.

      With command.Parameters
            .AddWithValue("@UserID", UsernameTextBox.Text)
            .AddWithValue("@Password", PasswordTextBox.Text)
        End With

[aldrean]

Ok, forgot to tell you, on the TableAdapter Configuration wizard you can set there the parameter by for instance, on the UserName field there is a Filter field there, try to have it with =@UserName, same with the Password field try to have it with =@Password.

Executing the query will prompt you to supply both parameters and if your values are correct meaning they are in the table, the query will return you with values.

[bagstoper]

yes but how do i do that at runtime?

[aldrean]

May I ask what happened to your query, I mean in the DataAdapter does it return you with values upon supplying with the right username and password?I yes then you can start modifying the code given by Jm.Remember, I just use the adapter to check if your problems deals with the parameter or something.Although you can still use the table adapter, but making an execute scalar is way simple compared to having a Fill method for your adapter.

[bagstoper]

ok it might work but once i have built the table adapter with the command in it how do i call the command? this might work so far it looks like a sub that would have overloads. ScalarQuery (@UserID, @Password)

[svibuk]

hi
use a loop to check thr' all the records in the table

[bagstoper]

i had thought about that but how would i index the loop ( i know how to make a loop but not for this)

[svibuk]

wht excatly u need? u want that when a user logins his username & password both shld match from the database & if both correct shld be able to login.
then use a loop to check thr' the existing records. if both matches then u can login

[bagstoper]

my point is i cant do a datareader.read(i) where I is the record. i have tried that. that was my first idea.

[bagstoper]

i tried to change my data base to sql express but i cant seem to get it to connect becuase it requires me to set it up for remote connections (which i did) but for some reason it isnt working. it is giving me this error.

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

[bagstoper]

ok nevermind this thread is resolved. this is the working code.

         Dim ConnString As String = "Driver={MySQL ODBC 3.51 Driver};Server=dansoft.selfip.com;Option=131072;Stmt=;Database=elemental_battle; User=****;Password=****;"
        Dim objConn As New OdbcConnection(ConnString)

        objConn.Open()

        Dim sqlComm As New OdbcCommand("SELECT username,password from userinfo", objConn)
        Dim dr As OdbcDataReader = sqlComm.ExecuteReader

        Do While dr.Read
            Dim user As String = CStr(dr("username"))
            Dim pass As String = CStr(dr("password"))
            If user = UsernameTextBox.Text And pass = PasswordTextBox.Text Then
                frmMain.Show()
                Me.Close()
                Exit Sub
            End If
        Loop
        MsgBox("Invalid Username or password, Please try again")
        objConn.Close()