• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

how do you pass query in Visual Basic 2010


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
fosiul01
Asked:
fosiul01
  • 3
  • 2
  • 2
3 Solutions
 
DhaestCommented:
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
 
joriszwaenepoelCommented:
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
 
fosiul01Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
DhaestCommented:
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
 
fosiul01Author Commented:
yap thats works. thanks!!!

I will create another question on encryption of password
0
 
joriszwaenepoelCommented:
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
 
fosiul01Author Commented:
thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now