Jump to content
rony_78

Inserting empty datagridview cells into table

Recommended Posts

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...