How can I insert records in different columns from multiple textboxes in a MySQL table?

Question

How can I add records from multiple text boxes in a VB.net WinForm, to a remote MySQL database? I want to insert the value in the textboxes (fname, lname) to the corresponding columns in a mySQL table. Only one textbox is visible at a time. The next textbox becomes visible only when the previous one has been submitted. I'm using the following code for entering a single record, but am unable to use it for multiple columns.

Imports MySql.Data.MySqlClient


Public Class Regform
Dim serverstring As String = "server=***;Uid=****;database=****;password=****"
Dim SQLstate As String
Dim firstn As String
Dim SQLconnection As MySqlConnection = New MySqlConnection


Private Sub Regform_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    SQLconnection.ConnectionString = serverstring

    Try
        If SQLconnection.State = ConnectionState.Closed Then
            SQLconnection.Open()
            MsgBox("Connection Established")
        Else
            SQLconnection.Close()
            MsgBox("Connection is Closed")
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
    text2.Text = "Well, Hello " & firstn & "Please give me your last name"
End Sub

Public Sub savename(ByRef SQLstatement As String)
    Dim cmd As MySqlCommand = New MySqlCommand
    With cmd
        .CommandText = SQLstatement
        .CommandType = CommandType.Text
        .Connection = SQLconnection
        .ExecuteNonQuery()

    End With

    SQLconnection.Close()
    MsgBox("Added")
    SQLconnection.Dispose()

End Sub

Private Sub cmdsubmit_Click(sender As Object, e As EventArgs) Handles cmdsubmit.Click
    SQLstate = "INSERT INTO users(fname) VALUES('" & fname.Text & "')"

    savename(SQLstate)
    text1.Visible = False
    text2.Visible = True
    fname.Visible = False
    '        lname.visible = True

End Sub

Private Sub fname_TextChanged(sender As Object, e As EventArgs) Handles fname.TextChanged
    firstn = fname.Text
End Sub
End Class

score 0 · Answer 1 · edited May 23 '17 at 11:49

Can you store the first name in a variable locally until you have the last name? (you already seem to have Dim firstn As String)

firstn = fname.Text

then use something like:

"INSERT INTO users(fname,lname) VALUES('" & firstn & "','" & lname.Text & "')"

If not using this approach or you for this or other reasons want to select back out the ID of inserted rows so that you can update them then use a technique like this:

Return Last ID (IDENTITY) On Insert row VB.NET MySQL

BTW i would not select back out of DB based on first name alone.. they are not very unique ;)

James Stafford · Answer 2 · 2014-01-18T04:23:55.233

I would not recommend your approach, or rather I may suggest transferring the data from your first form to your second form (assuming they are not on the same form) before submitting the data to the mysql database

create a global declared variable for both forms

Public indform1 As form1
Public indform2 As form2

when you call the form

        indform1 = New form1
        indform1.Owner = Me
        indform1.MdiParent = Me
        indform1.Show()

same with form2 - this will allow you to transfer data more easily.

when you create form2 either create a local declared string

dim fname as new string = indform1.fname.text

and use the variable fname to say "hello...."

once they push your submit button for the second form have it run your insert statement

INSERT INTO users(fname,lname) VALUES('" & fname & "','" & lname.Text & "')

or your other option would be to run an update statement after you receive the last name which I would not recommend because as the other responder stated the first name is not very unique unless you add a unique id to it also.

otherwise if it is going to be on the same form and you are only dealing with object visibility - you can use

INSERT INTO users(fname,lname) VALUES('" & fname.text & "','" & lname.Text & "')

making an object visible=false does not modify the content

How can I insert records in different columns from multiple textboxes in a MySQL table?

2 Answers2