prowebinteractive
asked on
get value from database using VB and Mysql
need some sample code to get single values from a query
Do you have the query written already?
ASKER
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.MyS qlConnecti on
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.MyS qlExceptio n
MessageBox.Show(ex.Message )
End Try
Dim SqlString As String = "SELECT * FROM sysusers where email=textbox1"
Dim Cmd As MySql.Data.MySqlClient.MyS qlCommand = New MySql.Data.MySqlClient.MyS qlCommand( 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 !")
the following is what I have already
Dim conn As New MySql.Data.MySqlClient.MyS
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.MyS
MessageBox.Show(ex.Message
End Try
Dim SqlString As String = "SELECT * FROM sysusers where email=textbox1"
Dim Cmd As MySql.Data.MySqlClient.MyS
'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 !")
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(myConnecti onString)
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.
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(myConnecti
Using cmd as new MySqlCommand(String.Format
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.
ASKER
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
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
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.
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.
ASKER
do i put this in my button click ?
In your button click, put something like
If CheckPassword(textBox1.tex t, textBox2.text) then
Response.Redirect("secure page name")
else
' Do something here to display error message
end if
If CheckPassword(textBox1.tex
Response.Redirect("secure page name")
else
' Do something here to display error message
end if
ASKER
Return (cmd.ExecuteScalar Is System.DBNull.Value) error connection must be valid and open
Yes... which is why the line right before that in my code was conn.Open()
ASKER
i have conn.open()
ASKER
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.MyS qlConnecti on(myConne ctionStrin g)
Using cmd As New MySql.Data.MySqlClient.MyS qlCommand( String.For mat("SELEC T 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.MyS qlExceptio n
MessageBox.Show(ex.Message )
End Try
End Function
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.MyS
Using cmd As New MySql.Data.MySqlClient.MyS
conn.Open()
Return (cmd.ExecuteScalar Is System.DBNull.Value)
End Using
End Using
Catch ex As MySql.Data.MySqlClient.MyS
MessageBox.Show(ex.Message
End Try
End Function
The command object needs the connection:
Using cmd As New MySql.Data.MySqlClient.MyS qlCommand( String.For mat("SELEC T sysUserId FROM sysusers WHERE email='{0}' and sysPasswd='{1}'", userName, password), conn)
Using cmd As New MySql.Data.MySqlClient.MyS
ASKER
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}
what is this doing email='{0}' and sysPasswd='{1}
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>'
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>'
ASKER
but in actual fact shouldnt textbox1.text and textbox2.text go here ?
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.tex t, textBox2.text) then
Response.Redirect("secure page name")
else
' Do something here to display error message
end if
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.tex
Response.Redirect("secure page name")
else
' Do something here to display error message
end if
ASKER
k well for some reason or another Im not able to login...
something is not right somewhere
something is not right somewhere
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
need to compare username and password to users table in my mysql database