Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

get value from database using VB and Mysql

Posted on 2006-11-28
19
Medium Priority
?
308 Views
Last Modified: 2008-02-01
need some sample code to get single values from a query
0
Comment
Question by:prowebinteractive
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 

Author Comment

by:prowebinteractive
ID: 18031171
creating login screen

need to compare username and password to users table in my mysql database
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18032101
Do you have the query written already?
0
 

Author Comment

by:prowebinteractive
ID: 18036742
yes my query is writen, I just need to get the fields from the database
the following is what I have already


        Dim conn As New MySql.Data.MySqlClient.MySqlConnection
        Dim myConnectionString As String

        myConnectionString = "server=127.0.0.1;" _
                    & "uid=user;" _
                    & "pwd=password;" _
                    & "database=databaseName;"

        Try
            conn.ConnectionString = myConnectionString
            conn.Open()

        Catch ex As MySql.Data.MySqlClient.MySqlException
            MessageBox.Show(ex.Message)
        End Try


        Dim SqlString As String = "SELECT * FROM sysusers where email=textbox1"
        Dim Cmd As MySql.Data.MySqlClient.MySqlCommand = New MySql.Data.MySqlClient.MySqlCommand(SqlString)

        'MessageBox.Show()

        'Dim Cmd As OdbcCommand = New OdbcCommand(SqlString)


        'Dim myEmail As myEmail = "admin"
        If TextBox1.Text = "admin" And TextBox2.Text = "pass" Then
            Me.Hide()
            Form2.Show()
            'MessageBox.Show("your in !")
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18037458
1. You need to look up "SQL injection attack"... your site is vulnerable to this type of attack.
2. I'd take the approach of checking for the existence of the user, like this:

Function CheckPassword(userName as string, password as string) as boolean
Dim myConnectionString As String = "server=127.0.0.1;" _
                    & "uid=user;" _
                    & "pwd=password;" _
                    & "database=databaseName;"

Using conn as new MySqlConnection(myConnectionString)
Using cmd as new MySqlCommand(String.Format("SELECT UserId FROM sysusers WHERE email='{0}' and password='{1}'", userName, password))
conn.open()
return (cmd.ExecuteScalar Is System.DBNULL.Value)
end using
end using
End function

This will return TRUE is a user exists who has the username and password you provide (you might have to tweak the field names in the query).

3. Consider changeing your control names to something useful.
0
 

Author Comment

by:prowebinteractive
ID: 18039267
this was meant to connect to my database

Dim myConnectionString As String = "server=127.0.0.1;" _
                    & "uid=user;" _
                    & "pwd=password;" _
                    & "database=databaseName;"

I have the query alreadt done, what I need is to compare the infomation give in the password box and the info in the database

my original application is done in php on a linux server and this is the main reason why I need to keep the mysql server

my sysusers table is on a remote server
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18039300
If you use the query I provided above, you do all the comparison on the database server. You connect to the database, check for the username/password pair and return true if it exists; otherwise false is returned. There's a minor typo -- it should be
return NOT (cmd.ExecuteScalar Is System.DBNULL.Value)

All you have to do is provide the function above with the username/password pair you want to check -- and make sure I named the fields right.

If this is a production app, you REALLY need to look at SQL injection. Your login page (the most public-facing page on your site) will allow malicious users to take over your site without a whole lot of effort.
0
 

Author Comment

by:prowebinteractive
ID: 18039397
do i put this in my button click ?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18039898
In your button click, put something like

If CheckPassword(textBox1.text, textBox2.text) then
  Response.Redirect("secure page name")
else
  ' Do something here to display error message
end if
0
 

Author Comment

by:prowebinteractive
ID: 18041469
Return (cmd.ExecuteScalar Is System.DBNull.Value)   error connection must be valid and open
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18041788
Yes... which is why the line right before that in my code was conn.Open()
0
 

Author Comment

by:prowebinteractive
ID: 18045156
i have conn.open()
0
 

Author Comment

by:prowebinteractive
ID: 18045166
copy and paste of what I have


    Function CheckPassword(ByVal userName As String, ByVal password As String) As Boolean

        Dim myConnectionString As String = "server=127.0.0.1;" _
                            & "uid=bdipasq_bruno;" _
                            & "pwd=bd4321le;" _
                            & "database=bdipasq;"
        Try
            Using conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
                Using cmd As New MySql.Data.MySqlClient.MySqlCommand(String.Format("SELECT sysUserId FROM sysusers WHERE email='{0}' and sysPasswd='{1}'", userName, password))
                    conn.Open()
                    Return (cmd.ExecuteScalar Is System.DBNull.Value)
                End Using
            End Using
        Catch ex As MySql.Data.MySqlClient.MySqlException
            MessageBox.Show(ex.Message)
        End Try

    End Function
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18045970
The command object needs the connection:

Using cmd As New MySql.Data.MySqlClient.MySqlCommand(String.Format("SELECT sysUserId FROM sysusers WHERE email='{0}' and sysPasswd='{1}'", userName, password), conn)
0
 

Author Comment

by:prowebinteractive
ID: 18046135
ok connection being made but Im entering the right information to login and Im not getting in

what is this doing   email='{0}' and sysPasswd='{1}
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18046176
String.Format replaces each argument in {} with the arguments that follow... so
String.Format("SELECT sysUserId FROM sysusers WHERE email='{0}' and sysPasswd='{1}'", userName, password)
returns
SELECT sysUserId FROM sysusers WHERE email='<contents of userName variable>' and sysPasswd='<contents of password variable>'
0
 

Author Comment

by:prowebinteractive
ID: 18046202
but in actual fact shouldnt textbox1.text   and textbox2.text go here ?
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 18046252
Sort of...

This is a function...declared as:  Function CheckPassword(ByVal userName As String, ByVal password As String) As Boolean

This means that you pass it textBox1.Text and textBox2.Text as arguments. (Again, I encourage you to give these controls meaningful names.)

Like this:
If CheckPassword(textBox1.text, textBox2.text) then
  Response.Redirect("secure page name")
else
  ' Do something here to display error message
end if
0
 

Author Comment

by:prowebinteractive
ID: 18046303
k well for some reason or another Im not able to login...

something is not right somewhere
0
 
LVL 24

Accepted Solution

by:
Jeff Certain earned 2000 total points
ID: 18046325
Put a break-point on conn.Open()

This will let you run cmd.ExecuteScalar manually (using the watch window) to see what it returns, as well as checking that the right user name and password made it through to the function.

You also need to put the NOT in the line below, as I mentioned previously:
Return NOT (cmd.ExecuteScalar Is System.DBNull.Value)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

662 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