Read and Write SQL information from VB.Net

Question

I have a linked my vb.net project to an only SQL database which holds a list of predefined email addresses. My vb form contains a 'firstname','lastname' and 'email' textbox.

How do I program vb.net to locate the text in the 'email' textbox within the database and add the fistname and lastname textbox values to the appropriate column in the same row as the located email field? (filling the gaps)

My code so far:

' Initiate SQL Connection for db4free.net on port: 3306
    MySqlConnection = New MySqlConnection
    MySqlConnection.ConnectionString = "server=db4free.net; Port=3306; user id=username; password=password; database=databasename"
    Try
        MySqlConnection.Open()
    Catch ex As Exception
        MsgBox("The server could not be reached, check that you have internet connectivity and try again.", MsgBoxStyle.Critical, "Connection Error")
    End Try

        --- SQL DATABASE CONNECTION --- '
        Dim Myadaptor As New MySqlDataAdapter
        Dim sqlquery = "SELECT * FROM registration WHERE email='" & emailTextBox.Text & "';"
        Dim command As New MySqlCommand
        command.Connection = MySqlConnection
        command.CommandText = sqlquery
        Myadaptor.SelectCommand = command
        Dim Mydata As MySqlDataReader
        Mydata = command.ExecuteReader

        ' SQL Entry Validation
        If Mydata.HasRows = 0 Then
            MsgBox("Please enter a valid E-Mail address", MsgBoxStyle.Critical, "Invalid Details")
        Else
           --- THE ANSWER TO MY QUESTION (write firstname and lastname into row based on email) ---

Thank you in advance, (I only just started learning SQL).

So you want to update the database with the new firstname and lastname values? — Daniel Waghorn, Jul 27 '15 at 08:07
As you're just starting to learn - *don't* mangle strings together, learn to use parameters for values passed by a client to avoid a SQL injection vulnerability. — Bridge, Jul 27 '15 at 08:08
@DanielWaghorn Yes, I want to enter an email address into the 'email' textbox, then some random firstname and lastname into the other two textboxes and finally click a button that searches the database for that email and adds the textbox values to the firstname and lastname column the same row in which the found email address is located. — ProGrammer, Jul 27 '15 at 08:15

score 0 · Accepted Answer · edited May 23 '17 at 12:13

0

You'll want to do something like this:

If Mydata.HasRows = 0 Then
    MsgBox("Please enter a valid E-Mail address", MsgBoxStyle.Critical, "Invalid Details")
    Mydata.Close()
Else
    Mydata.Close()
    Dim command As New MySqlCommand
    command.Connection = MySqlConnection
    command.ConnectionText = "UPDATE `registration` SET `firstname` = @firstname,`lastname` = @lastname WHERE `email` = @email"
    command.Prepare()
    command.Parameters.AddWithValue("@firstname", firstnameTextBox.Text)
    command.Parameters.AddWithValue("@lastname", lastnameTextBox.Text)
    command.Parameters.AddWithValue("@email", emailTextBox.Text)
    command.ExecuteNonQuery()
End If

You may want to adjust the SQL or the references to the UI elements to match your setup.

I've also bound the parameters into the SQL rather than creating a string by concatenating in the parameters in order to protect against SQL Injection.

edited May 23 '17 at 12:13

Community

1
1

answered Jul 27 '15 at 08:24

Daniel Waghorn

2,997
2
20
33

Exception: 'There is already an open DataReader associated with this Connection which must be closed first.' and at what point does your code add the firstname and lastname to the same row as the email address (to fill the gap), from what I can tell it simply adds it to a new row, doesn't it? – ProGrammer Jul 27 '15 at 09:25
After `Mydata = command.ExecuteReader` add a line which reads `Mydata.Close()` – Daniel Waghorn Jul 27 '15 at 09:27
Mydata.Close() was originally below the if statement, moving it up to beneath the command.ExcecuteReader will mean that the if statement will not work (If Mydata.HasRows = 0)... And please read the extension on my previous comment. – ProGrammer Jul 27 '15 at 09:49
Apologies, I've updated the SQL and have also placed the close statement where it should work. – Daniel Waghorn Jul 27 '15 at 09:57
The code breaks on the 'command.ExecuteNonQuery()', Error message: "Fatal error encountered during command execution". I have tried a few different things with the code but the message is always the same, "An unhandled exceptions occoured in MySql.Data.dll" and then refers back to the error above. Please help, I have no idea what is going wrong here... – ProGrammer Jul 30 '15 at 09:37
I've added a `Prepare()` to my original answer. Please see if that helps. – Daniel Waghorn Jul 30 '15 at 09:49

Read and Write SQL information from VB.Net

1 Answers1