rony_78 Posted May 29, 2016 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now