none
how to save datagridview content to mysql database RRS feed

  • Question

  • how to save datagridview content to mysql database

    here is my DGV:

    im trying to save all including the row number 

    Saturday, February 16, 2019 11:44 AM

Answers

  •    Dim cmd As MySqlCommand
            con.Open()

            cmd = con.CreateCommand
            For i As Integer = 0 To dgvortho.Rows.Count - 2 Step +1
                cmd = New MySqlCommand("INSERT INTO `paymentschedule`(`rec_id`,`patient_id`,`status`,`balance`, `date`) VALUES (@rec_id,@patientid,@status,@balance, @date)", con)
                cmd.Parameters.Add("@rec_id", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells(0).Value.ToString()
                cmd.Parameters.Add("@patientid", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("PatientId").Value.ToString()
                cmd.Parameters.Add("@status", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Status").Value.ToString()
                cmd.Parameters.Add("@balance", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Balance").Value.ToString()
                cmd.Parameters.Add("@date", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Date").Value.ToString()
                cmd.ExecuteNonQuery()
            Next
            MsgBox("All Data Inserted", MsgBoxStyle.Information, "Msg Box")

            con.Close()

    i tried this code but i want to include the row number in the row selector is that possible?

    The row number is the same as iterating the DataGridView row collection e.g.

    For rowIndex As Integer = 0 To DataGridView1.Rows.Count - 1
        If Not DataGridView1.Rows(rowIndex).IsNewRow Then
            Console.WriteLine($"This is the value in the row header {rowIndex + 1}")
        End If
    Next
    rowIndex +1 is the value you want.


    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 no[one] Monday, February 18, 2019 9:01 AM
    Saturday, February 16, 2019 2:25 PM
    Moderator

All replies

  • If the intent is to add, remove and update.

    You would need to field in the database table to remember the row index taken from the DataGridView. You need to consider what happens if the DataGridView is sorted on a column.

    High level, do your add/edit/remove operations to the database table. Next go through all records in the database table and set the field to remember the row index to 0. Now iterate data from the DataGridView, the row index is 0 based, get the primary key for each row, find it in the database table and set the field for remembering the row index to the index of the for/next used to iterate the DataGridView and use it.

    I've done this with the following code sample but it's not MySQL. In the example below MS-Access is used, I later wrote a C# code sample doing the same with SQL-Server, never have done one in MySql.

    https://code.msdn.microsoft.com/Move-rows-updown-and-987fe786


    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

    Saturday, February 16, 2019 1:40 PM
    Moderator
  •    Dim cmd As MySqlCommand
            con.Open()

            cmd = con.CreateCommand
            For i As Integer = 0 To dgvortho.Rows.Count - 2 Step +1
                cmd = New MySqlCommand("INSERT INTO `paymentschedule`(`rec_id`,`patient_id`,`status`,`balance`, `date`) VALUES (@rec_id,@patientid,@status,@balance, @date)", con)
                cmd.Parameters.Add("@rec_id", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells(0).Value.ToString()
                cmd.Parameters.Add("@patientid", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("PatientId").Value.ToString()
                cmd.Parameters.Add("@status", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Status").Value.ToString()
                cmd.Parameters.Add("@balance", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Balance").Value.ToString()
                cmd.Parameters.Add("@date", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Date").Value.ToString()
                cmd.ExecuteNonQuery()
            Next
            MsgBox("All Data Inserted", MsgBoxStyle.Information, "Msg Box")

            con.Close()

    i tried this code but i want to include the row number in the row selector is that possible?

    Saturday, February 16, 2019 1:47 PM
  •    Dim cmd As MySqlCommand
            con.Open()

            cmd = con.CreateCommand
            For i As Integer = 0 To dgvortho.Rows.Count - 2 Step +1
                cmd = New MySqlCommand("INSERT INTO `paymentschedule`(`rec_id`,`patient_id`,`status`,`balance`, `date`) VALUES (@rec_id,@patientid,@status,@balance, @date)", con)
                cmd.Parameters.Add("@rec_id", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells(0).Value.ToString()
                cmd.Parameters.Add("@patientid", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("PatientId").Value.ToString()
                cmd.Parameters.Add("@status", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Status").Value.ToString()
                cmd.Parameters.Add("@balance", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Balance").Value.ToString()
                cmd.Parameters.Add("@date", MySqlDbType.VarChar).Value = dgvortho.Rows(i).Cells("Date").Value.ToString()
                cmd.ExecuteNonQuery()
            Next
            MsgBox("All Data Inserted", MsgBoxStyle.Information, "Msg Box")

            con.Close()

    i tried this code but i want to include the row number in the row selector is that possible?

    The row number is the same as iterating the DataGridView row collection e.g.

    For rowIndex As Integer = 0 To DataGridView1.Rows.Count - 1
        If Not DataGridView1.Rows(rowIndex).IsNewRow Then
            Console.WriteLine($"This is the value in the row header {rowIndex + 1}")
        End If
    Next
    rowIndex +1 is the value you want.


    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 no[one] Monday, February 18, 2019 9:01 AM
    Saturday, February 16, 2019 2:25 PM
    Moderator