locked
how to auto insert or update datagridview without filling all cells in arow RRS feed

  • Question

  • I have a datagridview where it has  columns where the first columns is the subject column in readonly mode and fifth column is the total column in readonly mode.When i insert numbers in column 2,3,4 should give up the total the fifth column.My datgridview is doing that without any problem but the problem is that when when i key in numbers in column 2 and 3 no information inserted unless i key in column 2, 3 and 4 the information will inserted.What i want is to key in in column 2 and 3 and information is inserted.Please help me where possible.

      For Each row As DataGridViewRow In DataGridView1.Rows

                If cmbexam.Text = "CAT 1" Then

                    sqlSTR = "SELECT ID, Subject, SubjNo, Class, Exam, OtherExam, Term, CAT1, CAT2, CAT3, ENDTERM, TOTAL, PAPER1, PAPER2, PAPER3, TOTAL2  FROM  TBL_ResultsProcessing  WHERE (Subject ='" & row.Cells(0).Value & "') AND (Class = '" & cmbform.Text & "') AND (Exam = '" & cmbexam.Text & "') AND (Term ='" & cmbterm.Text & "')"
                        ExecuteSQLQuery(sqlSTR)

                        If sqlDT.Rows.Count > 0 Then

                            sqlSTR = "UPDATE TBL_ResultsProcessing  SET Subject ='" & row.Cells(0).Value & "', SubjNo ='" & txtsubjno.Text & "', Class ='" & cmbform.Text & "', Exam ='" & cmbexam.Text & "', Term ='" & cmbterm.Text & "', PAPER1 ='" & row.Cells(6).Value & "', PAPER2 ='" & row.Cells(7).Value & "', PAPER3 ='" & row.Cells(8).Value & "', TOTAL2 ='" & row.Cells(9).Value & "'  WHERE          (Subject ='" & row.Cells(0).Value & "') AND (Class = '" & cmbform.Text & "') AND (Exam = '" & cmbexam.Text & "') AND (Term ='" & cmbterm.Text & "')"
                            ExecuteSQLQuery(sqlSTR)

                        Else

                            sqlSTR = "INSERT INTO TBL_ResultsProcessing (Subject, SubjNo, Class, Exam, Term, PAPER1, PAPER2, PAPER3, TOTAL2) VALUES ('" & row.Cells(0).Value & "','" & txtsubjno.Text & "','" & cmbform.Text & "','" & cmbexam.Text & "','" & cmbterm.Text & "','" & row.Cells(6).Value & "','" & row.Cells(7).Value & "','" & row.Cells(8).Value & "','" & row.Cells(9).Value & "')"
                            ExecuteSQLQuery(sqlSTR)

                        End If

                    End If

            Next

    Wednesday, April 29, 2020 9:24 AM

All replies

  • Hello,

    It appears you are not setting the DataSource of the DataGridView, if you were to use a DataTable columns 2,3 and 4 could have default values, 5th column can be calculated using an Expression set on a DataColumn for values in 2,3 and 4.

    Many think using a DataTable as per above is data binding to a database, this is false, a DataTable knows nothing about a database.

    Rather than iterate the DataGridView as done currently you could iterate the underlying DataTable.

    When composing SQL with string concatenation you should use parameters, about the only time to concatenate SQL is when it's done dynamically.

    Suppose for a minute row.Cells(0).Value as an embedded apostrophe, at runtime you'd get an exception thrown. Similarly other data types can be trouble also the way you are writing SQL.

    SET Subject ='" & row.Cells(0).Value & "', SubjNo ='"


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, April 29, 2020 11:42 AM