Solved

how do you pass query in Visual Basic 2010

Posted on 2010-08-27
7
406 Views
Last Modified: 2013-11-27

Hi

I am trying to implement a simple login form,
but I am unable to pass the query to a mysql table and then check the output with textfiled to see if user typed the same user name and password or not ..

I marked the line with "###########"


Get_user_name = Sql_Get_user.ExecuteNonQuery
            Get_user_password = Sql_Get_password.ExecuteNonQuery
            If Get_user_name = Me.txtUsername.Text Then
                MsgBox("username match")
            Else
                MsgBox("Username invalid")

            End If

but its saying, Username invalid, that means its not getting Data from Mysql TAble.

How will i be able to solved the issue ??

thanks



Imports MySql.Data.MySqlClient

Imports System.Data



Public Class logging

    Dim mySql_cn As MySqlConnection

    Dim myCommand As New MySqlCommand

    Dim myAdapter As New MySqlDataAdapter

    Dim myData As New DataTable

    Dim SQL As String



    

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdQuit.Click

        End

        mySql_cn.Close()

        mySql_cn.Dispose()

        Application.Exit()



    End Sub



    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdlogin.Click

        Dim Sql_Get_user As New MySqlCommand

        Dim Sql_Get_password As New MySqlCommand

        Dim Get_user_name As String

        Dim Get_user_password As String



        Try

            mySql_cn.Open()

            Sql_Get_user.Connection = mySql_cn

            Sql_Get_password.Connection = mySql_cn

#################################################################################################

            Sql_Get_user.CommandText = "SELECT username FROM userinfo WHERE  username = @Username"

            Sql_Get_password.CommandText = "SELECT password from userinfo where password= @Password"

            Sql_Get_user.Parameters.AddWithValue("@Username", txtUsername.Text)

            Sql_Get_password.Parameters.AddWithValue("@Password", txtpassword.Text)



            Get_user_name = Sql_Get_user.ExecuteNonQuery

            Get_user_password = Sql_Get_password.ExecuteNonQuery

            If Get_user_name = Me.txtUsername.Text Then

                MsgBox("username match")

            Else

                MsgBox("Username invalid")



            End If

##########################################################################################

        Catch myerror As MySqlException

            MessageBox.Show("Unable to read From Tables : " & myerror.Message)

        Finally

            If mySql_cn.State <> ConnectionState.Closed Then mySql_cn.Close()



        End Try





        'frmmain.Show()

        'Me.Hide()

    End Sub



    Private Sub logging_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        mySql_cn = New MySqlConnection()

        mySql_cn.ConnectionString = "Database=xxx;" & _

                                "Data Source=192.162.134.67;" & _

                                "User Id=xxx;Password=xxxx"

        

        

    End Sub

    



End Class

Open in new window

login.GIF
0
Comment
Question by:fosiul01
  • 3
  • 2
  • 2
7 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 350 total points
ID: 33539801
Can you try executeScalar instead and see what is in the string get_user_name

Get_user_name = Sql_Get_user.executeScalar

ExecuteScalar: Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored

ExecuteNonQuery: Executes a Transact-SQL statement against the connection and returns the number of rows affected.
0
 
LVL 10

Assisted Solution

by:joriszwaenepoel
joriszwaenepoel earned 150 total points
ID: 33539864
ExecuteNonQuery does not return the results for a SELECT statement, you should use ExecuteScalar.

But, I don't think you code is correct.  You search if the username exists, then you search if the password exists.  You don't verify if the password belongs to that user.

You could just test if a record exists with that username and password
SELECT COUNT(*) FROM userinfo WHERE username = @username AND password = @password

If this returns 0 then the combination of username and password is invalid, if it returns 1, then it is valid.

Also, you should consider to encrypt the password, and verify if the encrypted password = the encryption of password provided at the login screen.  You don't even need to be able to decrypt it, so you could use the .GetHashCode method for example.  
0
 
LVL 29

Author Comment

by:fosiul01
ID: 33539923
HI thanks both of you.

yes Executescalar fixed the issue..

just one more little question..


how will i use conditions to check both user name and password ...



Sql_Get_user.CommandText = "SELECT username,password FROM userinfo WHERE  @username AND password = @password"     : its error i guess
            'Sql_Get_password.CommandText = "SELECT password from userinfo where password= @Password"
            Sql_Get_user.Parameters.AddWithValue("@Username", txtUsername.Text)
            Sql_Get_password.Parameters.AddWithValue("@Password", txtpassword.Text)

            Get_user_name = Sql_Get_user.ExecuteScalar
            Get_user_password = Sql_Get_password.ExecuteScalar
           If (Get_user_name = Me.txtUsername.Text) And (Get_user_password = Me.txtpassword.Text) Then
                MsgBox("username match")
            Else
                MsgBox("Username invalid")

            End If


About the hash and encryption, I will create another question on that issue .

thanks
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 53

Assisted Solution

by:Dhaest
Dhaest earned 350 total points
ID: 33539983
You can perform it offcourse in just one query (like joriszwaenepoel) already suggested

This example below just uses a counter, no additional check on username, because you perform that already on the database in your query !


Sql_Get_user.CommandText = "SELECT count(*) FROM userinfo WHERE  @username AND password = @password"   

Sql_Get_user.Parameters.AddWithValue("@Username", txtUsername.Text)

Sql_Get_user.Parameters.AddWithValue("@Password", txtpassword.Text)



dim counter as integer = Sql_Get_user.ExecuteScalar



If counter > 0 Then

               MsgBox("username match")

           Else

               MsgBox("Username invalid")

           End If

Open in new window

0
 
LVL 29

Author Comment

by:fosiul01
ID: 33540055
yap thats works. thanks!!!

I will create another question on encryption of password
0
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 33540058
What I meant in my previous post is that you don't need to verify the username and/or password in your code, since you already test that in your SQL criteria.

If you user "SELECT COUNT(*)" instead of "SELECT username, password", then all you need to verify is if ExecuteScalar returns 0 or 1 (assuming username will be unique in the database).
0
 
LVL 29

Author Closing Comment

by:fosiul01
ID: 33540060
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now