Insert all rows in datagridview to one row in database

Question

I'm working with my project and i want to save all the records in my datagridview in one id but the resultt, only one row inserted in my database

table picture as you can see, there are two rows, but when I click submit only one row will save to my database.

database picture

This is my code in submit button

        Dim p_id As Integer
        Dim p_description As String = Nothing
        Dim p_from As String = Nothing
        Dim p_to As String = Nothing
        Dim table As New DataTable
        For i As Integer = 0 To DataGridView2.Rows.Count = 1

            p_id = DataGridView2.Rows(i).Cells(0).Value
            p_description = DataGridView2.Rows(i).Cells(1).Value
            p_from = DataGridView2.Rows(i).Cells(2).Value
            p_to = DataGridView2.Rows(i).Cells(3).Value


        Next
'sql insert query'

sql_query = sql_query = "INSERT INTO Petition_tbl(p_id,p_name_of_petitioner,p_item_no,p_description,p_from,p_to)VALUES('"petition_id.Text.Trim"''" & petitioner_name.Text.Trim & "','" & p_id & "','" & p_description & "','" & p_from & "','" & p_to & "')"

Anyone can help me, will be appreciated.

Where exactly do you execute your SQL query? Are you only executing *one* `INSERT` statement with *one* set of values, *after* your loop? If you execute *one* `INSERT` statement with *one* set of values then that will insert *one* row. Maybe you meant to execute it inside the loop? — David, Mar 08 '23 at 21:48
i want to insert multiple values in table(datagridview) in one column which is id,description,from and to columns. sir — Shigeo, Mar 08 '23 at 21:51
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Mar 08 '23 at 21:53
@Shigeo: What do you mean by "insert multiple values in one column"? Please update the question to indicate the intended result you are trying to achieve, and how *specifically* you are trying to achieve that result. — David, Mar 08 '23 at 21:54
Obligatory reference to [Exploits of a Mom](https://xkcd.com/327/). Even VB.NET can use `SqlCommand`s with strongly typed parameters, [Example](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types#example). — AlwaysLearning, Mar 08 '23 at 22:12
Honestly, not really 100% sure you are really understanding how relation databases should work, but maybe you've got good reason to do what you're doing. Related data should be stored in seperate records, then "flattened" if needed when consumed by whatever front end/client for presentation. — Hursey, Mar 08 '23 at 22:16
If this is ACTUALLY what you want to do, you will need some algorithm to combine data into a single field, no one here can help based of this info. Some strategies might be a delimited list, or data serializations (eg. xml/Json) — Hursey, Mar 08 '23 at 22:16
https://meta.stackoverflow.com/questions/285551/why-should-i-not-upload-images-of-code-data-errors#:~:text=Images%20are%20harder%20to%20read,actual%20code%20and%20formatting%20it. — Sean Lange, Mar 09 '23 at 06:07
https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements — Sean Lange, Mar 09 '23 at 06:08

score 0 · Answer 1 · answered Mar 17 '23 at 02:53

Here is code that puts rows of data in a DataGridView (dgvTX) that is not bound to the data source in this case a SQLite DB

                Using rdr As SQLite.SQLiteDataReader = cmd.ExecuteReader
                'dgvTX.DataSource = rdr
                'Statement Above is when DB is bound to dgvTX
                '============================================

                While rdr.Read()
                    intID = CInt((rdr("TID")))
                    strDate = rdr("txSortDate").ToString
                    strTxType = rdr("txType").ToString
                    strAmt = CDec(rdr("txAmount"))
                    strCKNum = rdr("txCKNum").ToString
                    strDesc = rdr("txDesc").ToString
                    strBal = CDec(rdr("txBalance"))
                    dgvTX.Columns(3).DefaultCellStyle.Format = "N"
                    dgvTX.Columns(6).DefaultCellStyle.Format = "N"
                    'dgvTX.Columns(6).DefaultCellStyle.Format = "C"'Adds the $ sign and commas
                    dgvTX.Rows.Add(intID, strDate, strTxType, strAmt, strCKNum, strDesc, strBal, emptyStr)

                    rowCount = rowCount + 1

                End While

Insert all rows in datagridview to one row in database

1 Answers1