DataGridView WinForms Auto Update / Update / Update

Question

I have a window form with a DataGridView control.

I linked it with the attached DB file (.mdf).

I am performing insertion by creating a dynamic Insert statement. Then I upload this sql statement to the SqlCommand object and execute the ExecuteNonQuery() method. All this is done by handling the Button click event. The button and the grid are visible in the same form.

 Public Sub InsertRow(ByVal param1 As String, ByVal param2 As String, ByVal param3 As String) Dim strConn As String = (the connection string) Dim sqlConn As New SqlConnection(strConn) Dim insertSQL As String = "INSERT INTO theTable VALUES ('" + param1 + "', '" + param2 + "', '" + param3 + "', '" + DateTime.Now + "', '" + DateTime.Now + "')" Dim comm As New SqlCommand(insertSQL, sqlConn) sqlConn.Open() comm.ExecuteNonQuery() sqlConn.Close() End Sub Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click InsertRow("a","b","c") End Sub 

After executing the code, the DataGridView not updated (remains intact). I need to exit the form and reload it in order to refresh the gridview.

For certain reasons, I cannot use a DataTable . But I would like this gridview to be updated every time I start the insert.

Can someone tell me how to do this? Thanks x

PS Although I do this in VB, I don't mind getting an answer in C #

Answer 1

First of all, you should use SQLCommand and Parameters to avoid sql injection as you use the SQLClient namespace. Try this as your Insert procedure.

 Private Sub InsertSQL(ByVal param1 As String, ByVal param2 As String, ByVal param3 As String) Using sqlConn As New SqlConnection("ConnectionStringHere") Using sqlComm As New SqlCommand() sqlComm.Connection = sqlConn sqlComm.CommandType = CommandType.Text sqlComm.CommandText = "INSERT INTO theTable VALUES (@Param1,@Param2,@Param3,@Param4,@Param5)" With sqlComm.Parameters .AddWithValue("@Param1", param1) .AddWithValue("@Param2", param2) .AddWithValue("@Param3", param3) .AddWithValue("@Param4", Now) .AddWithValue("@Param5", Now) End With Try sqlConn.Open() sqlComm.ExecuteNonQuery() Catch ex As SqlException MsgBox(ex.Message.ToString, MsgBoxStyle.Exclamation, "Error No. " & ex.ErrorCode.ToString) Finally sqlConn.Close() End Try End Using End Using End Sub 

Second, why do you prefer to use a DataTable to bind a DataGridView ? Well, here is another solution. It uses SQLDataReader , and you need to loop on it to put records in your grid.

 Private Sub ReloadGrid(ByVal connectionString As String) Dim queryString As String = "Your Query Here" Using connection As New SqlConnection(connectionString) Dim command As New SqlCommand(queryString, connection) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() DataGridView1.Rows.Clear() ' Clear All Rows ' While reader.Read() ' Console.WriteLine(String.Format("{0}, {1}", reader(0), reader(1))) ' ' Insert the record in your datagrid ' Dim row As String() = New String() {reader(0).ToString, reader(1).ToString, reader(2).ToString} DataGridView1.Rows.Add(row) End While ' Call Close when done reading. ' reader.Close() End Using End Sub 

Answer 2

if you use ADO.net, use the .EndEdit() / .Validate() method

Answer 3

You must specify the procedure or function responsible for reloading the data, which you can use to create a new call to the methods that you use to load the data into the datagrid.

 Public Sub InsertRow(ByVal param1 As String, ByVal param2 As String, ByVal param3 As String) Dim strConn As String = (the connection string) Dim sqlConn As New SqlConnection(strConn) Dim insertSQL As String = "INSERT INTO theTable VALUES ('" + param1 + "', '" + param2 + "', '" + param3 + "', '" + DateTime.Now + "', '" + DateTime.Now + "')" Dim comm As New SqlCommand(insertSQL, sqlConn) sqlConn.Open() comm.ExecuteNonQuery() sqlConn.Close() ReLoadData(grid) End Sub Private Sub btnInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInsert.Click InsertRow("a","b","c") End Sub Private Sub ReloadData(ByVAl sender as DataGridView) ' Implement your data load function ' I use for example sender.datasource = GetTable() 'GetTable is a function that return a DataTable Object With my data sender.DataSource = Nothing 'Free the DataGridView DataSource property for enable row edition. End Sub 

Answer 4

you can programmatically set dataSource to null and set the actual dataSource again to reload the gridview.

but the control will hang for a few seconds .. (even suspendLayout may not work)

Other than that I don’t know how to update. Even if you update, update, etc., it will not update the datagrid control associated with it