rony_78 0 Posted May 29, 2016 Report Share Posted May 29, 2016 Could somebody tell me how to update table with datagridview with blank cell in it? I have created a data entry form with datagridview with two columns in designer. I want to leave some cells of a column blank and save blank cells as zero in table. If there are no blank cells I can save datagridview content into table Dim thisConnection As New SqlConnection() Dim nonqueryCommand As SqlCommand = thisConnection.CreateCommand() Try ' Open Connection thisConnection.Open() Console.WriteLine("Connection Opened") ' Create INSERT statement with named parameters nonqueryCommand.CommandText = _ "INSERT INTO myTable (Col1, Col2) VALUES (@Col1, @Col2)" ' Add Parameters to Command Parameters collection nonqueryCommand.Parameters.Add("@Col1", SqlDbType.VarChar, 50) nonqueryCommand.Parameters.Add("@Col2", SqlDbType.VarChar, 50) ' Prepare command for repeated execution nonqueryCommand.Prepare() ' Data to be inserted For Each row As DataGridViewRow In DataGridView1.Rows If Not row.IsNewRow Then nonqueryCommand.Parameters("@Col1").Value = row.Cells(0).Value.ToString nonqueryCommand.Parameters("@Col2").Value = row.Cells(1).Value.ToString End If Next nonqueryCommand.ExecuteNonQuery() Catch ex As SqlException ' Display error Console.WriteLine("Error: " & ex.ToString()) Finally ' Close Connection thisConnection.Close() Console.WriteLine("Connection Closed") End Try I don’t know if this is correct way to check for empty cell in order to save into table. I get an error when I place the code between try and Catch ex As SqlExceptionAn OleDbParameter with ParameterName '@Col1' is not contained by this OleDbParameterCollection If row.Cells(0).Value.ToString IsNot Nothing Then nonqueryCommand.Parameters("@Col1").Value = row.Cells(0).Value.ToString() else nonqueryCommand.Parameters("@Col1").Value = "0" end if Dim thisConnection As New OleDbConnection("") Dim nonqueryCommand As OleDbCommand = thisConnection.CreateCommand() Try ' Open Connection thisConnection.Open() Console.WriteLine("Connection Opened") nonqueryCommand.CommandText = _ "INSERT INTO Table1 (Col1, Col2) VALUES (@Col1, @Col2)" ' Data to be inserted For Each row As DataGridViewRow In DataGridView1.Rows If row.Cells(0).Value.ToString IsNot Nothing Then nonqueryCommand.Parameters("@Col1").Value = row.Cells(0).Value.ToString() Else nonqueryCommand.Parameters("@Col1").Value = "0" End If If row.Cells(1).Value.ToString IsNot Nothing Then nonqueryCommand.Parameters("@Col1").Value = row.Cells(1).Value.ToString() Else nonqueryCommand.Parameters("@Col1").Value = "0" End If Next nonqueryCommand.ExecuteNonQuery() Catch ex As OleDbException ' Display error Console.WriteLine("Error: " & ex.ToString()) Finally ' Close Connection thisConnection.Close() Console.WriteLine("Connection Closed") End Try Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.