none
VB.NET Connect to local MySQL (I'm desperate) RRS feed

  • Question

  • I'm quite desperate. What I'm trying to accomplish is super simple, yet I do not have the required knowledge.

    • What I'm trying to do is to insert data from textboxes into a database by the use of a button click. The purpose of this form is to create users. So there are 4 boxes. Username, Password, Email, IsAdmin (Yes/no) And a button of course called "Create user".
    • I have stored the following code into app.config

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
      <connectionStrings>
    <add name="ConString" connectionString="SERVER=localhost; DATABASE=accountid; UID=root; PASSWORD=" providerName="MySql.Data.MySqlClient" />
    </connectionStrings>
        <configSections>


    • I want to access my database using the connectionString stored in app.config. I don't know the code for this, yet I've tried multiple exampels from different websites. See my fake code below to get the general idea of what I wanna do. I just wrote this thing below just to explain what I want to do, it's really just gibberish.
    Button press
    
    Connect to database using app.config string "constring"
    msgbox "Connection successfull" 'this is to just let me know im connected.
    
    Using constring as new connection
    insert into database table account.text, password.text etc.
    msgbox "Created user"
    
    button press end

    Help me Obi-NET Techforium, you are my only hope.



    //Regards, Andreas


    • Edited by Plankt0n Thursday, July 27, 2017 8:55 AM
    Thursday, July 27, 2017 8:54 AM

Answers

  • It wont be changed, no. The rest of what you wrote, saddly I dont comprehend. But I do thank you for your reply.

    //Regards, Andreas

    See my revised reply with code for reading the connection string. Generally speaking to connection and insert data you would use a code pattern as shown in the following link which is for ms-access but by changing the data provider it will work for SQL-Server or MySql.

    https://social.msdn.microsoft.com/Forums/en-US/17378fcd-52d3-4480-a8b5-90c15ef51257/visual-basic-edit-form-help?forum=vbgeneral


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Plankt0n Thursday, July 27, 2017 9:20 AM
    Thursday, July 27, 2017 9:12 AM
    Moderator

All replies

  • Hello,

    Is the reason for storing the connection string into app.config because you might change it? If not then storing the connection string in app.config is not needed, instead you would simply use the connection string in the property of the connection object by setting the ConnectionString.

    Edit: Here is how you read the connection string.

    Add System.Configuration as a reference to your project then read the connection string via the following into a variable or right in the construction of the connection.

    Configuration.ConfigurationManager.ConnectionStrings("ConString").ConnectionString


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, July 27, 2017 9:02 AM
    Moderator
  • It wont be changed, no. The rest of what you wrote, saddly I dont comprehend. But I do thank you for your reply.

    //Regards, Andreas

    Thursday, July 27, 2017 9:05 AM
  • Sorry for got to mention, the reason I'm storing it in app.config is because I only want to define my connection string once. 

    And then I can call it in all of my forms whenever I need to establish a db con.


    //Regards, Andreas

    Thursday, July 27, 2017 9:08 AM
  • It wont be changed, no. The rest of what you wrote, saddly I dont comprehend. But I do thank you for your reply.

    //Regards, Andreas

    See my revised reply with code for reading the connection string. Generally speaking to connection and insert data you would use a code pattern as shown in the following link which is for ms-access but by changing the data provider it will work for SQL-Server or MySql.

    https://social.msdn.microsoft.com/Forums/en-US/17378fcd-52d3-4480-a8b5-90c15ef51257/visual-basic-edit-form-help?forum=vbgeneral


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Plankt0n Thursday, July 27, 2017 9:20 AM
    Thursday, July 27, 2017 9:12 AM
    Moderator
  • Hello,

    Is the reason for storing the connection string into app.config because you might change it? If not then storing the connection string in app.config is not needed, instead you would simply use the connection string in the property of the connection object by setting the ConnectionString.

    Edit: Here is how you read the connection string.

    Add System.Configuration as a reference to your project then read the connection string via the following into a variable or right in the construction of the connection.

    Configuration.ConfigurationManager.ConnectionStrings("ConString").ConnectionString


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


        Private Sub btncreateuser_Click(sender As Object, e As EventArgs) Handles btncreateuser.Click
    
            Configuration.ConfigurationManager.ConnectionStrings("ConString").ConnectionString
    
    
        End Sub

    How would I use this to insert data into my MySql database?


    //Regards, Andreas

    Thursday, July 27, 2017 9:12 AM
  • It wont be changed, no. The rest of what you wrote, saddly I dont comprehend. But I do thank you for your reply.


    //Regards, Andreas

    See my revised reply with code for reading the connection string. Generally speaking to connection and insert data you would use a code pattern as shown in the following link which is for ms-access but by changing the data provider it will work for SQL-Server or MySql.

    https://social.msdn.microsoft.com/Forums/en-US/17378fcd-52d3-4480-a8b5-90c15ef51257/visual-basic-edit-form-help?forum=vbgeneral


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    That article is to complex for me but I still want to thank you for your assistance and help.

    I'll simply try to google back and forth again. Marking your reply as the answer.


    //Regards, Andreas

    Thursday, July 27, 2017 9:20 AM
  • Okay, the following was put together from a Google search coupled with what I mentioned about altering my code (via the link I supplied).

    I don't have MySql to test this out on, I wrote the code in notepad.

    Either version, you would pass in two string values for the first two parameters and a int value which on success would be set to the new key for the new row.

    Call

    Dim newId As Integer = 0

    AddRow("Karen","pass",newId)

    If AddRow returns True then newId has the new key.

    Using the connection string from app.config

    Public Function AddNewRow(ByVal UserName As String, ByVal UserPassword As String, ByRef Identfier As Integer) As Boolean
         Try
             Using cn As New MySql.Data.MySqlClient.MySqlConnection(Configuration.ConfigurationManager.ConnectionStrings("ConString").ConnectionString)
                 Using cmd As New MySql.Data.MySqlClient.MySqlCommand("", cn)
    
                     cmd.CommandText = "INSERT INTO users (UserName,UserPassword) Values (@UserName,@UserPassword); SELECT LAST_INSERT_ID()"
    
                     cmd.Parameters.AddWithValue("@UserName", UserName.Trim)
                     cmd.Parameters.AddWithValue("@UserPassword", UserPassword.Trim)
    
                     cn.Open()
    
                     Identfier = CInt(cmd.ExecuteScalar())
                 End Using
             End Using
    
         Catch ex As MySqlException
             Success = False
         End Try
    
         Return Success
    
    End Function

    Using the connection string w/o app.config

    Public Function AddNewRow(ByVal UserName As String, ByVal UserPassword As String, ByRef Identfier As Integer) As Boolean
    	Dim myConnectionString as String = "SERVER=localhost; DATABASE=accountid; UID=root; PASSWORD=" 
    
         Try
             Using cn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
                 Using cmd As New MySql.Data.MySqlClient.MySqlCommand("", cn)
    
                     cmd.CommandText = "INSERT INTO users (UserName,UserPassword) Values (@UserName,@UserPassword); SELECT LAST_INSERT_ID()"
    
                     cmd.Parameters.AddWithValue("@UserName", UserName.Trim)
                     cmd.Parameters.AddWithValue("@UserPassword", UserPassword.Trim)
    
                     cn.Open()
    
                     Identfier = CInt(cmd.ExecuteScalar())
                 End Using
             End Using
    
         Catch ex As MySqlException
             Success = False
         End Try
    
         Return Success
    
    End Function
    MySql documentation on LAST_INSERTED_ID used in the code above.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, July 27, 2017 9:54 AM
    Moderator
  • 'Success' is not declared it states, BUT I did also try something myself, it  gives an error, but maybe you can figure it out? 

    First I do is press connect (password is blank) it tells me "Connected"

    Then i fill in data into create user texts, hit create.

    Error when I click create button

    System.InvalidOperationException: 'Connection must be valid and open.'

    Imports System
    Imports System.IO
    Imports System.Text
    Imports MySql.Data.MySqlClient
    Imports System.Configuration
    
    Public Class AdminMain
    
        Private Sub AdminMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            'a
    
        End Sub
    
        Private Sub btnConnectDBCancel_Click(sender As Object, e As EventArgs) Handles btnConnectDBCancel.Click
            Me.Hide()
        End Sub
        Dim MysqlConn As MySqlConnection
        Public Sub btnConnectDB_Click(sender As Object, e As EventArgs) Handles btnConnectDB.Click
    
            MysqlConn = New MySqlConnection()
            MysqlConn.ConnectionString = "server=" & txtConnectDBServer.Text & ";" _
           & "user id=" & txtConnectDBUsername.Text & ";" _
           & "password=" & txtConnectDBPassword.Text & ";" _
           & "database=test"
            Try
                MysqlConn.Open()
                MessageBox.Show("Connection to Database has been opened.")
                MysqlConn.Close()
            Catch myerror As MySqlException
                MessageBox.Show("Cannot connect to database: " & myerror.Message)
                'Finally
                '   MysqlConn.Dispose()
            End Try
        End Sub
    
        Public Sub btncreateuser_Click(sender As Object, e As EventArgs) Handles btncreateuser.Click
            Dim cmd As MySqlCommand
            Dim ra As Integer
            Try
                MysqlConn.Open()
                MessageBox.Show("Yes")
    
                cmd = New MySqlCommand("INSERT INTO accoundid (accountname, accountpassword, accountemail, accountadmin) VALUES ('" + txtnewuseraccountname.Text + "','" + txtnewuseraccountpassword.Text + "','" + txtnewuseraccountemail.Text + "','" + txtnewuseraccountisadmin.Text + "')")
                ra = cmd.ExecuteNonQuery
                MessageBox.Show("Yes 2")
    
                MysqlConn.Close()
            Catch myerror As MySqlException
                MessageBox.Show("Cannot connect to database: " & myerror.Message)
            Finally
                MysqlConn.Dispose()
            End Try
    
    
        End Sub
    End Class
    
    
    


    //Regards, Andreas

    Thursday, July 27, 2017 10:05 AM
  • First off you should not declare the connection until it's needed, this is a "Best Practice".

    I would recommend using the pattern I shows e.g.

    Try
        Using cn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
    	cn.Open()
        End Using
    Catch ex As MySqlException
         MessageBox.Show(ex.Message)
    End Try
    

    The above creates the connection, uses it then disposes of it. If you note in the link I provided I used a connection string builder

    ''' <summary>
    ''' Creates our connection string to the database which is easy to follow
    ''' and there is no string concatenation done here
    ''' </summary>
    ''' <remarks></remarks>
    Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "names.accdb")
        }
    

    Which is far better than creating one via string concatenation. MySql has a connection string builder too found here.

    I can stress enough that from the link I provided that each data provider has the same methods, they are simply in their own namespace e.g. for SQL Server we use System.Data.SqlClient, for ms-access System.Data.OleDb, for MySql MySql.Data.MySqlClient. Beings they all have the same methods and functions means for 99.99% of your code you can take say ms-access code and transform to MySql. The differences are in the actual connection string and the underlying SQL.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, July 27, 2017 10:55 AM
    Moderator