0

I created two table parent and child.

Cus: Parent

+----+-------------------------+------+
| ID |          Name           | Age  |
+----+-------------------------+------+
|  1 | Japhet                  | 22   |
|  2 | Abegail                 | 31   |
|  3 | Norlee                  | 35   |
|  4 | Pacita                  | 60   |
|  5 | Reynaldo                | 65   |
|  6 | Barro, Reynaldo Batucan | 65   |
|  7 | Batucan, Japhet C.      | NULL |
|  8 | Barro, Reynaldo B.      | NULL |
+----+-------------------------+------+

Cus2: Child

+-----+----+------+---------+
| QID | ID | Name | Country |
+-----+----+------+---------+
|     |    |      |         |
+-----+----+------+---------+

I've used the codes below to populate the datagridview with the custom rows, but when I attempt to save it, it won't save. I get this error message:

Input string was not in correct format. Couldn't store in ID Column. Expected type is Int32.

Also, the Cus.ID will not populate in Cus2.ID

Private Sub Populate()
        Dim dt As New DataTable()
        dt.Columns.AddRange(New DataColumn(3) {New DataColumn("QID", GetType(Integer)), New DataColumn("ID", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
        Try
            dt.Rows.Add(1, 0, "John Hammond", "United States")
            dt.Rows.Add(2, 0, "Mudassar Khan", "India")
            dt.Rows.Add(3, 0, "Suzanne Mathews", "France")
            dt.Rows.Add(4, 0, "Robert Schidner", "Russia")

            Me.Cus2DataGridView.DataSource = dt
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Populate()
    End Sub

    Private Sub Cus2DataGridView_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles Cus2DataGridView.CellContentClick

    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        For Each row As DataGridViewRow In Cus2DataGridView.Rows
            Dim constring As String = "Data Source=DESKTOP-0M1930H\PNJK;Initial Catalog=Hello;Integrated Security=True"
            Using con As New SqlConnection(constring)
                Using cmd As New SqlCommand("INSERT INTO Cus2 VALUES(@QID, @ID, @Name, @Country)", con)
                    Try
                        cmd.Parameters.AddWithValue("@QID", row.Cells("QID").Value)
                        cmd.Parameters.AddWithValue("@ID", row.Cells("ID").Value)
                        cmd.Parameters.AddWithValue("@Name", row.Cells("Name").Value)
                        cmd.Parameters.AddWithValue("@Country", row.Cells("Country").Value)
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
                    Catch ex As Exception

                    End Try
                End Using
            End Using
        Next
    End Sub

I am new to this, so please help. Thanks :D

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • My general advice: Do away with the comma separated representation of parent-child relationships. Instead, have one column with a parent and one column with a child. – Tim Biegeleisen Jul 12 '16 at 01:11
  • "when I attempt to save it, it won't save". What does that actually mean? EXACTLY what actually happens? – jmcilhinney Jul 12 '16 at 01:15
  • @jmcilhinney some pop-up will show: http://prntscr.com/brr4g5 – Japhet Batucan Jul 12 '16 at 01:18
  • @Plutonix Yes, but I don't understand what it is saying. Still new to this. – Japhet Batucan Jul 12 '16 at 01:22
  • Specify the column names in the SQL - you are assuming the DB has the columns in the same exact order as you are specifying the values in - it apparently does not. see http://www.tutorialspoint.com/sql/ You could also insert all the rows on the same connection, and same command object – Ňɏssa Pøngjǣrdenlarp Jul 12 '16 at 01:26
  • 1
    Assuming you are using sql server 2008 or later (and you should), you should use a [table valued parameter](http://stackoverflow.com/questions/31965233/adding-multiple-parameterized-variables-to-a-database-in-c-sharp/31965525#31965525) instead of inserting records one by one. – Zohar Peled Jul 12 '16 at 05:19

0 Answers0