none
when i fill up details in form and press "save" button it saved in database but does not display into the row of datagridview at same time

    Question

  • Option Explicit On
    Option Strict On
    Imports System.Data
    Imports System.Data.OleDb
    
    Public Class Register
        Dim connection As OleDbConnection
        Dim command As OleDbCommand
        Dim reader As OleDbDataReader
        Dim conStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Admin\documents\visual studio 2010\Projects\WindowsApplication289\WindowsApplication289\form1.accdb"
        Dim query As String
        Private Sub Register_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'Form1DataSet1.reg' table. You can move, or remove it, as needed.
            Me.RegTableAdapter.Fill(Me.Form1DataSet1.reg)
            txtFirstName.Text = ""
            txtLastName.Text = ""
            txtUsername.Text = ""
            txtPassword.Text = ""
            txtConfirmPassword.Text = ""
            txtMobileNo.Text = ""
            txtEmail.Text = ""
            txtFirstName.Focus()
        End Sub
    
        Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
            If Len(Trim(txtFirstName.Text)) = 0 Then
                MessageBox.Show("Please Enter Your FirstName", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtFirstName.Focus()
                Exit Sub
            End If
            If txtFirstName.TextLength > 1 Then
                txtFirstName.Text = txtFirstName.Text.Substring(0, 1).ToUpper() + txtFirstName.Text.Substring(1).ToLower()
            ElseIf txtFirstName.TextLength = 1 Then
                txtFirstName.Text = txtFirstName.Text.ToUpper()
            End If
            If Len(Trim(txtLastName.Text)) = 0 Then
                MessageBox.Show("Please Enter Your LastName", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtLastName.Focus()
                Exit Sub
            End If
            If txtLastName.TextLength > 1 Then
                txtLastName.Text = txtLastName.Text.Substring(0, 1).ToUpper() + txtLastName.Text.Substring(1).ToLower()
            ElseIf txtLastName.TextLength = 1 Then
                txtLastName.Text = txtLastName.Text.ToUpper()
            End If
            If Len(Trim(txtUsername.Text)) = 0 Then
                MessageBox.Show("Please Enter Your UserName To Access Your Account", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtUsername.Focus()
                Exit Sub
            End If
            If txtUsername.TextLength > 1 Then
                txtUsername.Text = txtUsername.Text.Substring(0, 1).ToUpper() + txtUsername.Text.Substring(1).ToLower()
            ElseIf txtUsername.TextLength = 1 Then
                txtUsername.Text = txtUsername.Text.ToUpper()
            End If
            If Len(Trim(txtPassword.Text)) = 0 Then
                MessageBox.Show("Please Enter Your Password For Your Account", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtPassword.Focus()
                Exit Sub
            End If
            If Len(Trim(txtConfirmPassword.Text)) = 0 Then
                MessageBox.Show("Please Confirm Your Password By Re-Typing", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtConfirmPassword.Focus()
                Exit Sub
            End If
            If Len(Trim(txtMobileNo.Text)) = 0 Then
                MessageBox.Show("Please Enter Your Mobile No", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtMobileNo.Focus()
                Exit Sub
            End If
            If Len(Trim(txtEmail.Text)) = 0 Then
                MessageBox.Show("Please Enter Your Email Address", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtEmail.Focus()
                Exit Sub
            End If
            Try
                connection = New OleDbConnection(conStr)
                connection.ConnectionString = conStr
                connection.Open()
                query = "SELECT * from [reg] where [First Name]=@fname AND [Last Name]=@lname AND [Username]=@uname AND [Password]=@pass AND [Confirm password]=@cpass AND [Mobile No]=@mobno AND [Email]=@mail"
                command = New OleDbCommand(query, connection)
                command.Connection = connection
                command.Parameters.AddWithValue("@fname", txtFirstName.Text)
                command.Parameters.AddWithValue("@lname", txtLastName.Text)
                command.Parameters.AddWithValue("@uname", txtUsername.Text)
                command.Parameters.AddWithValue("@pass", txtPassword.Text)
                command.Parameters.AddWithValue("@cpass", txtConfirmPassword.Text)
                command.Parameters.AddWithValue("@mobno", txtMobileNo.Text)
                command.Parameters.AddWithValue("@mail", txtEmail.Text)
                reader = command.ExecuteReader()
                If reader.Read Then
                    MessageBox.Show("Account already exists" & vbNewLine & "Please enter your account", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    txtFirstName.Text = ""
                    txtLastName.Text = ""
                    txtUsername.Text = ""
                    txtPassword.Text = ""
                    txtConfirmPassword.Text = ""
                    txtMobileNo.Text = ""
                    txtEmail.Text = ""
                    txtFirstName.Focus()
                    If Not reader Is Nothing Then
                        reader.Close()
                    End If
                    Exit Sub
                End If
                connection = New OleDbConnection(conStr)
                connection.ConnectionString = conStr
                connection.Open()
                query = "insert into [reg]([First Name],[Last Name],[Username],[Password],[Confirm Password],[Mobile No],[Email]) values('" & txtFirstName.Text & "','" & txtLastName.Text & "','" & txtUsername.Text & "','" & txtPassword.Text & "','" & txtConfirmPassword.Text & "','" & txtMobileNo.Text & "','" & txtEmail.Text & "')"
                command = New OleDbCommand(query, connection)
                command.ExecuteNonQuery()
                MessageBox.Show("Data Saved Successfully", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
                command.Dispose()
                connection.Close()
                Me.Hide()
                Form1.Show()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, MsgBoxResult.Ok)
            End Try
            DataGridView1.Rows.Add("txtFirstName.Text", "txtLastName.Text", "txtUsername.Text", "txtPassword.Text", "txtConfirmPassword.Text", "txtMobileNo.Text", "txtEmail.Text")
        End Sub
    
        Private Sub btnClear_Click(sender As System.Object, e As System.EventArgs) Handles btnClear.Click
            txtFirstName.Text = ""
            txtLastName.Text = ""
            txtUsername.Text = ""
            txtPassword.Text = ""
            txtConfirmPassword.Text = ""
            txtMobileNo.Text = ""
            txtEmail.Text = ""
            txtFirstName.Focus()
        End Sub
    
        Private Sub btnExit_Click(sender As System.Object, e As System.EventArgs) Handles btnExit.Click
            Application.Exit()
        End Sub
    
        Private Sub txtEmail_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles txtEmail.Validating
            Dim rEMail As New System.Text.RegularExpressions.Regex("^[a-zA-Z][\w\.-]{2,28}[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$")
            If txtEmail.Text.Length > 0 Then
                If Not rEMail.IsMatch(txtEmail.Text) Then
                    MessageBox.Show("invalid email address", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
                    txtEmail.SelectAll()
                    e.Cancel = True
                End If
            End If
        End Sub
    
        Private Sub DataGridView1_CellMouseClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick
            On Error Resume Next
            txtFirstName.Text = CStr(DataGridView1.Rows(e.RowIndex).Cells(0).Value)
            txtLastName.Text = CStr(DataGridView1.Rows(e.RowIndex).Cells(1).Value)
            txtUsername.Text = CStr(DataGridView1.Rows(e.RowIndex).Cells(2).Value)
            txtPassword.Text = CStr(DataGridView1.Rows(e.RowIndex).Cells(3).Value)
            txtConfirmPassword.Text = CStr(DataGridView1.Rows(e.RowIndex).Cells(4).Value)
            txtMobileNo.Text = CStr(DataGridView1.Rows(e.RowIndex).Cells(5).Value)
            txtEmail.Text = CStr(DataGridView1.Rows(e.RowIndex).Cells(6).Value)
        End Sub
    End Class

    Friday, February 9, 2018 3:54 PM

All replies

  • I would recommend using a BindingSource because it greatly simplifies the process of binding controls to an underlying data source. 

    Your code doesn't show much about your DataGridView. To add a row manually to your DataGridViewRowCollection it is required that your DataGridView has as many DataGridViewTextBoxColumn instances as values in the object array parameter of your Add(object[]) method.

    wizend

    Friday, February 9, 2018 6:23 PM
  • I see one problem, you appear to be relying on a TableAdapter to read data from your database while discarding the TableAdapter in favor of using OleDb for the INSERT. This can be done be makes zero sense to go between TableAdapter and OleDb. The other issue is you are adding data to the DataGridView via the row collection which if you are loading data via a TableAdapter to populate the DataGridView (which would also be using a BindingSource) will throw a run time exception yet you have not indicated this.

    There are much better ways to do this if interested see the following code sample.

    https://code.msdn.microsoft.com/CRUD-data-operations-for-4783d8dd?redir=0


    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


    Friday, February 9, 2018 11:12 PM
  • Hi Deeps,

    I noticed there is a line code named "TableAdapter.Fill()" in the Form_Load event, so I guess you bound a data source to the datagridview with the arrow in the upper right corner, right?

    In this case, if you want to insert a record, I suggest you do not use ADO.NET to do it, instead of(C#):

            private void btnInsert_Click(object sender, EventArgs e)
            {
                DataRow dr = dBPersonDataSet.T_BindingSourceTest.NewRow();
                dr["Name"] = textBox1.Text;
                dr["Age"] = Convert.ToInt32(textBox2.Text);
                dr["Gender"] = textBox3.Text;
                dBPersonDataSet.T_BindingSourceTest.Rows.InsertAt(dr, tBindingSource.Position);
                tBindingSource.EndEdit();
                t_TableAdapter.Update(dBPersonDataSet.T_BindingSourceTest);
            }

    Or in your code with ADO.NET, if you want the new record to display to the datagridview in real time, just do it with:

    connection = New OleDbConnection(conStr) connection.ConnectionString = conStr connection.Open() query = "insert into [reg]([First Name],[Last Name],[Username],[Password],[Confirm Password],[Mobile No],[Email]) values('" & txtFirstName.Text & "','" & txtLastName.Text & "','" & txtUsername.Text & "','" & txtPassword.Text & "','" & txtConfirmPassword.Text & "','" & txtMobileNo.Text & "','" & txtEmail.Text & "')" command = New OleDbCommand(query, connection) command.ExecuteNonQuery() MessageBox.Show("Data Saved Successfully", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information) command.Dispose() connection.Close()

    'Add this line Me.RegTableAdapter.Fill(Me.Form1DataSet1.reg)

    Me.Hide()
                Form1
    .Show()

    Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 12, 2018 6:06 AM
    Moderator
  • As everyone here has already said, you should be using a BindingSource. I think that what you need to do is use that BindingSource to databind both the grid *and* the TextBoxes:

    Dim bsReg As new BindingSource()

    You can set it up in the Register_Load():

    Me.RegTableAdapter.Fill(Me.Form1DataSet1.reg)
    bsReg.DataSource = Me.Form1DataSet1.reg
     
    ' then bind the grid and all the TextBoxes
    DataGridView1.DataSource = bsReg
    txtFirstName.DataBindings.Add("Text", bsReg, "First Name")
    txtLastName.DataBindings.Add("Text", bsReg, "Last Name")
    txtUsername.DataBindings.Add("Text", bsReg, "Username")
    txtPassword.DataBindings.Add("Text", bsReg, "Password")
    txtConfirmPassword.DataBindings.Add("Text", bsReg, "Confirm Password")
    txtMobileNo.DataBindings.Add("Text", bsReg, "Mobile No")
    txtEmail.DataBindings.Add("Text", bsReg, "Email")
    txtFirstName.Focus()
    

    In this way, you won't need to set the TextBoxes in any DataGridView1 events (like the one you already have for CellMouseClick, you won't need that anymore) because the TextBoxes and Grid are databound to the same DataTable with the bsReg BindingSource.

    Your btnClear_Click would probably be reduced to something simple like this:

    Dim row = dtGlobal.Rows(bsReg.Position)
    For Each col As DataColumn in dtGlobal.Columns
        row[col.ColumnName] = ""
    Next         

    Anyway, I hope you get the point ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, February 12, 2018 3:39 PM
  • Your code looks much to old VB6 style code with the MS FlexGrid where you have added a dataadapter to make your presentation more easy. 

    Others have written a lot already, but start to go away from that VB6 style. Separate the data handling from the presentation. 

    You as well can then use a TableAdapterManager which you add to a component class (Project -> Add Items -> Component Class) 

    But start with reading about the TableAdapterManager, because you can remove almost 60% of your code and then add new lines to do it more on a current way of coding. 

    https://msdn.microsoft.com/en-us/library/bb384426.aspx 


    Success Cor

    Monday, February 12, 2018 4:28 PM
  • Hi Deeps,

    Have you solved this problem now?

    I think the above replies can provide you with a solution, have you tried them?

    If so, please close this thread by marking the helpful reply as answer as this will help others looking for the same or similar issues down the road.

    Best Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 26, 2018 2:31 AM
    Moderator