Datagridview and adding all of it to a SQL DB (Object reference not set)

[emogirl]

Hi everyone,

as my title says.. Im trying to add the entire data of the gridview to a SQLdatabase.
what i'm doing is looping through the DGView and adding it 1 at a time in a db.. Im not sure if this is the elegant way, but my knowledge in vb is just at noob level

my code so far.

Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadData.Click
 
        Dim ConnectionString As String = _
 "DataSource=.\SQLEXPRESS;AttachDbFilename=C:\EmoGirlSexyMe\2012\Programming\CSVtoDB\db1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
 
        Dim i As Integer = 0
        Dim str, Name, Items, SerialNumber, ContactDetails As String
        Dim strInsert As String = _
            "Insert Into csvTable (Name,Items,SerialNumber,ContactDetails)" & _
            " values (@CN,@CD,@PN,@D1)"
        Dim con1 As New SqlConnection(ConnectionString)
        Dim cmdInsert As New SqlCommand
        con1.Open()
        For i = 0 To DataGridView1.RowCount - 1
 
            Name = DataGridView1.Rows(i).Cells(0).Value.ToString()
            Items = DataGridView1.Rows(i).Cells(1).Value.ToString()
            SerialNumber = DataGridView1.Rows(i).Cells(2).Value.ToString()
            ContactDetails = DataGridView1.Rows(i).Cells(3).Value.ToString()
           
           
            cmdInsert = New SqlCommand(strInsert, con1)
            cmdInsert.Parameters.AddWithValue("@CN", Name)
            cmdInsert.Parameters.AddWithValue("@CD", Items)
            cmdInsert.Parameters.AddWithValue("@PN", SerialNumber)
            cmdInsert.Parameters.AddWithValue("@D1", ContactDetails)
           
            cmdInsert.ExecuteNonQuery()
 
        Next
        con1.Close()

End Sub

For the life of me, I cannot figure out why i'm getting a Object reference not set to an instance of an object.
Please help. I wasted hours trying to get pass this wall

 

[jmcilhinney]

Create a DataTable with the appropriate schema, either by adding the columns yourself or calling FillSchema on a data adapter containing the appropriate query. Bind the DataTable to the grid. As you populate the grid, the data is pushed to the DataTable thanks to data-binding. When you're done, call Update on a data adapter containing your INSERT statement. Follow the link below for an example of that and other common ADO.NET scenarios.

Retrieving and Saving Data in Databases

 

[IanRyder]

Hi emogirl,

There are some easier and more efficient ways to do this as jmcilhinney has just described above but to specifically answer the question with your own code, see below:-

The issue here is that your for loop is trying to pick up the information in "New" record at the end if the DataGridView. This "New" record is always added ready for your next entry but until you use it a DataGridViewRow abject has not been created and therefore you get the Object Reference error message.

The easy way to fix this is change the for loop to a For Each loop and then check to see if you are on the New record object thus ignoring it. Please see the code below as an example:-

  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    'This is my connection string so please replace
    Dim ConnectionString As String = "Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True;"
    '"DataSource=.\SQLEXPRESS;AttachDbFilename=C:\EmoGirlSexyMe\2012\Programming\CSVtoDB\db1.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True
    Dim Name, Items, SerialNumber, ContactDetails As String
    Const strInsert As String = "Insert Into csvTable (Name,Items,SerialNumber,ContactDetails) values (@CN,@CD,@PN,@D1)"
    Dim con1 As New SqlConnection(ConnectionString)
    Dim cmdInsert As New SqlCommand(strInsert, con1)
 
    con1.Open()
    For Each DGR As DataGridViewRow In DataGridView1.Rows
      If Not DGR.IsNewRow Then
        Name = DGR.Cells(0).Value.ToString()
        Items = DGR.Cells(1).Value.ToString()
        SerialNumber = DGR.Cells(2).Value.ToString()
        ContactDetails = DGR.Cells(3).Value.ToString()
 
        With cmdInsert
          .Parameters.Clear()
          .Parameters.AddWithValue("@CN", Name)
          .Parameters.AddWithValue("@CD", Items)
          .Parameters.AddWithValue("@PN", SerialNumber)
          .Parameters.AddWithValue("@D1", ContactDetails)
          .ExecuteNonQuery()
        End With
      End If
    Next
    con1.Close()
  End Sub

Hope that helps.

Cheers,

Ian.

 

[emogirl]

Hello..
Thank you both JM and Ian,
as of now, I'm short of time and need to provid the needed output so I used what little knowledge I have

The link looks interesting and might help with the improvements, i'll read it later. thanks JM,

Thank you Ian for explaining it beautifully. I understand now. and thanks for the code fix.

 

[jmcilhinney]

This part is not ideal:

    For Each DGR As DataGridViewRow In DataGridView1.Rows
      If Not DGR.IsNewRow Then
        Name = DGR.Cells(0).Value.ToString()
        Items = DGR.Cells(1).Value.ToString()
        SerialNumber = DGR.Cells(2).Value.ToString()
        ContactDetails = DGR.Cells(3).Value.ToString()
 
        With cmdInsert
          .Parameters.Clear()
          .Parameters.AddWithValue("@CN", Name)
          .Parameters.AddWithValue("@CD", Items)
          .Parameters.AddWithValue("@PN", SerialNumber)
          .Parameters.AddWithValue("@D1", ContactDetails)
          .ExecuteNonQuery()
        End With
      End If
    Next

You should not be adding the same parameters over and over. You add the parameters once and then set their values over and over:

    Dim parameters = cmdInsert.Parameters

    Dim p1 = parameters.Add("@CN", Name, 50, SqlDbType.VarChar)
    Dim p2 = parameters.Add("@CD", Items, 50, SqlDbType.VarChar)
    Dim p3 = parameters.Add("@PN", SerialNumber, 50, SqlDbType.VarChar)
    Dim p4 = parameters.Add("@D1", ContactDetails, 50, SqlDbType.VarChar)

    For Each DGR As DataGridViewRow In DataGridView1.Rows
      If Not DGR.IsNewRow Then
        Name = DGR.Cells(0).Value.ToString()
        Items = DGR.Cells(1).Value.ToString()
        SerialNumber = DGR.Cells(2).Value.ToString()
        ContactDetails = DGR.Cells(3).Value.ToString()
 
        p1.Value = Name
        p2.Value = Items
        p3.Value = SerialNumber
        p4.Value = ContactDetails

        cmdInsert.ExecuteNonQuery()
      End If
    Next

You can set the size and type of each parameter as appropriate.

 

[Icone]

Try this:

dim cmd as sqlclient.sqlclientcommand
dim a as integer = 0
For i = 0 To DataGridView1.RowCount - 1
cmd= new sqlclient.sqlclientcommand("insert into tblsample(field1)values('" & DataGridView1.Rows(i).Cells(0).Value.ToString() & "')",con)
if con.state = connection.closed then con.open
cmd.Executenonquery()
con.close
a = a + 1
Next
msgbox (a & " " & "data's have been added. ")