rony_78

Inserting empty datagridview cells into table

1 post in this topic

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 SqlException

An 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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now